Re: Passing one array per field into procedure.

From: Mark D Powell <>
Date: Wed, 23 Feb 2011 06:26:27 -0800 (PST)
Message-ID: <>

On Feb 21, 12:57 pm, seanD <> wrote:
> 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

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 -- Received on Wed Feb 23 2011 - 08:26:27 CST

Original text of this message