I will be teaching a PL/SQL class, and I wanted to get some opinions.
I read about PL/SQL, but wanted some practical advice from people who
have used it.


1. Why use PL/SQL instead of just sending SQL queries from a program
written in a procedural language on the client side (Java, Visual
Basic, C++, anything.)


2. What are some examples of "real-world" things that you have done
with PL/SQL (or have heard some one do with PL/SQL?)


thanks.



--
http://www.douglassdavis.com

Doug Davis <douglass_davis (AT) earthlink (DOT) net> wrote in
news:1179630172.123167.246530 (AT) o5g2000hsb (DOT) googlegroups.com:


I contend that PL/SQL reduces network traffic by significant amounts.
With SQL you can't do any type of flow control (IF THEN ELSE).
With any 3GL lanuage, the results set must be returned back to the 3GL
before simple flow control can occur.
While the identical results can be obtained using either approach,
I believe that PL/SQL solution(s) can scale to MUCH large databases
and/or many more application users.

On May 19, 11:02 pm, Doug Davis <douglass_da... (AT) earthlink (DOT) net> wrote:
If you know Java or C++, you are better of using them than using PL/
SQL. Computers run so fast that performance advantages gained by PL/
SQL over C++ or Java are very little. On the other hand, if you do not
know these languages or do not want to write lot of code in them use
PL/SQL. PL/SQL you can write database centric programs quickly (lot
less lines of code than JAVA or C++). JAVA or C++ , however, are not
limited to calling SQL, you can do lot more things. If you are a
software engineer, have complex big software projects, use Java or C+
+. On the other hand if you want to write database centric programs
quickly write them in PL/SQL.

Certain activity such as writing triggers etc can be best done only
using PL/SQL blocks. Event, tough Java in theory can be called from
triggers (using Java stored procedures), one has to write convoluted
code to do it (you still write lots of PL/SQL code).

"Doug Davis" <douglass_davis (AT) earthlink (DOT) net> a écrit dans le message de news: 1179630172.123167.246530 (AT) o5g200...oglegroups.com...
PL/SQL User's Guide and Reference, Chapter 1 "Overview of PL/SQL",
section "Advantages of PL/SQL":
http://download-uk.oracle.com/docs/c...view.htm#i9141

Regards
Michel Cadot

On 20 mai, 05:02, Doug Davis <douglass_da... (AT) earthlink (DOT) net> wrote:
1. Architecturally, it just makes sense to pack primitive
data manipulation operations with the data, that is in the database.

2. Seconding Ana Dent, network traffic is reduced by a *huge* amount
(a factor of 20 in some of our applications)

3. SQL is nicely integrated in PL/SQL. 3GL code doing SQL
is often more tedious and complicated .

4. Transactions make PL/SQL the only language I know of that
is fool-proof in the face of exceptions

Note that some of the advantages above apply to any language
running in the database.

50,000 lines of PL/SQL code here, implementing all primitive
operations.

--- Raoul

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

zigzagdna (AT) yahoo (DOT) com wrote:

Never heard more BS than that


- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGUA+ALw8L4IAs830RAtdxAJ9tx3Tw9Fqj/ezIaBLhWfrpqF/6vgCbBQEK
VDfwZV5T6HSQtWOpY8oyDOE=
=EV8q
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Doug Davis wrote:
When asking these questions, I seriously begin to question
the authority to teach classes.

These are exactly the type of questions *you* should be
able to answer, as an instructor!

On 2: metalink.oracle.com, Oracle Application Express.
100% pl/sql!

- --
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)

iD8DBQFGUBATLw8L4IAs830RArCcAJ4rxjHBRBMNxooj09uwv6 kX0ThxxQCeOd9L
PN5FxWRXysL2AKBYWpfhMSM=
=ux1v
-----END PGP SIGNATURE-----

On 19 May 2007 20:02:52 -0700, Doug Davis <douglass_davis (AT) earthlink (DOT) net> wrote:

First: when talking about PL/SQL, it always means to me a mixture of SQL and
PL/SQL. We follow the first law according to Tom Kyte:

- If you can do it in pure SQL, do it in pure SQL.

- If you can't do it in pure SQL, do it in PL/SQL.

- If you can't do it in PL/SQL, do it in Java, C or whatever...


We use PL/SQL as an application server thier. The PL/SQL layer manages all data
centric operations. Typically representation and user interaction remains to the
4GLs like Delphi and so on.

From a deployability standpoint, putting business requierements into packages
makes code management in dev, test and prod much more easier.

Security models are much easier to implement.

PL/SQL is tightly integrated with the database engine. You have the full power
of ORACLE server at your fingertips.

The only advantage for other languages seems to be when doing extremely CPU
intensive algorithms. When throwing data around, PL/SQL is unbeatable.

For about 10 years we use with great success a PL/SQL-centric approach in dozens
of big applications, individual solutions and software products.

Typically it's the small projects, which don't follow this path.

We do incredible big and complex applications. 100.000 and 100.000 and more
lines of code, which implement not-so-simplistic business rules.

Last shot was a software module to manage time series data, implementing a
API-based approach, coded 100% in PL/SQL and SQL.

In legacy systems, we successfully migrate code from the app layer to the PL/SQL
layer, whenever possible.

--
Marc Blum
mailto:<nachname>@marcblum.de
http://www.marcblum.de

On May 20, 5:06 am, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:

Why do you thinh JDBC, PRO C/C++ , Csharp .NET etc are developed in
the first place. Does PL/SQL have all the object-orieneted features of
JAVA or C++? It all depends on which background one comes from and
what are the career path one wants to take? Learning many langauges
is always a good think. One should not limit oneself to PL/SQL. Usings
statements like rubbish just shows one lacks objectivity and.

On 20 May 2007 05:51:46 -0700, zigzagdna (AT) yahoo (DOT) com wrote:

It seems you dismiss PL/SQL, just because you don't know.
Developers promoting JDBC etc usually do a very lousy job in
programming against Oracle, just because they don't know Oracle.
Your statements show you lack objectivity.

--
Sybrand Bakker
Senior Oracle DBA