Re: Passing one array per field into procedure.
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 23 Feb 2011 06:26:27 -0800 (PST)
Message-ID: <23be0350-e873-4f22-b7e2-d581dd515531_at_z3g2000prz.googlegroups.com>
On Feb 21, 12:57 pm, seanD <sean.den..._at_gmail.com> 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
Date: Wed, 23 Feb 2011 06:26:27 -0800 (PST)
Message-ID: <23be0350-e873-4f22-b7e2-d581dd515531_at_z3g2000prz.googlegroups.com>
On Feb 21, 12:57 pm, seanD <sean.den..._at_gmail.com> 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