0

I need to execute a dynamic query.
I need to extract some values of a table. With these a values I can construct the table names where I must do another query and check every value that it gives me with a if clause.

I'm trying to make with cursor, but it seems don't let me put a variable in the second cursor.

What is the best method to do this. Could you help me please?

DECLARE
    CURSOR c_outer IS SELECT ACTIVE FROM COMM;
    CURSOR c_inner(p_name VARCHAR2) IS SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 HOURS FROM p_name WHERE  UPPER(STATUS) = 'EXECUTING';  
    HOURS NUMBER; 
    TAB VARCHAR2(30);

BEGIN

    FOR r IN c_outer LOOP
       TAB :='A'||c_outer||'JOB';

        OPEN  c_inner(TAB);

        FETCH c_inner INTO HOURS;
                IF HOURS > 24 THEN
                        DBMS_OUTPUT.PUT_LINE('MAXIMMO');
                END IF;

        CLOSE c_inner;

    END LOOP;

END;
0

You can declare a reference cursor type and a cursor variable based on that type. Then you define a select statement that the ref cursor will represent using a bind variable in the place of the dynamic table name you get from the outer cursor. Open the ref cursor for the sql and populate the bind variable as you go with the 'USING' clause. I've put the code below together but I haven't run or checked it in anyway so I suggest taking it as a guide to what you want rather than the line for line solution. On top of that I can't really tell what exactly you are trying to achieve.

DECLARE
    CURSOR c_outer IS
       SELECT 'A'||c_outer.active||'JOB' AS active
         FROM comm;

    TYPE innerCurTyp IS REF CURSOR;
    v_innerCur innerCurTyp    
    v_sql      VARCHAR2(1000);
    n_hours     NUMBER;

BEGIN
    FOR r_outer IN c_outer LOOP
       v_sql := q'[SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 HOURS FROM ]'|| r_outer.active || q'[  WHERE  UPPER(STATUS) = 'EXECUTING']';
       OPEN  v_innerCur FOR v_sql
       LOOP
          FETCH v_innerCur INTO n_hours;
                IF n_hours > 24 THEN
                    DBMS_OUTPUT.PUT_LINE('MAXIMMO');
                END IF;
          EXIT WHEN v_innerCur%NOTFOUND;
       END LOOP;
       CLOSE v_innerCur;

    END LOOP;

END; 
  • Thanks! It seem that the sentence OPEN v_innerCur FOR v_sql USING r_outer.active gives me an error: ORA-00903: invalid table name ORA-06512: at line 16 It's strange, I put before a dbms_output and I see that the table name is ok – user650034 Mar 17 '16 at 10:54
  • Edited. tried this method 5 minutes ago using tables on my system and it works for me. – BriteSponge Mar 17 '16 at 11:37
  • What you take from this is the technique I guess. Build a select and use a ref cursor to open it using the 'OPEN cursor FOR' syntax. – BriteSponge Mar 17 '16 at 11:39
  • Thank it works! What I don't understand is why I must use a reference cursor, maybe because I use a select with table_name parameter? – user650034 Mar 17 '16 at 12:39
  • As the SELECT that you want to base the cursor on is dynamic (the table_name changes) you can't declare it entirely 'up-front'. The way to do this in Oracle is to use a REF cursor. A link to dynamic SQL in the Oracle docs - docs.oracle.com/cd/E11882_01/appdev.112/e25519/… . – BriteSponge Mar 17 '16 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.