469,363 Members | 2,560 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,363 developers. It's quick & easy.

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

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?
Jan 14 '10 #1
3 5403
amitpatel66
2,367 Expert 2GB
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?
Jan 14 '10 #2
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.
Jan 14 '10 #3
amitpatel66
2,367 Expert 2GB
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
Jan 14 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Eric Liu | last post: by
7 posts views Thread by Harris | last post: by
1 post views Thread by =?Utf-8?B?VGVycmFuY2U=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.