PL/SQL set a link as default at the begining of the script

Question

I need to select a link at the beginning in the script. Usually we do select links as below,

begin
select * from v$database@linkname;
end;

But now I need to select the link at the beginning something like this,

begin
select_link 'linkname';
select * from v$database;
end;

Thank you!

Answer 1

select * from v$database@linkname;

You cannot simply have a SELECT statement like that in PL/SQL. It expects an INTO clause.

If I understand correct;y, you want to parameterize the DATABASE LINK. I am afraid you need to (ab) use dynamic SQL.

For example,

SQL> var cur refcursor
SQL> DECLARE
  2    var_link varchar2(20);
  3  BEGIN
  4  var_link:='@your_db_link';
  5    OPEN :cur FOR 'SELECT * FROM dual'||var_link;
  6    END;
  7  /

PL/SQL procedure successfully completed.

SQL> print cur

D
-
X

SQL>