Re: encapsulating tables in pl/sql packages?
- From: Michael Thomas <mhthomas@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 1 Feb 2004 01:22:51 -0800 (PST)
Hi,
Here are my experiences.
I've used it quite a few times. First back when
Revealnet sold it as a product. Now its free to
download, but there may be 9i (10g) issues getting it
all compiled. I've used it in the last year.
I think the performance is not a problem, but I have
not recently compared the many plus & minuses. One
thing for sure, it cleaned up our code design
tremendously. Because all the table access was via
simple standard PL/SQL 'access' procedures, the
business logic was easier to follow in our code
(assuming you didn't need to look under the covers at
the 'generated' code). Of course you don't need the
tool to have good code design. The structural syntax
of the 'generated' code is quite good (OO like). The
simple standard 'access' procedures are shown in
documented examples. You better know what you are
doing if you are going to 'manually' maintain the
generated code, or even 'regenerate' your code.
It best supports tables with primary keys, but does
work on tables/views with unique indexes (a
challenging hack if you follow the generator's
instructions). I have tweaked it quite a bit.
One issue I found was there are 'select *' in some of
the generated cursor statements (I think its still
done in tool) which caused us headaches with generated
code between our various environments (dev,test,prod).
Some of our environments had different column orders
on the same tables. Don't ask... no really... don't
ask. ;-)
Regarding the optimizer, there are a set of a few
distinct cursors generated automatically. They use
bind variables, and generally should reduce parsing.
You can generate additional cursors on views via
additional configurations (an especially scary
process). Its easier to roll-your-own additional
cursors in PL/SQL if you are doing a multi-table
select and you don't use a view.
If you have a specific question, maybe I can answer.
Otherwise, its had to think what else to say. Okay?
Regards,
Mike Thomas
PS: While you may never want to use the generated
code, we learned some really good PL/SQL lessons by
reading the generated code. I recommend generating
some examples, reading how to 'access' the generated
procedures in documented examples, and assimilating
the generated code design/syntax.
--- Tanel Poder <tanel.poder.003@xxxxxxx> wrote:
> No experiences on it, but reduced performance and
> additional bugs are
> possible drawbacks of this approach. The Optimizer
> probably doesn't have
> free hands for putting together the execution plans
> either.
>
> Tanel.
>
> ----- Original Message -----
> From: "Ryan" <ryan.gaffuri@xxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Sunday, February 01, 2004 5:57 AM
> Subject: encapsulating tables in pl/sql packages?
>
>
> > Steve Fuerstein has written on this extensively
> and I heard a guy speak on
> this recently. I was wondering if anyone tried this.
> Instead of giving
> developers direct sql access to tables, each table
> is wrapped in a package
> and the developers call those packages to perform
> select and DML statements
> on the tables.
> > if you have done this, please post your
> experiences.
> >
> >
>
----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >
>
----------------------------------------------------------------
> > To unsubscribe send email to:
> oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at
> //www.freelists.org/archives/oracle-l/
> > FAQ is at
> //www.freelists.org/help/fom-serve/cache/1.html
> >
>
-----------------------------------------------------------------
> >
>
>
>
----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
>
----------------------------------------------------------------
> To unsubscribe send email to:
> oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> //www.freelists.org/archives/oracle-l/
> FAQ is at
> //www.freelists.org/help/fom-serve/cache/1.html
>
-----------------------------------------------------------------
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
