通过DBLINK引用Oracle用户定义的类型？ [英] Referencing Oracle user defined types over DBLINK?

问题描述

我在两个不同的Oracle实例上使用两个不同的Oracle模式。我定义了几个类型和类型集合在这些模式之间传输数据。我遇到的问题是，即使类型具有完全相同的定义（用于在模式中创建两个集合的相同脚本），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.

我想将传入的远程类型对象转换为相同的本地类型，但是我得到一个关于在dblink中引用类型的错误。

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

m执行以下操作：

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;

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

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

我也尝试DECLARING MyType如下：

I tried DECLARING MyType as follows as well:

  MyType REMOTE_SCHEMA.RemoteType@DBLINK;

但我得到另一个关于引用dblinks类型的错误。类型之间的CASTing不工作，因为为了转换，我需要引用远程类型跨dblink - 同一问题，相同的错误。我也尝试使用SYS.ANYDATA作为在两个实例之间交叉的对象，但它也有类似的错误。

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.

任何想法？

UPDATE：
尝试使用相同的OID在DBLINK的两端声明对象类型（使用 SYS_OP_GUID / code>），但Oracle仍然看到两个对象不同，并抛出一个错误的数字或类型的争论错误。

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.

推荐答案

我已经阅读过Oracle文档，这不是很难。

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

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

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

您可以使用GUID作为OID。

You can use a GUID as OID.

SELECT SYS_OP_GUID() FROM DUAL; 

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

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

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

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

现在创建一个表：

create table testlink 
( name testlinktype);

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

commit;

现在您可以通过其他数据库中的dblink从表中选择：

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')

如果您尝试选择时出现错误ORA-21700 dblink第一次，只是重新连接。

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

这篇关于通过DBLINK引用Oracle用户定义的类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！