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>