Oracle-L: RE: supplying dblink name to package at runtime

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 25 Oct 2002 10:19:20 -0800
Message-ID: <F001.004F3F91.20021025101920@fatcity.com>

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.
dwilliams_at_lifetouch.com

-----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_at_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: Bill.Magaliff_at_lendware.com

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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------