views:

1721

answers:

5

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?

A: 

See this existing discussion

referencing oracle user defined types over dblink

Matthew Watson
i read this before i post my question
drnk
A: 

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.

i can`t use OID because.. central database have a code, that can extend on satellite databases (over dblink). but code identity is not mandatory requirement for the whole system. if i use OID in one satellite, i need use IOD in other 50 satellites. Same OID is contrary to the system ideology
drnk
Not sure why you're telling me, I'm the guy saying it doesn't work.
A: 

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

tuinstoel
no, its a distributed database-network. the core in the center and many points around. dblink - its a fastest and prefferable way
drnk
+1  A: 

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.

Aussie Craig
The OID solution works on my machine. What Oracle version did you use?
tuinstoel
It was quite a few years ago (probably 8, possibly 9i). My specific problem was CLOBS or VARCHAR2(4000) blowing up. The java path allows you to sidesteps the limitations of DBLinks.
Aussie Craig
8 and 9 are a little old. It works on my machine with OIDs, I made a link from Oracle 11.1 to Oracle 10.2 .
tuinstoel
oo, Java is sounds well, but what about speed in this solution?
drnk
A: 

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.

drnk