Search code examples
oracletypescollectionsdblink

Referencing Oracle user defined types over DBLINK?

I'm working in two different Oracle schemas on two different instances of Oracle. I've defined several types and type collections to transfer data between these schemas. The problem I'm running into is that even though the type have exactly the same definitions (same scripts used to create both sets in the schemas) Oracle sees them as different objects that are not interchangeable.

I thought about casting the incoming remote type object as the same local type but I get an error about referencing types across dblinks.

Essentially, I'm doing the following:

DECLARE
  MyType  LocalType; -- note, same definition as the RemoteType (same script)
BEGIN
  REMOTE_SCHEMA.PACKAGE.PROCEDURE@DBLINK( MyType );  -- MyType is an OUT param
  LOCAL_SCHEMA.PACKAGE.PROCEDURE( MyType ); -- IN param
END;

That fails because the REMOTE procedure call can't understand the MyType since it treats LocalType and RemoteType as different object types.

I tried DECLARING MyType as follows as well:

  MyType REMOTE_SCHEMA.RemoteType@DBLINK;

but I get another error about referencing types across dblinks. CASTing between types doesn't work either because in order to cast, I need to reference the remote type across the dblink - same issue, same error. I've also tried using SYS.ANYDATA as the object that crosses between the two instance but it gets a similar error.

Any ideas?

UPDATE: Tried declaring the object type on both sides of the DBLINK using the same OID (retrieved manually using SYS_OP_GUID()) but Oracle still "sees" the two objects as different and throws a "wrong number or types of arguements" error.

Solution

  • I have read the Oracle Documentation and it is not very difficult.

    You need to add an OID to your type definitions in both databases.

    You can use a GUID as OID.

    SELECT SYS_OP_GUID() FROM DUAL; 

SYS_OP_GUID()
--------------------------------
AE34B912631948F0B274D778A29F6C8C

    Now create your UDT in both databases with the SAME OID. 

    create type testlinktype oid 'AE34B912631948F0B274D778A29F6C8C' as object
( v1 varchar2(10) , v2 varchar2(20) );
/

    Now create a table:

    create table testlink 
( name testlinktype);

insert into testlink values (testlinktype ('RC','AB'));

commit;

    Now you can select from the table via the dblink in the other database:

    select * from testlink@to_ora10;

NAME(V1, V2)
--------------------------
TESTLINKTYPE('RC', 'AB')

    If you get error ORA-21700 when you try to select via the dblink the first time, just reconnect.