Do you use PL/SQL

#41

On May 22, 12:19 pm, "mfuller... (AT) gmail (DOT) com" <mfuller... (AT) gmail (DOT) com>
wrote:

Quote:

On May 19, 11:44 pm, zigzag... (AT) yahoo (DOT) com wrote:

On May 19, 11:02 pm, Doug Davis <douglass_da... (AT) earthlink (DOT) net> wrote:

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

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).

Ok, from personal experience I have to disagree. However, I am willing
to be open minded. Do you have any kind of data to support this? Some
examples how the performance is improved enough by using a procedural
language that it makes sense to move the business logic out to a
different tier?

Mike Fullerton- Hide quoted text -

- Show quoted text -

Businees logic is not moved to another tier/server. All you do is to
have your business logic on database server. All one does is to make
JDBC connection from DB server to database on DB server. Also, one can
do JDBC connection pooling. I have worked on many projects where we do
not write any business logic in stored procedures because stored
procedures cannot call any of the project libraries written in a
language different from PL/SQL. Also, developers write business logic
in Java or C++ classes which are easier to reuse (inehritance etc).
There were anever any performance issues which impact users.
Performance issues occur when one writes inefficient quieries and not
when one writes code in JDBC or C++/Por*C vs PL/SQL. This has been
done many many times.

#42

On May 20, 1:18 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

Doug Davis wrote:

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

I authored and teach the Oracle program at the University of Washington
and I must agree with Frank van Bortel when he questions your
qualification to teach the class. And my apology if the following seems
a bit cruel... it isn't intended to be such.

Several years ago I brought in a PhD Java programmer who was one of the
authors of the Hibernate framework to teach one quarter with me. Truly
a Java genius. But my students knew more about databases than he did and
while they learned a lot of Java they class evaluation form after the
quarter was brutal.

Let me give you a short self-test to consider before you teach it.
1. When is it appropriate to commit inside of a loop?
2. When is it appropriate in 9i or 10g to use a cursor loop?
3. When should production code be deployed with stand-alone functions
and procedures?

If your answer to all three isn't ... "essentially never" ... reconsider.

If you don't understand database concepts and architecture you should
reconsider teaching the class. If you don't understand the difference
between thinking in sets and procedural code you should reconsider
teaching the class. My interpretation of your two questions is that you
are wholly unqualified.

Again: sorry if this is a bit harsh.
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org

Let's see ... this guy is asking a question politely and gets jumped
all over.

Hmmm ... guess I can't remember anyone else putting out gems like
"drop the dba role", "run produciton systems on 10.1 mac osx", "do
your performance tuning based on statspack 1 hour snapshots" ...

You said that he was unqualified? Wow.

#43

Doug Davis wrote:

Quote:

On May 20, 6:12 am, Marc Blum <b... (AT) marcblum (DOT) de> wrote:
On 19 May 2007 20:02:52 -0700, Doug Davis <douglass_da... (AT) earthlink (DOT) net> wrote:

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.)
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.

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?)
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.

thanks. real-world experiences such as this are always helpful to me.

PL/SQL does seem like it would be good for less CPU intensive tasks.

As someone who has been in IT since 1969 and has more than a few years
of experience with Oracle your response, the line above this, is an
emetic. You truly do not understand PL/SQL. You seem to truly not
understand relational databases. You seem to not have any background
in Oracle concepts and architecture. And your students deserve to take
this class from someone who, if not a subject matter expert, has at
least a passing familiarity with the subject.

The more you write the more you throw gasoline on the issue.

Quote:

I would think that if you put business-logic in a client side app, you
would have the problems of more network traffic (although this seems
very dependent apon how much network traffic is inherent in the app,
it seems like it may not even be a problem for some).

You just don't get it so let me spell it out. No database yet built and
put into production has been accessed by only the designated front-end
tool. It will be accessed by many tools and all but one of them will
not have the business logic.

You put business logic into the database to protect the data from people
with SQL*Plus, MS Access, and yes even Crystal Reports. Doing so is far
more efficient in terms of CPU, Disk I/O, network bandwidth, etc. Plus,
in the database, we have tools that we can use to tune DML.

The problem in application development these days is that people who
have essentially no background in databases, such as you, make decisions
based wholly upon their ignorance. No SQL developer would pretend to
know how many layers belong in a Java application, or whether to use or
not use beans. But front-end developers, reeking of ignorance, quite
often think they can write SQL, and worse yet PL/SQL.

Quote:

Also, if you did
a lot of SQL queries on the client side, and wanted to change and
develop another client, you may have to reprogram much of the business
logic too. So, i could see that being a problem.

Don't worry. We have DBMS_ADVANCED_REWRITE and we rip your queries out
of our databases and replace them with our own when they are too ugly.

I would recommend that you pick up a copy of Tom Kyte's books and read
them. And again that you find a good DBA to co-teach with you so that
your students have a chance of being successful.

Essentially every sentence you have written here demonstrates that you
do not understand the subject. Please reconsider taking the assignment.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#44

hpuxrac wrote:

Quote:

On May 20, 1:18 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Doug Davis wrote:

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
I authored and teach the Oracle program at the University of Washington
and I must agree with Frank van Bortel when he questions your
qualification to teach the class. And my apology if the following seems
a bit cruel... it isn't intended to be such.

Several years ago I brought in a PhD Java programmer who was one of the
authors of the Hibernate framework to teach one quarter with me. Truly
a Java genius. But my students knew more about databases than he did and
while they learned a lot of Java they class evaluation form after the
quarter was brutal.

Let me give you a short self-test to consider before you teach it.
1. When is it appropriate to commit inside of a loop?
2. When is it appropriate in 9i or 10g to use a cursor loop?
3. When should production code be deployed with stand-alone functions
and procedures?

If your answer to all three isn't ... "essentially never" ... reconsider.

If you don't understand database concepts and architecture you should
reconsider teaching the class. If you don't understand the difference
between thinking in sets and procedural code you should reconsider
teaching the class. My interpretation of your two questions is that you
are wholly unqualified.

Again: sorry if this is a bit harsh.
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org

Let's see ... this guy is asking a question politely and gets jumped
all over.

Hmmm ... guess I can't remember anyone else putting out gems like
"drop the dba role", "run produciton systems on 10.1 mac osx", "do
your performance tuning based on statspack 1 hour snapshots" ...

You said that he was unqualified? Wow.

To quote John B. Hurley on May 1, 2007

"He is as entitled as any to voice his opinion."

And I did. Thank you.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#45

On May 22, 6:25 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

Doug Davis wrote:
On May 20, 6:12 am, Marc Blum <b... (AT) marcblum (DOT) de> wrote:
On 19 May 2007 20:02:52 -0700, Doug Davis <douglass_da... (AT) earthlink (DOT) net> wrote:

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.)
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.

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?)
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.

thanks. real-world experiences such as this are always helpful to me.

PL/SQL does seem like it would be good for less CPU intensive tasks.

As someone who has been in IT since 1969 and has more than a few years
of experience with Oracle your response, the line above this, is an
emetic. You truly do not understand PL/SQL. You seem to truly not
understand relational databases. You seem to not have any background
in Oracle concepts and architecture. And your students deserve to take
this class from someone who, if not a subject matter expert, has at
least a passing familiarity with the subject.

The more you write the more you throw gasoline on the issue.

I would think that if you put business-logic in a client side app, you
would have the problems of more network traffic (although this seems
very dependent apon how much network traffic is inherent in the app,
it seems like it may not even be a problem for some).

You just don't get it so let me spell it out. No database yet built and
put into production has been accessed by only the designated front-end
tool. It will be accessed by many tools and all but one of them will
not have the business logic.

You put business logic into the database to protect the data from people
with SQL*Plus, MS Access, and yes even Crystal Reports. Doing so is far
more efficient in terms of CPU, Disk I/O, network bandwidth, etc. Plus,
in the database, we have tools that we can use to tune DML.

The problem in application development these days is that people who
have essentially no background in databases, such as you, make decisions
based wholly upon their ignorance. No SQL developer would pretend to
know how many layers belong in a Java application, or whether to use or
not use beans. But front-end developers, reeking of ignorance, quite
often think they can write SQL, and worse yet PL/SQL.

Also, if you did
a lot of SQL queries on the client side, and wanted to change and
develop another client, you may have to reprogram much of the business
logic too. So, i could see that being a problem.

Don't worry. We have DBMS_ADVANCED_REWRITE and we rip your queries out
of our databases and replace them with our own when they are too ugly.

I would recommend that you pick up a copy of Tom Kyte's books and read
them. And again that you find a good DBA to co-teach with you so that
your students have a chance of being successful.

Essentially every sentence you have written here demonstrates that you
do not understand the subject. Please reconsider taking the assignment.

I'm not really sure how to reply here, it seems you are creating
things in your mind that are not even being said.

i appreciate any advice you or any one else has on real-world issues
with using PL/SQL, I learn quickly, and will learn all I can before I
teach the class, but otherwise, you have made yourself pretty much
useless to me at this point. Take care.

#46

On May 22, 6:25 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

Doug Davis wrote:
On May 20, 6:12 am, Marc Blum <b... (AT) marcblum (DOT) de> wrote:
On 19 May 2007 20:02:52 -0700, Doug Davis <douglass_da... (AT) earthlink (DOT) net> wrote:

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.)
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.

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?)
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.

thanks. real-world experiences such as this are always helpful to me.

PL/SQL does seem like it would be good for less CPU intensive tasks.

As someone who has been in IT since 1969 and has more than a few years
of experience with Oracle your response, the line above this, is an
emetic. You truly do not understand PL/SQL. You seem to truly not
understand relational databases. You seem to not have any background
in Oracle concepts and architecture. And your students deserve to take
this class from someone who, if not a subject matter expert, has at
least a passing familiarity with the subject.

The more you write the more you throw gasoline on the issue.

I would think that if you put business-logic in a client side app, you
would have the problems of more network traffic (although this seems
very dependent apon how much network traffic is inherent in the app,
it seems like it may not even be a problem for some).

You just don't get it so let me spell it out. No database yet built and
put into production has been accessed by only the designated front-end
tool. It will be accessed by many tools and all but one of them will
not have the business logic.

You put business logic into the database to protect the data from people
with SQL*Plus, MS Access, and yes even Crystal Reports. Doing so is far
more efficient in terms of CPU, Disk I/O, network bandwidth, etc. Plus,
in the database, we have tools that we can use to tune DML.

The problem in application development these days is that people who
have essentially no background in databases, such as you, make decisions
based wholly upon their ignorance. No SQL developer would pretend to
know how many layers belong in a Java application, or whether to use or
not use beans. But front-end developers, reeking of ignorance, quite
often think they can write SQL, and worse yet PL/SQL.

Also, if you did
a lot of SQL queries on the client side, and wanted to change and
develop another client, you may have to reprogram much of the business
logic too. So, i could see that being a problem.

Don't worry. We have DBMS_ADVANCED_REWRITE and we rip your queries out
of our databases and replace them with our own when they are too ugly.

I would recommend that you pick up a copy of Tom Kyte's books and read
them. And again that you find a good DBA to co-teach with you so that
your students have a chance of being successful.

Essentially every sentence you have written here demonstrates that you
do not understand the subject. Please reconsider taking the assignment.

I'm not really sure how to reply here, it seems you are creating
things in your mind that are not even being said.

i appreciate any advice you or any one else has on real-world issues
with using PL/SQL, I learn quickly, and will learn all I can before I
teach the class, but otherwise, you have made yourself pretty much
useless to me at this point. Take care.

#47

On May 22, 6:25 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

Quote:

Doug Davis wrote:
On May 20, 6:12 am, Marc Blum <b... (AT) marcblum (DOT) de> wrote:
On 19 May 2007 20:02:52 -0700, Doug Davis <douglass_da... (AT) earthlink (DOT) net> wrote:

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.)
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.

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?)
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.

thanks. real-world experiences such as this are always helpful to me.

PL/SQL does seem like it would be good for less CPU intensive tasks.

As someone who has been in IT since 1969 and has more than a few years
of experience with Oracle your response, the line above this, is an
emetic. You truly do not understand PL/SQL. You seem to truly not
understand relational databases. You seem to not have any background
in Oracle concepts and architecture. And your students deserve to take
this class from someone who, if not a subject matter expert, has at
least a passing familiarity with the subject.

The more you write the more you throw gasoline on the issue.

I would think that if you put business-logic in a client side app, you
would have the problems of more network traffic (although this seems
very dependent apon how much network traffic is inherent in the app,
it seems like it may not even be a problem for some).

You just don't get it so let me spell it out. No database yet built and
put into production has been accessed by only the designated front-end
tool. It will be accessed by many tools and all but one of them will
not have the business logic.

You put business logic into the database to protect the data from people
with SQL*Plus, MS Access, and yes even Crystal Reports. Doing so is far
more efficient in terms of CPU, Disk I/O, network bandwidth, etc. Plus,
in the database, we have tools that we can use to tune DML.

The problem in application development these days is that people who
have essentially no background in databases, such as you, make decisions
based wholly upon their ignorance. No SQL developer would pretend to
know how many layers belong in a Java application, or whether to use or
not use beans. But front-end developers, reeking of ignorance, quite
often think they can write SQL, and worse yet PL/SQL.

Also, if you did
a lot of SQL queries on the client side, and wanted to change and
develop another client, you may have to reprogram much of the business
logic too. So, i could see that being a problem.

Don't worry. We have DBMS_ADVANCED_REWRITE and we rip your queries out
of our databases and replace them with our own when they are too ugly.

I would recommend that you pick up a copy of Tom Kyte's books and read
them. And again that you find a good DBA to co-teach with you so that
your students have a chance of being successful.

Essentially every sentence you have written here demonstrates that you
do not understand the subject. Please reconsider taking the assignment.

I'm not really sure how to reply here, you are creating things in
your mind that are not even being said.

i appreciate any advice you or any one else has on real-world issues
with using PL/SQL or what books to study, otherwise, you have made
yourself pretty much useless to me at this point. Take care.

#48

Quote:

Let's see ... this guy is asking a question politely and gets jumped
all over.

I don't want to be rude, but I have spent way too much of my life over
the last 20 years trying to make applications designed according to Mr.
Davis' anti-principles work in real operational environments.

Statements such as "today network performance doesn't matter" are very
true: in the single office where the 10 developer sit with exactly two
applications installed on their workstations (their IDE and the target
app) all nicely connected with the fastest switch[1] in the catalog.

Try to make those work in the real world, where you have manufacturing
subsidiaries 187 miles from the nearest telco POP (I am talking _inside_
the United States here, not international!) and with best possible
latency in the 200ms range, then tell me network performance "isn't a
consideration". Or look at the server rooms with stacks and stacks of
"tiers", along with load directors, layer 3 switches, etc rather than
one nice solid business data server with as much as possible of the
logic safely and securely tucking inside. And "platform independent"
Java "tiers" that are quite platform independent: they perform equally
_badly_ on Oracle, SQL Server, and DB2!

I could go on if you like; I have 20 years of such cases. I don't
always agree with Mr. Morgan but he is 100.1% right on this one and Mr.
Davis is IMHO not qualified to teach this class. In fact I would like
to know where it is being taught so I can avoid hiring anyone from that
institution.

sPh

#49

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

Doug Davis wrote:

Quote:

On May 20, 5:08 am, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com
wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Doug Davis wrote:

I will be teaching aPL/SQLclass, and I wanted to get some opinions.
I read aboutPL/SQL, but wanted somepracticaladvice from people who
have used it.
1. Why usePL/SQLinstead 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
withPL/SQL(or have heard some one do withPL/SQL?)
thanks.
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!

thanks for your opinion.

You're welcome.

One thing I haven't seen in this thread, is the possibility to
write database independent applications.

When executed correctly, all the application does is make calls
to (packaged) procedures. (packaged in brackets, as Oracle is
the only RDBMS that supports that, whereas every RDBMS system
with procedural extensions supports functions and procedures).

The application then does not have to "know" the ins and outs
of the RDBMS, and every RDBMS programmer can implement specifics
in the procedures, using the possibilities of the RDBMS to the
full extent of the product.
Obviously, such an approach can only be done with whatever language
the RDBMS offers natively. For Oracle, that would be either PL/SQL,
or Java. Rule of thumb here (and be careful not to promote ROT to
truth!) to use Java for CPU intensive stuff, and PL/SQL for data
intensive stuff.

Sadly, it's all to common that apps get "re-engineered" towards
db independence after the initial release(s). It may become
clear from the above, such an approach requires a hell of a
lot of thinking and planning, and thus is seldom (if at all)
seen, while "taking too long", "being too expensive", "being
too difficult".
<sarcasm>
No - having to patch systems on a monthly basis for the life
of the product (5 ~ 7 years), that's smart, inexpensive, fast.
</sarcasm>
- --
Regards,
Frank van Bortel

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

iD8DBQFGU+uULw8L4IAs830RAiOeAJ95eB8qXi2xaMWv/Cg5SHbfXbrQHQCfeVn2
zNTH6Y3V5gK+K8cCMBp57iw=
=ZdXx
-----END PGP SIGNATURE-----

#50

On 23.05.2007 01:45, www.douglassdavis.com wrote:

Quote:

On May 22, 6:25 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Doug Davis wrote:
On May 20, 6:12 am, Marc Blum <b... (AT) marcblum (DOT) de> wrote:
On 19 May 2007 20:02:52 -0700, Doug Davis <douglass_da... (AT) earthlink (DOT) net> wrote:
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.)
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.
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?)
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.
thanks. real-world experiences such as this are always helpful to me.
PL/SQL does seem like it would be good for less CPU intensive tasks.
As someone who has been in IT since 1969 and has more than a few years
of experience with Oracle your response, the line above this, is an
emetic. You truly do not understand PL/SQL. You seem to truly not
understand relational databases. You seem to not have any background
in Oracle concepts and architecture. And your students deserve to take
this class from someone who, if not a subject matter expert, has at
least a passing familiarity with the subject.

The more you write the more you throw gasoline on the issue.

I would think that if you put business-logic in a client side app, you
would have the problems of more network traffic (although this seems
very dependent apon how much network traffic is inherent in the app,
it seems like it may not even be a problem for some).
You just don't get it so let me spell it out. No database yet built and
put into production has been accessed by only the designated front-end
tool. It will be accessed by many tools and all but one of them will
not have the business logic.

You put business logic into the database to protect the data from people
with SQL*Plus, MS Access, and yes even Crystal Reports. Doing so is far
more efficient in terms of CPU, Disk I/O, network bandwidth, etc. Plus,
in the database, we have tools that we can use to tune DML.

The problem in application development these days is that people who
have essentially no background in databases, such as you, make decisions
based wholly upon their ignorance. No SQL developer would pretend to
know how many layers belong in a Java application, or whether to use or
not use beans. But front-end developers, reeking of ignorance, quite
often think they can write SQL, and worse yet PL/SQL.

Also, if you did
a lot of SQL queries on the client side, and wanted to change and
develop another client, you may have to reprogram much of the business
logic too. So, i could see that being a problem.
Don't worry. We have DBMS_ADVANCED_REWRITE and we rip your queries out
of our databases and replace them with our own when they are too ugly.

I would recommend that you pick up a copy of Tom Kyte's books and read
them. And again that you find a good DBA to co-teach with you so that
your students have a chance of being successful.

Essentially every sentence you have written here demonstrates that you
do not understand the subject. Please reconsider taking the assignment.

I'm not really sure how to reply here, you are creating things in
your mind that are not even being said.

i appreciate any advice you or any one else has on real-world issues
with using PL/SQL or what books to study, otherwise, you have made
yourself pretty much useless to me at this point. Take care.

I can see this posting three times with timestamps differing one minute
each. Please make sure to post just once. Thank you.

robert