Oracle sql types over dblink

Question

I have two schemas: A and B (Oracle 9). At the A there is a dblink to B. At the B there is a package, that i calls from A. Procedures in B package can returns varying count results and i think that returning a collection is a better way for this reason.

create type B.tr_rad as object (
  name     varchar2(64)
 ,code     number
 ,vendor   number
 ,val      varchar2(255)
 ,num      number
);

create type B.tt_rad as varray(256) of B.tr_rad;

But from A scheme I cannot use tt_rad type because using SQL-types by dblink is not supported. DBMS_SQL is not supported cursors. Create types with same OID is impossible.

I think to use temporary tables. But firstly it is not that good (after the remote function returns the value, calling side must select collection from remote table). And there are fears of a slowdown of work with temporary tables.

Maybe who knows the alternative interaction?

Answer 1

See this existing discussion

referencing oracle user defined types over dblink

Answer 2

Read the whole link that Matthew posted, all the comments. We tried to get this to work with the OID. Never worked.

If you can get it to work, please post an entire working demo.

Answer 3

An alternative interaction is to have one database with schemas A and B instead of two databases with a database link.

Answer 4

I've had similar problems in the past. Then I came to the conclusion that fundamentally Oracle's db links are "broken" for anything but simple SQL types (especially UDT's, CLOBS may have problems, XMLType may as well). If you can get the OID solution working then good luck to you.

The solution I resorted to was to use a Java Stored procedure, instead of the DB Link.

Characteristics of the Java Stored Procedure:

1. Can return a "rich set of types", just about all of the complex types (UDT's, tables/arrays/varrays) see Oracle online documentation for details. Oracle does a much better job of marshalling complex (or rich) types from java, than from a DBLink.
2. Stored Java can acquire the "default connection" (runs in the same session as the SQL connection to the db - no authentication issues).
3. Stored Java calls the PL/SQL proc on the remote DB, and the java JDBC layer does the marshaling from the remote DB.
4. Stored Java packages up the result and returns the results to the SQL or PL/SQL layer.

It's a bit of work, but if you have a bit of java, you should be able to "cut and paste" a solution together from the Oracle documentation and sample.

I hope this helps.

Answer 5

My solution. On the side B i create temporary table like the collection record. At the A side i have a DBMS_SQL wrapper that calls procedure over dblink. This procedure writes result collection in the temporary table. After successful completion remote procedure i select results from remote temporary table and transform it to local collection type.

Limitations 1. the need for permanent object synchronization. 2. impossibility use A-side procedure (that call remote procedure) in SQL query. 3. the complexity of using.