I'm in the process of learning more about Oracle PL/SQL. My question
however concerns access to a database model in any database.
Most texts recommend that all access to the data model should be through
a PL/SQL package API and that no SQL should exist directly within the
application. I can see some merits to this approach, both in terms of
organization and possibly performance.
Unfortunately, following this approach would lead to writing a good
chunk of code in PL/SQL and not in CL.
What do folks think of following this approach? Are there any CL tools
to help with writing PL/SQL? Are there any good databases that use CL
as their stored procedure language?
Thanks for your thoughts,
-Jason
"Jason S. Cornez" <·····@au-bon-pain.lcs.mit.edu> wrote in message
····················@au-bon-pain.lcs.mit.edu...
> I'm in the process of learning more about Oracle PL/SQL. My question
> however concerns access to a database model in any database.
>
> Most texts recommend that all access to the data model should be through
> a PL/SQL package API and that no SQL should exist directly within the
> application. I can see some merits to this approach, both in terms of
> organization and possibly performance.
>
> Unfortunately, following this approach would lead to writing a good
> chunk of code in PL/SQL and not in CL.
>
> What do folks think of following this approach? Are there any CL tools
> to help with writing PL/SQL? Are there any good databases that use CL
> as their stored procedure language?
It's a perfectly valid concept. It's just one more level of abstraction
between the raw data (the SQL) and the application, nothing much wrong with
that.
Many people avoid it for portablilty concerns, particularly for your most
basic SQL.
From a CL side, you can draw the abstraction line in your design whereever
you want. You can push much of the basic data access code into PL/SQL versus
CL. There really isn't much of a matter WHERE the basic SQL statements live,
the real question is where does the logic that operates on data fetched from
the database live.
But, as I recall, it's been a while, LispWorks had a nice extension of the
LOOP macro which was SQL aware, UnCommon SQL might have the same extension,
I haven't looked. Either way, it's an interesting idea towards what CL can
do to help you write your application that PL/SQL can not.
As far as performance, the only real benefit you would get is that PL/SQL
precompiles all of its SQL when it compiles the procedure, whereas all of
the SQL from your CL app is essentially "dynamic" SQL which needs to be
compiled when first used. The interfaces may be better today about letting
you prepare statements once and use them several times. In theory, Oracle
does this for you automatically anyway when it sees a SQL statement that
it's seen before, so maintaining it on CLs side may be moot.
Also, local calls would likely incur lower network costs as well for data
transfers.
Of course, the other advatnage of having any app logic on the DB side is
that it's more accessible to things outside your app (like 3rd party
reporting tools).
However, on the other side of the coin, you'd be amazed at how much code in
Oracles own applications is NOT in the database, so there's a lot of "Do as
I say, not as I do" in this field as well.
So, its really an idiomatic choice of what you're comfortable with and what
your app requires. If your app is not monolithic, and needs to share the DB
and play nice with others, then perhaps putting more code into the DB is
appropriate so you only have to write it once. You can always prototype your
stuff in CL, and then convert it over to PL/SQL later, or write a simple
translator. Do a bunch of CL macrology to make that task easy if you like.
Regards,
Will Hartung
(·····@msoft.com)
From: Frank Goenninger
Subject: Re: CL and PL/SQL
Date:
Message-ID: <it35mh06.fsf@hp.com>
Hi Jason,
As an approach type I for myself came to a simple
rule:
Everything that is close to the data model and is
handling data integrity is to be done in PL/SQL.
To illustrate I want to give you a real life example
from a project I have done at a major aerospace customer.
The "application"'s purpose was to implement a data
replication solution replicating DB contents between
two independent locations of the customer via WAN.
The front-end application that the user interacts with
is not needed to know anything of the replication
being in place.
This led us to the design choice that the replication
code is to be implemented as close to the DB as possible,
therefore in Oracle PL/SQL (using stored procedures,
triggers).
This is resulting in a few advantages:
* The replication code only has to be changed when
the data model changes.
* The replication is guarantueed to work with any
application accessing the DB. Also future ones
that are not yet implemented.
* Data integrity is handled entirely by the DB.
And that's the core business of the DB anyway.
There are obvious disadvantages to this too:
* PL/SQL is one more implementation language,
adding complexity to source code management.
* You need PL/SQL know-how - there is plenty of
it available on the market. But it may not be
within your team.
* Oracle is Oracle. MS is MS. PL/SQL runs on
Oracle. You have to make your choice.
HTH.
Regards,
Frank