Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: Wrapping all tables with packages and scalability
> 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:
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
-- 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