OraFAQ Forum: SQL & PL/SQL » Passing an collection of data as an input parameter to an procedure and insert them into a table?

Home » SQL & PL/SQL » SQL & PL/SQL » Passing an collection of data as an input parameter to an procedure and insert them into a table? (11g running on Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Passing an collection of data as an input parameter to an procedure and insert them into a table? [message #604458]

Tue, 31 December 2013 01:05

ashwanth77
Messages: 95
Registered: April 2013
Location: India

Member

I'm trying to pass an collection of (varchar) data into an Oracle procedure

pr_savecust_bill(
product_id(1,2,3,4,5,6,7,8,9,10,11,12..so on

);

Report message to a moderator

Re: Passing an collection of data as an input parameter to an procedure and insert them into a table? [message #604459 is a reply to message #604458]

Tue, 31 December 2013 01:21

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Here's an example:

SQL> create or replace procedure p (p_in sys.odcivarchar2list, p_out out varchar2, p_nb out int)
  2  is
  3    res varchar2(32000);
  4  begin
  5    p_nb := p_in.count;
  6    for i in 1..p_in.count loop
  7      res := res || ' ' || p_in(i);
  8    end loop;
  9    p_out := substr(res,2);
 10  end;
 11  /

Procedure created.

SQL> var result varchar2(4000)
SQL> var nb number
SQL> set autoprint on
SQL> exec p (sys.odcivarchar2list('Michel','Cadot'), :result, :nb);

PL/SQL procedure successfully completed.

        NB
----------
         2

RESULT
-------------------------------------------------------------------------------------------------
Michel Cadot

[Updated on: Tue, 31 December 2013 01:22]

Report message to a moderator