Type via dblink

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
marcos.magri
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 10
Registrado em: Qua, 13 Ago 2008 1:43 pm
Localização: São Paulo
Marcos Magri

Olá pessoal,

Como eu acesso um type via dblink?
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5018
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

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
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 10
Registrado em: Qua, 13 Ago 2008 1:43 pm
Localização: São Paulo
Marcos Magri

Obrigado,

Isso me ajudou muito!!!
Responder
  • Informação