Receive a db link name as a variable in Oracle PLSQL

Question

I have the following function in PLSQL which connects remotely to different Database Links to change passwords:

FUNCTION fun_change_password(DB_LINK_VARIABLE varchar2)
    RETURN binary_integer IS

    jobid binary_integer;

  BEGIN
      dbms_job.submit@DB_LINK_VARIABLE (jobid,'begin execute immediate ''alter user MYUSER identified by mypassw''; end;');
      COMMIT;
      RETURN jobid;
  END;

My goal is to specify which DB Link to use sending its name in a varchar2 variable called *DB_LINK_VARIABLE*. But when I compile this into a package, the parser sends me an error:

PLS-00352: Unable to access another database 'DB_LINK_VARIABLE'

Obviously, I pre-configured and tested all my datalinks and works perfectly.

How can I use variable 'DB_LINK_VARIABLE' into this code?

@OldProgrammer Actually, there is a way to do this using 'dynamic SQL' and SYS_REFCURSOR data-type, but I found a problem with quotation. Check this example: CREATE OR REPLACE FUNCTION pro_test (pv_dblink VARCHAR2) IS emp_refcur SYS_REFCURSOR; empno VARCHAR2(20); ename VARCHAR2(20); BEGIN OPEN emp_refcur FOR 'SELECT empno, ename FROM emp@'||pv_dblink; LOOP FETCH emp_refcur into empno,ename; EXIT WHEN emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Employee no: '||empno||'/ Employee name: '||ename); END LOOP; END; — Guillermo Garcia
– Guillermo Garcia, Commented Jan 13, 2014 at 19:15

user272735 · Accepted Answer · 2014-01-16 06:41:28Z

2

You can do this with dynamic SQL by executing an anonymous PL/SQL block.

Below is a simple example where I execute dbms_utility.get_time function over a database link.

$ cat so35.sql
declare
  function remote_time(p_dblink in varchar2) return number is
    v_time number; 
  begin  
    execute immediate
      'begin :time := dbms_utility.get_time@' || p_dblink || '; end;'
    using out v_time;
    return v_time;
  end;
begin
  dbms_output.put_line('time = ' || remote_time('foo'));
end;
/


SQL> select dbms_utility.get_time as local, dbms_utility.get_time@foo as remote from dual;

     LOCAL     REMOTE
---------- ----------
  77936814 1546395927

SQL> @so35.sql
time = 1546396850

PL/SQL procedure successfully completed.

SQL>

answered Jan 16, 2014 at 6:41

user272735

10.6k9 gold badges72 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maheswaran Ravisankar Over a year ago

I see.. You are right, so, the user can just make a minor change in his code to make it work! Thanks for letting me know my mistake!

Maheswaran Ravisankar · Accepted Answer · 2014-01-13 15:42:14Z

1

PLS-00352: Unable to access another database 'DB_LINK_VARIABLE'

Error message shows, oracle is looking for a db link called DB_LINK_VARIABLE instead of the value associated to it.

You may need to do a check on variable, and make the hardcoding of the db link , instead of using a bind variable for it.!

Functions are compiled code in DB, so I guess oracle would do a semantic check on this during compilation itself, rather than doing it in runtime.

If it was just a SQL call to remote db, EXECUTE IMMEDIATE would have been used. Since it is PL/SQL there is no way for it, but for having multiple IF conditions, to validate the variable name, and making the full name in your PL/SQL block.

edited Jan 13, 2014 at 15:42

answered Jan 13, 2014 at 15:28

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

1 Comment

user272735 Over a year ago

The explanation of PLS-00352 is correct, but the conclusion that this can't be done with dynamic SQL is incorrect !

Collectives™ on Stack Overflow

Receive a db link name as a variable in Oracle PLSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related