Type via dblink

[marcos.magri]

Olá pessoal,

Como eu acesso um type via dblink?

[dr_gori]

No manual, indica as restrições de uso de TYPES e DBLINKS:

http://download.oracle.com/docs/cd/B283 ... tm#i462606

Restriction on Using User-Defined Types with a Remote Database

Objects or user-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database. Oracle Database restricts use of a database link as follows:

* You cannot connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table.

You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.
* You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.
* You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call.

Mas parece que dá pra fazer algo:
http://download.oracle.com/docs/cd/B283 ... m#ADDCI210
http://stackoverflow.com/questions/3782 ... ver-dblink

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.

Selecionar tudo

SELECT SYS_OP_GUID() FROM DUAL; 

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

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

Selecionar tudo

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

Now create a table:

Selecionar tudo

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:

Selecionar tudo

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.

[marcos.magri]

Obrigado,

Isso me ajudou muito!!!