Home » SQL & PL/SQL » SQL & PL/SQL » Passing array having the columns name as its elements
icon9.gif  Passing array having the columns name as its elements [message #398713] Fri, 17 April 2009 04:00 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 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

Re: Passing array having the columns name as its elements [message #398722 is a reply to message #398713] Fri, 17 April 2009 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not start a new topic continue to your previous one.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Passing array having the columns name as its elements [message #398726 is a reply to message #398713] Fri, 17 April 2009 04:51 Go to previous message
RaviRajHulk
Messages: 7
Registered: April 2009
Junior Member
Can i use an array argument which is of type record
Previous Topic: List of Months between range (merged 3)
Next Topic: Trigger Mutating solved now deadlock error
Goto Forum:
  


Current Time: Tue Dec 06 12:30:37 CST 2016

Total time taken to generate the page: 0.07830 seconds