select statment with dynamic dblink

[lelo25]

Hello,
i need to execute a select statment with dynamic dblink
select cust_id into v_id
from cust_table@dblink003
i must change dblink003 to dblink004 ,dblink005 etc...
it depend what user enter in the form.
What is the way to write this select ?
please help

 

[HimanB]

Hi,
You can make use of Ref Cursor as follows:

Procedure XXX IS
TYPE CUST_TYPE IS REF CURSOR;  
CUST_REC     OUTFITTER_TYPE;
L_DBLINK    VARCHAR2(100):=<NAME OF DBLINK>;
V_ID NUMBER(10):=NULL;
BEGIN
OPEN CUST_REC FOR 'SELECT CUST_ID FROM CUST_TABLE@'||L_DBLINK;
  LOOP
   Fetch CUST_REC into V_ID;
   EXIT WHEN CUST_REC%NOTFOUND; 
  END LOOP;
EXCEPTION
  .............
END;

 

[lelo25]

hello,
When i have compiled this procedure i have had 2 errors:
1-error 103 encountered the symbol "FOR' when expecting one of the following "."".........
2-error 103 encountered the symbol "REF' when expecting one of the following ".""...
Please help

 

[HimanB]

Sorry There was a Typo Mistake in the code.

Regards
Himanshu

CREATE or REPLACE Procedure XXX IS
TYPE CUST_TYPE IS REF CURSOR;
CUST_REC     CUST_TYPE;
L_DBLINK    VARCHAR2(100):='dbn005';
V_ID NUMBER(10):=NULL;
BEGIN
OPEN CUST_REC FOR 'SELECT CUST_ID FROM <SCHEMANAME>.CUST_TABLE@'||L_DBLINK;
  LOOP
   Fetch CUST_REC into V_ID;
   EXIT WHEN CUST_REC%NOTFOUND;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
   dbms_output.put_line(SQLERRM);
END;