Search code examples
oraclecollectionsuser-defined-typesdblink

Oracle sql types over dblink

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?

Solution

  • 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.