通过DBLINK引用Oracle用户定义的类型？

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.

我在两个不同的Oracle实例上使用两个不同的Oracle模式。我已经定义了几种类型和类型集合来在这些模式之间传输数据。我遇到的问题是,即使类型具有完全相同的定义(用于在模式中创建两个集的相同脚本),Oracle将它们视为不可互换的不同对象。

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

我考虑将传入的远程类型对象转换为相同的本地类型,但是我得到一个关于跨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.

这失败是因为REMOTE过程调用无法理解MyType,因为它将LocalType和RemoteType视为不同的对象类型。

I tried DECLARING MyType as follows as well:

我也尝试了DECLARING MyType,如下所示:

  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.

但我得到另一个关于跨dblinks引用类型的错误。类型之间的CASTing也不起作用,因为为了进行强制转换,我需要跨dblink引用远程类型 - 同样的问题,同样的错误。我也尝试使用SYS.ANYDATA作为在两个实例之间穿过的对象,但它得到了类似的错误。

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.

更新:尝试使用相同的OID(使用SYS_OP_GUID()手动检索)在DBLINK的两端声明对象类型,但Oracle仍然“看到”两个对象不同并抛出“错误数量或类型的争论”错误。

2 个解决方案

#1

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

我已经阅读了Oracle文档,这并不是很困难。

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

您需要在两个数据库中的类型定义中添加OID。

You can use a GUID as OID.

您可以将GUID用作OID。

SELECT SYS_OP_GUID() FROM DUAL; 

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

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

现在使用SAME OID在两个数据库中创建UDT。

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:

现在,您可以通过其他数据库中的dblink从表中进行选择:

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.

如果第一次尝试通过dblink选择时出现错误ORA-21700,只需重新连接即可。

#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.

我认为潜在的问题是Oracle不知道如何通过网络自动序列化/反序列化您的自定义类型,可以这么说。

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

您最好的选择可能是通过链接传递XML(或其他)表示。

#1