Home » SQL & PL/SQL » SQL & PL/SQL » Passing array of records as the input parameter to store procedure
Passing array of records as the input parameter to store procedure [message #398735] Fri, 17 April 2009 05:21 Go to next message
RaviRajHulk
Messages: 7
Registered: April 2009
Junior Member
I have a table TESTTABLE

SQL> desc testtable;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
NAME VARCHAR2(30)
ACCOUNT NUMBER
DATETIME DATE


I have a collection
CREATE OR REPLACE TYPE v_acc_number AS VARRAY(10)OF NUMBER(10);

and a sequence
create sequence seq_testing start with 1 increment by 1;

I have created a stored procedure to insert values into Testtable. The stored procedure takes account number as array argument.


CREATE OR REPLACE procedure SYSTEM.testtableprocedure(
v_name in TESTTABLE.NAME%Type,
v_account in v_acc_number,
v_date in TESTTABLE.DATETIME%Type)
is
v_id_no TESTTABLE.ID%Type;


begin


for i in v_account.first..v_account.last
loop
select seq_testing.nextval into v_id_no from dual;
insert into TESTTABLE
values(v_id_no,
v_name,
v_account(i),
v_date
);
end loop;
end;
/


I do not want a particluar column to be as array argument. But I want an array of record (id,name,account and date) as input argument.
Id Name Account Date
1 Martin 2000 12/12/2008----array[0]
2 Harry 3000 04/12/2008 -----array[1]
3 Luther 4000 02/15/2009 -----array[2]
Re: Passing array of records as the input parameter to store procedure [message #398737 is a reply to message #398735] Fri, 17 April 2009 05:57 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
go through this site may be useful to you.
http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/seliverstov_multirows.html
Re: Passing array of records as the input parameter to store procedure [message #398740 is a reply to message #398735] Fri, 17 April 2009 06:00 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
STOP starting a new topic each hour for the same question.

Regards
Michel

[Updated on: Fri, 17 April 2009 06:00]

Report message to a moderator

Previous Topic: Trigger Mutating solved now deadlock error
Next Topic: Date format
Goto Forum:
  


Current Time: Thu Dec 08 14:09:53 CST 2016

Total time taken to generate the page: 0.10641 seconds