Using collection types across database links
I'm working on a project currently where I am developing an interface across a database link for two applications. I would like to pass data across this link using procedures that use collection types as parameters to hold arrays of data.
In both databases, I ran the following statement to create a persistent collection type:
CREATE OR REPLACE TYPE varchar2_array_ty is table of varchar2(32767);
I then wrote a procedure on Database A to accept this input:
PROCEDURE foo (P_INPUT_ARR IN VARCHAR2_ARRAY_TY)
On Database B, where I had created (and tested to verify it was working) a database link to Database A, I wrote the following block to try and run that procedure:
l_input_arr VARCHAR2_ARRAY_TY := NEW VARCHAR2_ARRAY_TY();
This results in the following error:
PLS-00306: wrong number or types of arguments in call to 'foo'
If I run the same exact declare block from DatabaseA (obviously removing the @DatabaseA reference), it runs perfectly fine. It's only when I try to run it from DatabaseB that it fails.
I also tried delcaring the l_input_arr by stating:
l_input_arr VARCHAR2_ARRAY_TY@DatabaseA := NEW VARCHAR2_ARRAY_TY@DatabaseA();
That resulted in:
PLS-00331: illegal reference to Schema.VARCHAR2_ARRAY_TY@DatabaseA
I even tried creating identical record types within the calling and destination procedures (rather than using the stored collection types), but that resulted in the same "wrong number or types of arguements" error.
So it seems to me like there is no way to pass complex data objects across database links between Oracle databases because Oracle is incapable of determining that the two objects have the same declaration. This is very surprising to me, since Java is capable of passing these objects to Oracle via JDBC, I would have expected Oracle to be capable of this.
My current plan is to just parse the array input into delimited strings and pass data to the destination procedure that way, but I would prefer to use the appropriate objects to improve future reusability.
Thanks in advance!