REF CUR and dynamically generating sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: REF CUR and dynamically generating sql

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 08:39 AM.

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
  •  



Click Here to Expand Forum to Full Width