通过 DBLINK 引用 Oracle 用户定义类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/378299/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Referencing Oracle user defined types over DBLINK?
提问by ScottCher
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.
我曾考虑将传入的远程类型对象强制转换为相同的本地类型，但出现跨 dblink 引用类型的错误。
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:
我也试过如下声明 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.
但我在跨 dblink 引用类型时遇到另一个错误。类型之间的 CAST 也不起作用，因为为了进行转换，我需要通过 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 仍然“看到”这两个对象不同，并抛出“错误数量或类型的争论”错误。
回答by tuinstoel
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 SAMEOID.
现在使用相同的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，只需重新连接即可。
回答by kurosch
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（或其他）表示。
