Home » SQL & PL/SQL » SQL & PL/SQL » pass values to array parameter
pass values to array parameter [message #357700] Thu, 06 November 2008 05:13 Go to next message
rags11
Messages: 21
Registered: October 2008
Junior Member
IS there any way that i can pass the values as an array to a procedure?
I have a procedure sample
sample (a dbms_sql.VARCHAR2_table
,b dbms_sql.VARCHAR2_table)
now,i want to test this from backend.how can i pass the values to this array?
Thank you.
Re: pass values to array parameter [message #357703 is a reply to message #357700] Thu, 06 November 2008 05:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can only call a procedure that takes a collection as a parameter from within PL/SQL. So what you would do is to declare the collection in a PL/SQL block, populate it, then call your sample procedure.

Ross Leishman
Re: pass values to array parameter [message #357763 is a reply to message #357700] Thu, 06 November 2008 08:31 Go to previous messageGo to next message
rags11
Messages: 21
Registered: October 2008
Junior Member
yes.but how to initailize the arrays with values
can i pass like this?
a=(10,20,30,40)
b=('a','b','c');
??
Re: pass values to array parameter [message #357766 is a reply to message #357763] Thu, 06 November 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 5 Using PL/SQL Collections and Records

Regards
Michel
Re: pass values to array parameter [message #357771 is a reply to message #357700] Thu, 06 November 2008 08:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
And keep in mind that dbms_sql.VARCHAR2_table is declared as
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
Re: pass values to array parameter [message #357830 is a reply to message #357771] Thu, 06 November 2008 20:27 Go to previous messageGo to next message
rags11
Messages: 21
Registered: October 2008
Junior Member
I have initialized like this
declare
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
v_a varchar2_table ;
begin
v_a := varchar2_table ('x','y','aa');
v_b := varchar2_table ('b','c','dd');

sample(v_a,v_b);
end;

Iam getting the following error
PLS-00306 : wrong number or types of arguments in call to sample
Re: pass values to array parameter [message #357845 is a reply to message #357700] Thu, 06 November 2008 22:32 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This search May help u

Smile
Rajuvan.
Re: pass values to array parameter [message #357854 is a reply to message #357830] Thu, 06 November 2008 23:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: pass values to array parameter [message #357868 is a reply to message #357700] Thu, 06 November 2008 23:45 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
v_a varchar2_table ;

Why did you create the new type and did not simply use dbms_sql.VARCHAR2_table type? Although they have the same definition, Oracle treats them as different types.

My note was aimed to the fact, that populating of INDEX BY table is different than the one you tried (and still do). See Rajuvan's link to examples on this server and/or the documentation link posted by Michel (key word: INDEX BY).
Previous Topic: Newbie questions about regular expressions.
Next Topic: cursor
Goto Forum:
  


Current Time: Sun Dec 04 06:17:25 CST 2016

Total time taken to generate the page: 0.04335 seconds