Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

passing a collection as parameter from a procedure to another stored proc

613545Dec 13 2007 — edited Dec 16 2007
hi, i'm relatively new to oracle pl/sql

i'd like to know how to pass a collection (nested table) as paramter from a stored procedure or pl sql code to another stored procedure from a different database. i'm doing this because i need certain rows from a table from another database and i don't want to use a db link in my where clause because the latency between the two databases is too high.

i tried doing it this way:

--------------------------------------
declare

TYPE qvarchar2_table_100 IS TABLE OF VARCHAR2(100);

v1 varchar2_table_100;
begin

v1 := qvarchar2_table_100('dog');
test_@dev.world('cat',v1);

end;

---------------------------------

CREATE OR REPLACE PROCEDURE test_ (value in varchar2, value2 in varchar2_table_100)
is
begin
insert into test1 values (value);

commit;

END test_;
---------------------------

the type varchar2_table_100 already exists in our dev.world database, so i didn't have to declare/create it. i used the same definition in declaring the nested table as the one i found in dev.world:

TYPE qvarchar2_table_100 IS TABLE OF VARCHAR2(100);

however this error shows up:

ORA-06550: line 9, column 1:
PLS-00306: wrong number or types of arguments in call to 'TEST_'

----------------------------------

do you guys have any idea how i can get around this? i tried searching for answers in this forum but i haven't found anything bout passing from an oracle pl/sql script..most of the similar questions are bout java to stored procedure paramater passing.

thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 13 2008
Added on Dec 13 2007
7 comments
2,015 views