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

Answer 1

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>

Answer 2

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