| URGENT - Database Link as a variable problem [message #2084] |
Thu, 20 June 2002 05:52  |
Emma B
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
I have two possible source databases for one program so I set up a parameter whereby I would pass the name of the database link to use at that time. Problem is this program will not compile. it give the error message
ORA-04054: database link SOURCE_DB does not exist.
Any ideas on how to solve this????
Thanks
|
|
|
|
| Re: URGENT - Database Link as a variable problem [message #2089 is a reply to message #2084] |
Thu, 20 June 2002 07:28  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Use dynamic sql. You may want to pass in your DB link as a parameter.
CREATE OR REPLACE PROCEDURE test(p_dblink VARCHAR2)
TYPE dblnk_REF_CURSOR IS REF CURSOR;
l_dblink_CUR dblnk_REF_CURSOR;
l_sql_statement VARCHAR2(100);
l_table_name VARCHAR2(30)
BEGIN
l_sql_statement := 'select * from '
|| l_table_name || '@' || p_db_link;
-- where p_db_link is the DATABASE LINK
OPEN l_dblink_CUR FOR l_sql_statement;
LOOP
FETCH l_dblink_CUR
INTO ... /* Variables */;
PROCESS RECORDS;
END LOOP;
CLOSE l_dblink_cur;
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
