-
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>
I'm stmontgo and I approve of this message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|