Passing array having the columns name as its elements [message #398713] |
Fri, 17 April 2009 04:00  |
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 as input argument which will have all the columns (id,name,account and date) as its element .
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]
[Updated on: Fri, 17 April 2009 04:02] Report message to a moderator
|
|
|
|
|