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!

Calling a procedure with owa.vc_arr as parameter type to a dblink package

604377Feb 20 2008 — edited Feb 20 2008
We have a database A with package A and database B with package B.
In database B i created a dblink to database A so i could use the packages, tables,....
However... when i call a function that takes a para;eter of the type owa.vc_arr i get an error:

ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'P_SAVE_ANSWER'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

This is the code executing from databse B:
declare
v_arr owa.vc_arr;
begin
v_arr(1) := 'Y';
m_rch.P_SAVE_ANSWER('Q001','Q001.000',21,'MATY','YN',v_arr,'');
end;

m_rch is a synonym to m_rch@dblink.
When i execute the exact same code in database A it works.

Then i tried to create a DB connection fro; A to B and defined the owa.vc_arr as following:
myArr IN owa.vc_arr@linkToB

When i then execute the code in databse B i get another error:
ORA-04052: error occurred when looking up remote object SYS.OWA@linkToB.WORLD
ORA-00604: error occurred at recursive SQL level 1
ORA-02019: connection description for remote database not found

When i execute a select * from table@linkToA or linkToB, it works so my connections are all workink.

How can i resolve this issue? Do i have to use another way to pass the owa.vc_arr to another DB?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 19 2008
Added on Feb 20 2008
2 comments
790 views