Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: Wrapping all tables with packages and scalability

Re: Re: Wrapping all tables with packages and scalability

From: Nuno Souto <dbvision_at_optusnet.com.au>
Date: Mon, 3 May 2004 23:48:49 +1000
Message-ID: <002b01c43115$91fce200$9b00a8c0@dcs001>

> don't quite follow one point in your post. What do you mean, I nee dto use
object types for each row in a table? I thought the norm was to use a REF Cursor?
>

You have to pass parameters to-from your function(s) or procedure(s) in your wrapper packages. And these have to pass result values back.

These parameters: instead of being defined one by one as individual items of Oracle data types (DATE, NUMBER, VARCHAR, whatever) should instead be defined as one single parameter that is an Oracle object type. This is translated to the JDBC layer as a STRUCT.

You only use REF cursors when you are returning rows from a SELECT. If you are doing an complete wrapper for ALL operations on your tables, you need to address all possible interactions. INSERT, UPDATE, DELETE are equally possible, so is LOCK and neither can use REF CURSORs.

And whatever else more complex you may wish to wrap. Nothing stops you from handling an entire table hierarchy through a single wrapper package. In fact, this is a very nice way of handling the OO-relational mapping.

Now, instead of passing each individual column value as a parameter to the function or procedure, you pass ONE SINGLE parameter that is the entire row.

The easiest way to do this is to define an Oracle Object Type containing all columns of the table, then use that object type as the data type of the single parameter that you pass.

So, instead of doing for example:
call MY_TABLEA.INSERT_NEW(col1 DATE,col2 NUMBER,col3 VARCHAR,

                     col4 NUMBER,...etc);
you do:
fill-in the individual values in my_row object type; call MY_TABLEA.INSERT_NEW(my_row TABLEA_OBJ);

BTW: Oracle Designer has the ability to define these object types for you automatically from a single row definition. And JPublisher will create the Java struct code for you as well. Half the work. Clearer now?
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_optusnet.com.au



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 Mon May 03 2004 - 08:47:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US