Re: encapsulating tables in pl/sql packages?

From: Michael Thomas <mhthomas_at_yahoo.com>
Date: Sun, 1 Feb 2004 01:22:51 -0800 (PST)
Message-ID: <20040201092251.21786.qmail@web60505.mail.yahoo.com>

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_at_mail.ee> 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_at_cox.net>
> To: <oracle-l_at_freelists.org>
> 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_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at
> http://www.freelists.org/archives/oracle-l/
> > FAQ is at
> http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> http://www.freelists.org/archives/oracle-l/
> FAQ is at
> http://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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sun Feb 01 2004 - 03:22:51 CST