I am trying to define a variable using an object created in other schema

[atulkbajpai]

E.G.
in DB1 I have created an object using "Create type" named as t_obj
in DB2 I have a package in which I have to
define a variable refering to DB1@t_obj
but each time error is thrown
pls-00331 illegal reference to t_obj@DB1_link (I am using Database Link)
Is it possible to do so?
Actual problem is that I have to pass certain sets of records from DB1 to DB2 in t_obj.
If not possible can you suggest an alternate way to do so?

[amitpatel66]

Restriction on Using User-Defined Types with a Remote Database

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.

You cannot use a database link to do any of the following:

Connect to a remote database to query, insert, or update a user-defined type or an object REF on a remote table

Use database links within PL/SQL code to declare a local variable of a remote user-defined type

Convey a user-defined type argument or return value in a PL/SQL remote procedure call

Can you elaborate on your requirement? What you mean by certain set of records?... Can you think of creating a View in remote database and using a DB LINK to read from the View from remote database in your source database?

[atulkbajpai]

The requirement is : There is a procedure in DB1 which has input as table of object type. These records are processed in DB1 and a final list is prepared which is forwarded to DB2 procedure and further processing is done in DB2 and returned to DB1 procedure call.

[amitpatel66]

You can try something like:

1. Create a database link in DB2 connecting to database DB1
2. Create a procedure proc1 in DB1 which takes input parameter of type table
3. From DB2 call proc1 in DB1 using DB link
4. Store the processed records in some temporary table
5. Call proc2 in database DB2
6. Use the temporary table that holds the processed records using database link