select from db link inside package body

Question

When creating a package body i cannot have a select from another db link inside the package body? I dont know the value of the dblink name from before therefore

Create or replace package body sth as 
 procedure getvalues(linkname in char)
 is
 begin
 select col1 from table1@linkname;
end getvalues;
end sth;

When i create this i get that table or view does not exist.. The link name i dont know it from before hand its in parameter which will be used as:

getvalues(linkname => dbname);

How can you have these values inside the package body?

Answer 1

If you don't know the name of the database link at compile time, you'd need to use dynamic SQL. Regardless of whether you're using dynamic SQL or static SQL, though, a procedure can't just run a SQL statement. It would have to do something with the results. You could open a sys_refcursor either as a local variable or as an OUT parameter. You could iterate through the results, fetching the data and doing something with the results. The fact that you have declared a procedure rather than a function implies that you want to modify the database state somehow rather than simply returning a sys_refcursor. But the name of the procedure implies that maybe you really want a function that returns a sys_refcursor.

If you really want a function that returns a sys_refcursor

CREATE OR REPLACE FUNCTION getValues( p_dblink IN VARCHAR2 )
  RETURN SYS_REFCURSOR
IS
  l_rc SYS_REFCURSOR;
BEGIN
  OPEN l_rc FOR 'SELECT col1 FROM table1@' || p_dblink;
  RETURN l_rc;
END;