Bill - My guess is that part of the package compilation is to verify the
dblink as well as other objects, exist (for our own protection). What are
you trying to accomplish? Select the destination dynamically? Just a dumb
suggestion, maybe you can accomplish your task by creating a synonym and
then drop and create the synonym to make the change?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Friday, October 25, 2002 12:09 PM
To: Multiple recipients of list ORACLE-L
Hi,
I'm trying to incorporate a procedure in a package that needs to run using a
dblink name supplied at runtime. The dblink is used in the dml inside the
procedure, but when I compile the package I get "ORA-00942 table or view
does not exist" for all the sql referencing the dblink.
Here is a code snippet:
PROCEDURE CREATE_WC_OVER_DBLINK (
DBLINK VARCHAR2)
v_dblink VARCHAR2(100) := dblink;
BEGIN
INSERT INTO mm_product_temp (rec, rec_old, name, is_disabled,
is_workingcopy)
(SELECT mm_product_seq.nextval, rec, name, is_disabled, is_workingcopy
FROM mm_product@v_dblink
WHERE last_update IS null);
.....
END;
/
The ORA-00942 shows up for the line with the INSERT statement.
Looking for ideas as to how to do this. I saw a MetaLink forum posting
indicating that the way to do this is to create a refcursor, then use a
function to return the supplied sql to the refcursor, then open the
refcursor, etc. etc.
Is this the only way to do it?
thanks
bill
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
