passing a collection as parameter from a procedure to another stored proc
613545Dec 13 2007 — edited Dec 16 2007hi, 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