Home » SQL & PL/SQL » SQL & PL/SQL » How to pass an array to an Procedure (Toad, SQL plus)
icon7.gif  How to pass an array to an Procedure [message #398183] Wed, 15 April 2009 10:02 Go to next message
RaviRajHulk
Messages: 7
Registered: April 2009
Junior Member
I have to pass an array to an procedure and also insert into a particular table..my stored procedure is like this
create or replace procedure pAccountNo(slNo in number,
p_AccountNo in string,
p_Amount in varray[5])
is
begin
insert into account
values(1,10,2000);
insert into account
values(2,10,3000);
insert into account
values(3,10,4000);
end;
Re: How to pass an array to an Procedure [message #398184 is a reply to message #398183] Wed, 15 April 2009 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


So what is your question?
Re: How to pass an array to an Procedure [message #398185 is a reply to message #398183] Wed, 15 April 2009 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create an array type: CREATE TYPE.

Regards
Michel
icon9.gif  Re: How to pass an array to an Procedure [message #398191 is a reply to message #398183] Wed, 15 April 2009 10:24 Go to previous messageGo to next message
RaviRajHulk
Messages: 7
Registered: April 2009
Junior Member
Sorry ..i forgot to put the question...
Actually i am confused as to what data type to give when i am calling an array as the input parameter..and also
if the array passed is of size 50, we cannot write 50 insert. Is there any way of using 'for' statement to insert all the values got in the array arguement in 1 insert statement. (The primary key is a sequence which will be increased for each insert inside the for statement.)
please help
Re: How to pass an array to an Procedure [message #398192 is a reply to message #398183] Wed, 15 April 2009 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow previously provided Posting Guidelines
Re: How to pass an array to an Procedure [message #398195 is a reply to message #398183] Wed, 15 April 2009 10:43 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
RaviRajHulk wrote on Wed, 15 April 2009 17:02
I have to pass an array to an procedure and also insert into a particular table..my stored procedure is like this

Seems you have very advanced version of Oracle which can create procedures based on templates. In Oracle 10.2.0.3, I got this:
SQL> create or replace procedure pAccountNo(slNo in number,
  2  p_AccountNo in string,
  3  p_Amount in varray[5])
  4  is
  5  begin
  6  insert into account
  7  values(1,10,2000);
  8  insert into account
  9  values(2,10,3000);
 10  insert into account
 11  values(3,10,4000);
 12  end;
 13  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE PACCOUNTNO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/19     PLS-00103: Encountered the symbol "[" when expecting one of the
         following:
         := . ) , @ % default character

SQL> 

When I want to use collection type, I have to create one as suggested by Michel. Then I use the same collection type in the caller.

By the way, it would be nice, if you would specify the calling environment (SQL*Plus, PL/SQL, JDBC, ...). Do you think that this information is not important?
Re: How to pass an array to an Procedure [message #398238 is a reply to message #398183] Wed, 15 April 2009 13:42 Go to previous messageGo to next message
r-a-v-i
Messages: 1
Registered: April 2009
Junior Member
http://ravivedala.blogspot.com/2008/03/oracle-10g-passing-arrays-to-database.html
Re: How to pass an array to an Procedure [message #398408 is a reply to message #398238] Thu, 16 April 2009 03:03 Go to previous message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi Rajiv,

this might be helpfull for you.

SQL> select * from t1;

no rows selected

SQL> create or replace  type v_a as varray(3) of varchar2(1);
  2  /

Type created.

SQL>  create or replace procedure pass_ar ( i in number ,
  2                                         j in number  ,
  3                                         arr in v_a)
  4   as
  5   begin
  6   for i in arr.FIRST .. arr.LAST
  7   loop
  8   insert into t1 values (i,j,arr(i));
  9   commit;
 10   end loop;
 11   end;
 12   /

Procedure created.

SQL>  declare                       
  2   ar v_a := v_a();              
  3   begin                         
  4   ar.extend(3);                 
  5        ar(1):='R';              
  6        ar(2):='K';              
  7        ar(3):='G';              
  8       pass_ar (10,20,ar);       
  9    end;                         
 10   /

PL/SQL procedure successfully completed.

SQL> select * from t1;

         I          J K
---------- ---------- --------------------
         1         20 R
         2         20 K
         3         20 G 


You can do somethng like this according to your requirement.

[Updated on: Thu, 16 April 2009 03:05]

Report message to a moderator

Previous Topic: Multiple Record return [multi-merge]
Next Topic: Regarding Exchange of column value between module and program column using trigger
Goto Forum:
  


Current Time: Tue Dec 06 04:33:42 CST 2016

Total time taken to generate the page: 0.19033 seconds