How to create dynamic cursor in PLSQL - Oracle

Question

I cannot create this cursor dynamically, I only have to modify the name of the table in the statement. But it returns me error.

What am I doing wrong or what am I missing to create the dynamic cursor?

The dynamic statement is in the Lv_SQL variable and I call the cursor C_DATOS but it does not recognize it.

PROCEDURE PROC_CAB_DET(Pv_corte VARCHAR2, Pv_MsjError IN OUT VARCHAR2) IS
    Lv_Table VARCHAR2(100);
    Lv_SQL   VARCHAR2(5000);
    C_DATOS  SYS_REFCURSOR;
  BEGIN
    Lv_Table := NULL;
    IF (Pv_corte IN ('02', '03')) THEN
        Lv_Table := 'TABLE_TMP_MOV';
    ELSIF (Pv_corte IN ('14', '15')) THEN
        Lv_Table := 'TABLE_TMP_FIX';
    ELSE
        Lv_Table := 'TABLE_TMP_CMF';
    END IF;

    Lv_SQL := 'SELECT cuenta, campo_2 RUBRO 
                 FROM ' || Lv_Table || ' 
                WHERE codigo = 1 
                  AND CAMPO_3 != "000" 
                  AND (campo_2 NOT IN (SELECT RUBRO FROM GSI_QC_RUBROS_CABECERA) 
                  AND upper(campo_2) NOT LIKE "NAN%") 
                MINUS 
               SELECT cuenta, campo_2 RUBRO 
                 FROM ' || Lv_Table || ' 
                WHERE codigo=4 
                  AND campo_2 != "ICE (12%)"';

    OPEN C_DATOS FOR Lv_SQL;

    FOR I IN C_DATOS LOOP      
      INSERT INTO GSI_QC_CBS_CASOS_ERROR(CUENTA, ID_ESCENARIO, DATO_TMP_1) 
      VALUES(I.CUENTA, 'IdEscenario', 'DATA');      
    END LOOP;
    COMMIT;

    CLOSE C_DATOS;
                 
    EXCEPTION
      WHEN OTHERS THEN
        Pv_MsjError := SQLERRM;
  END PROC_CAB_DET;

PLS-00221: C_DATOS is not a procedure or is undefined

for loop is only possible for strongly typed cursors. Cursor for loop: cursor: Name of an explicit cursor (not a cursor variable) that is not open when the cursor FOR LOOP is entered. — astentx
– astentx, Commented Jan 20, 2023 at 18:43

Littlefoot · Accepted Answer · 2023-01-20 18:44:15Z

You can't enclose strings into double quotes; have to be single ones. To make it simpler, use the q-quoting mechanism. Also, you wrongly looped through refcursor.

I created dummy tables to make that procedure compile; I don't know whether code does what you planned.

SQL> create or replace
  2  PROCEDURE PROC_CAB_DET(Pv_corte VARCHAR2, Pv_MsjError IN OUT VARCHAR2) IS
  3      Lv_Table VARCHAR2(100);
  4      Lv_SQL   VARCHAR2(5000);
  5      C_DATOS  SYS_REFCURSOR;
  6      --
  7      l_cuenta table_tmp_mov.cuenta%type;
  8      l_rubro  table_tmp_mov.campo_2%type;
  9  BEGIN
 10      Lv_Table := NULL;
 11      IF (Pv_corte IN ('02', '03')) THEN
 12          Lv_Table := 'TABLE_TMP_MOV';
 13      ELSIF (Pv_corte IN ('14', '15')) THEN
 14          Lv_Table := 'TABLE_TMP_FIX';
 15      ELSE
 16          Lv_Table := 'TABLE_TMP_CMF';
 17      END IF;
 18
 19      Lv_SQL := 'SELECT cuenta, campo_2 RUBRO
 20                   FROM ' || Lv_Table || q'[
 21                  WHERE codigo = 1
 22                    AND CAMPO_3 != '000'
 23                    AND (campo_2 NOT IN (SELECT RUBRO FROM GSI_QC_RUBROS_CABECERA)
 24                    AND upper(campo_2) NOT LIKE 'NAN%')
 25                  MINUS
 26                 SELECT cuenta, campo_2 RUBRO
 27                   FROM ]' || Lv_Table || q'[
 28                  WHERE codigo=4
 29                    AND campo_2 != 'ICE (12%)']';
 30
 31      OPEN C_DATOS FOR Lv_SQL;
 32
 33      loop
 34        fetch c_datos into l_cuenta, l_rubro;
 35        exit when c_datos%notfound;
 36
 37        INSERT INTO GSI_QC_CBS_CASOS_ERROR(CUENTA, ID_ESCENARIO, DATO_TMP_1)
 38        VALUES(l_CUENTA, 'IdEscenario', 'DATA');
 39      END LOOP;
 40      COMMIT;
 41
 42      CLOSE C_DATOS;
 43
 44  EXCEPTION
 45        WHEN OTHERS THEN
 46          Pv_MsjError := SQLERRM;
 47  END PROC_CAB_DET;
 48  /

Procedure created.

Let's run it:

SQL> set serveroutput on;
SQL> declare
  2    l_err varchar2(200);
  3  begin
  4    proc_cab_det('02', l_err);
  5    dbms_output.put_line('error = ' || l_err);
  6  end;
  7  /
error =

PL/SQL procedure successfully completed.

SQL>

Paul W · Accepted Answer · 2023-01-20 19:15:39Z

1

Replace " inside your lv_SQL string with doubled single quotes, ''

answered Jan 20, 2023 at 19:15

Paul W

12.8k2 gold badges9 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to create dynamic cursor in PLSQL - Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related