REF CUR and dynamically generating sql

Hi ya,
I'm trying to write a proc that querys the status of dbms_jobs jobs
from various db's and to supply the link to the proc at run time.

I'm trying to do it with a ref cur as below, do I need to use a ref cur? If so any suggestions on what I am running into?

Thanks

Steve

SQL> CREATE OR REPLACE PROCEDURE test (p_link_name VARCHAR)
2 AS
3 TYPE dblink_rec IS record (what VARCHAR2(2000),last_date DATE, next_date DATE, failures NUMBER);
4 TYPE db_link_refcure_type IS ref cursor RETURN dblink_rec;
5 dblink_refcur db_link_refcure_type;
6 dblink dblink_rec;
7 BEGIN
8 OPEN db_link_refcur FOR
9 SELECT what, last_date, next_date, failures FROM dba_jobs@ || p_link_name;
10 FETCH dblink_refcur INTO dblink_rec;
11 WHILE dblink_refcur%FOUND LOOP
12 dbms_output('The ' || rpad(cur_jobs.WHAT,15) || ' job last ran at ' || to_char(cur_jobs.LAST_DATE, 'DAY HH24:MI MM/DD/RR') ||

13 ' and will run again ' || to_char(cur_jobs.NEXT_DATE, 'DAY HH24:MI MM/DD/RR'));
14 FETCH dblink_refcur INTO dblink_rec;
15 end loop;
16 end;
17 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/1 PL/SQL: SQL Statement ignored
9/60 PL/SQL: ORA-01729: database link name expected
SQL>

Something like:

sql_string varchar2(200);

sql_string := 'SELECT what, last_date, next_date, failures FROM dba_jobs@' || p_link_name;
OPEN db_link_refcur FOR sql_string;


P.S: http://download-west.oracle.com/docs...9dyn.htm#26796

eureka!, thanks for the push in the right direction

it works as below now

CREATE OR REPLACE PROCEDURE test (p_link_name VARCHAR)
AS
TYPE dblink_cur_type IS REF CURSOR;
cur dblink_cur_type;
v_sql_string VARCHAR2(1000);
what VARCHAR2(1000);
last_date DATE;
next_date DATE;
failures NUMBER;
BEGIN
v_sql_string := 'SELECT what, last_date, next_date, failures FROM dba_jobs@' || p_link_name;
OPEN cur FOR v_sql_string;
LOOP
FETCH cur INTO what, last_date, next_date, failures;
exit when cur%notfound;
dbms_output.put_line(what);
END LOOP;
CLOSE cur;
END;
/