-
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
-
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
Last edited by DaPi; 03-23-2004 at 09:39 AM.
-
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;
/
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
|