Oracle PL/SQL – Dynamic Query with Cursors

oracleplsql

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;

Best Answer

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;

Related Solutions

Why use explicit cursors instead of regular loops

A cursor can be explicit or implicit, and either type can be used in a FOR loop. There are really two aspects to your question.

Why use an explicit cursor FOR loop over an implicit cursor FOR loop?
- Use an explicit cursor FOR loop when the query will be reused, otherwise an implicit cursor is preferred.
Why use a loop with a FETCH rather than a FOR loop that doesn’t have an explicit FETCH?
- Use a FETCH inside a loop when you need to bulk collect or when you need dynamic SQL.

Here is some useful information from the documentation.

Example of Implicit Cursor FOR LOOP

BEGIN
   FOR vItems IN (
      SELECT last_name
      FROM employees
      WHERE manager_id > 120
      ORDER BY last_name
   ) 
   LOOP
      DBMS_OUTPUT.PUT_LINE ('Name = ' || vItems.last_name);
   END LOOP;
END;
/

Example of Explicit Cursor FOR LOOP

DECLARE
   CURSOR c1 IS
      SELECT last_name
      FROM employees
      WHERE manager_id > 120
      ORDER BY last_name;
BEGIN
   FOR vItems IN c1 LOOP
      DBMS_OUTPUT.PUT_LINE ('Name = ' || vItems.last_name);
   END LOOP;
END;
/

Implicit Cursor

An implicit cursor is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement. You cannot control an implicit cursor, but you can get information from its attributes.

An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another SELECT or DML statement runs.

The implicit cursor attributes are: SQL%ISOPEN, SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%BULK_ROWCOUNT, SQL%BULK_EXCEPTIONS

Explicit Cursor

An explicit cursor is a session cursor that you construct and manage. You must declare and define an explicit cursor, giving it a name and associating it with a query (typically, the query returns multiple rows). Then you can process the query result set in either of these ways:

Open the explicit cursor (with the OPEN statement), fetch rows from the result set (with the FETCH statement), and close the explicit cursor (with the CLOSE statement).

Use the explicit cursor in a cursor FOR LOOP statement (see "Query Result Set Processing With Cursor FOR LOOP Statements").

You cannot assign a value to an explicit cursor, use it in an expression, or use it as a formal subprogram parameter or host variable. You can do those things with a cursor variable (see "Cursor Variables").

Unlike an implicit cursor, you can reference an explicit cursor or cursor variable by its name. Therefore, an explicit cursor or cursor variable is called a named cursor.

Cursor FOR LOOP Statements

The cursor FOR LOOP statement lets you run a SELECT statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor.

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Best Answer

Related Solutions

Why use explicit cursors instead of regular loops

Sql-server – Oracle GoldenGate add trandata errors

Related Question