Referencing Oracle user defined types over DBLINK?

19,397

Solution 1

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.

Solution 2

I think the underlying issue is that Oracle doesn't know how to automatically serialize/deserialize your custom type over the wire, so to speak.

Your best bet is probably to pass an XML (or other) representation over the link.

Share:
19,397
ScottCher
Author by

ScottCher

BS Mechanical Engineering; lead architect and developer with systems engineering experience, 4yrs C# experience, 10yrs Oracle experience (SQL, PL/SQL), 10yrs ColdFusion/web experience.

Updated on June 27, 2022

Comments

  • ScottCher
    ScottCher almost 2 years

    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.

  • Dave Costa
    Dave Costa over 15 years
    It's not really due to "serialization". It's because custom types are given an object identifier, and if the OID for two different types is different they are considered different types, even if they have the same name. See tuinstoel's answer.
  • ScottCher
    ScottCher over 15 years
    Working with an OUT variable as shown in my example this doesn't work. I get "wrong number or types of arguements in call to <the procedure with the remote object type as an OUT param>"
  • ScottCher
    ScottCher over 15 years
    Just to be clear, I can't make use of the object table example you provided above, I need this to work using the object as an out param for a procedure call over the dblink.
  • ScottCher
    ScottCher over 15 years
    I've tried implementing tuinstoels answer by defining both types with the same OID but that didn't work. Oracle still sees the two types as different.
  • tuinstoel
    tuinstoel over 15 years
    You can't use a user objects as an out parameter over a database link. But you can write a procedure that inserts the user object together with a unique number in a table and returns the unique number. After that you can select your object from this table with the aid of the unique number.