All,

I hope this is not too vague of a question, but let me know if better
clarification is needed. I am going to be replacing a java/hibernate
based "data feed" with a pl/sql package. By data feed, I mean Oracle
fusion will be used to call our stored procedures with data to be
integrated into our schema. By integrated, I mean - foreign keys
looked up and populated, data validations, etc. It had been strongly
suggested that I use arrays as the arguments to the stored procedure
instead of "table of" .

My questions are: Anyone have experience calling a stored procedure
with one array per field where there are a large number of fields
(like 30+)? I ask because there are not really 3 dimensional arrays
to be used righ? Does anyone have any thoughts on this approach?
Again, this will be called from java. the database is 10G. Any
ideas (links) on how to manage these arays once they are passed in?

Any ideas are welcomed. And again if I have been unclear please let
me know.

Thanks !

Steve

On Feb 21, 12:57*pm, seanD <sean.den... (AT) gmail (DOT) com> wrote:

Can you provide a psuedo-code example?

would you be referring to PL/SQL Collections and Records?
http://download.oracle.com/docs/cd/E...composites.htm
http://download.oracle.com/docs/cd/B...4/05_colls.htm

I've been involved with something similar a number of years ago and it
worked very well.
That was on 10gR1.

I believe when you say "table of" you're referring to plsql
associative arrays and for "arrays" you mean nested table types (i.e.
create type).

At the time, we benchmarked different approaches and, in our
cirumcstances, using Oracle collections outperformed standard java
statement batching.

I'm not sure I'd go down the route of one array per field, although
it's certainly viable.

My preference would normally be to create an Oracle object type
(create type objecttypename as object (attr1, attr2...) and then
collections of those object types (create type tabletypename as table
of objecttypename). Certainly 30+ attributes to an object type would
not give me any worries, depending on what those attributes were (e.g.
CLOBS and BLOBS might give me second thoughts, especially if multiple
large LOBS I'd probably rule out collections altogether).


These can then be used directly in INSERT ... SELECT statements,
UPDATE statements, MERGE statements, etc using the
TABLE(CAST...AS ...) structures - this might tie in easily with your
data validation, foreign key lookups etc.
This fits directly into the "Do it with a single SQL statement" mantra
which normally delivers the best performance.

What you need to remember when dealing with collections in SELECT ..
TABLE (CAST...) statements is how the default cardinalities are
handled. It's related to the block size of the database but there are
ways of providing better information about the size of collections.

Finally, the main downside to using Oracle collections of object
types, is that the Java can be a bit clumsy. I've no idea how Oracle
Fusion would make any of this easier or harder.
I'm not a java programmer but I did a demo of some performance
comparisons of statement batching versus oracle collections here:
http://orastory.wordpress.com/2007/0...your-jdbc-app/
You'll note my crappy java but it does show the INSERT... SELECT...
TABLE statement to give you an idea of what I'm talking about.
If it had been anything other than a demo, then I would have had that
in a stored proc etc.

Hope this is relevant and helps.

Cheers,
Dominic

On Feb 21, 12:57*pm, seanD <sean.den... (AT) gmail (DOT) com> wrote:
What about creating and passing an associate array of records? With
an earlier version of VB I know one of our developers had to use a
associate array or pl/sql table as it is called for each column
because of VB restriction with that version but he had only about 6
columns to deal with. In theory 30 parameters just means more coding,

-- example definition of array of records
type r_ats is record (
tablespace_name varchar2(15)
,extent_sz number
,extents_avail number
,extents_used number
,is_tbl v_is_tbl%type
,is_mme v_is_mme%type
,is_lg v_is_lg%type
,is_spec v_is_spec%type
) ;
--
type t_atsr is table of r_ats
index by binary_integer ;
t_ats t_atsr ; -- alloc plsql tbl 4 new tblspc data
t_empty t_atsr ; -- empty tbl 2 allow freeing mem during
testing


HTH -- Mark D Powell --