Re: Re: Wrapping all tables with packages and scalability
- From: "Nuno Souto" <dbvision@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 May 2004 23:48:49 +1000
----- Original Message -----
From: <ryan.gaffuri@xxxxxxx>
> 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@xxxxxxxxxxxxxxx
----------------------------------------------------------------
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:
