How to display a sys_refcursor data in TOAD's DataGrid

Question

Please i need help. (I SEARCHED A lot and get more confused . )

I use Toad 9.7.25 and i made this procedure (in a package)

PROCEDURE ReportaCC(pfcorte IN DATE, lcursor  IN OUT  SYS_REFCURSOR)
IS
BEGIN
    OPEN lcursor FOR
        select c1, c3, c3 from table1 where hdate = pfcorte;
    close lcursor;
END;

---

In toad's sql editor i´d like execute that procedure and show the cursor results in toad's datagrid:

---

--- I WANT THIS CODE CAN EXECUTE IN TOAD'S SQL EDITOR.

    DECLARE 
      PFCORTE DATE;
      LCURSOR SYS_REFCURSOR;
    BEGIN 

        PFCORTE := '31/08/2012';
        -- LCURSOR := NULL;  -- Modify the code to initialize this parameter

        mypaq.REPORTACC( TO_DATE(PFCORTE,'DD/MM/YYYY') , LCURSOR );

        :to_grid := LCURSOR;

        COMMIT;

    END;

---

When i execute the script (F9), and set the variable :to_grid type cursor, i get the next error:

"ORA-24338: statement handle not executed"

What can be the problem

Thanks in advance.

---

Thanks four your posts... worked fine!

But now have another question... If i replace the simple query (select c1, c2, c3 from table...) for a mor complex like this:

  PROCEDURE ReportaCC(pfcorte IN DATE,  lcursor OUT SYS_REFCURSOR)
    IS           
    BEGIN

        OPEN lcursor FOR

            SELECT ENC.CVEOTORGANTE, ENC.NOMBREOTORGANTE, ENC.IDENDINTIFICADORDEMEDIO, TO_CHAR(SYSDATE, 'YYYYMMDD') AS FECHAEXT, ENC.NOTAOTORGANTE, 
                   CIRCRED.valida_cc.QUITASIGNOS(VCL.APELLIDOPATERNO) AS VAL_APELLIDOPATERNO, 
                   CIRCRED.valida_cc.QUITASIGNOS(VCL.APELLIDOMATERNO) AS VAL_APMATERNO, 
                   CIRCRED.valida_cc.QUITASIGNOS(VCL.APELLIDOADICIONAL) AS APELLIDOADICIONAL , 
                   CIRCRED.valida_cc.QUITASIGNOS(VCL.NOMBRES) AS NOMBRES, 
                   VCL.FECHANACIMIENTO, 
                   circred.valida_cc.valida_rfc(Vcl.rfc,'CORRIGE') AS VALRFC,  
                   circred.valida_cc.valida_curp(VCL.CURP,'CORRIGE') AS VALCURP, VCL.NACIONALIDAD,
                   circred.valida_cc.valida_RESIDENCIA('ESIACOM', SC.TIPOVIV ) AS VAL_RESIDENCIA, VCL.NUMEROLICENCIACONDUCIR, 
                   circred.valida_cc.valida_EDOCIVIL('ESIACOM', VCL.ESTADOCIVIL) AS VAL_ESTADOCIVIL, VCL.SEXO, 
                   circred.valida_cc.valida_IFE(VCL.CLAVEELECTORIFE,'CORRIGE') AS CLAVEELECTORIFE, 
                   VCL.NUMERODEPENDIENTES,
                   VCL.FECHADEFUNCION, VCL.INDICADORDEFUNCION, VCL.TIPOPERSONA,
                   CIRCRED.valida_cc.QUITASIGNOS(VCL.DIRECCION) AS DIRECCION, 
                   CIRCRED.valida_cc.QUITASIGNOS(VCL.COLONIAPOBLACION) AS COLONIAPOBLACION, 
                   CIRCRED.valida_cc.QUITASIGNOS(VCL.DELEGACIONMUNICIPIO) AS DELEGACIONMUNICIPIO, 
                   CIRCRED.valida_cc.QUITASIGNOS(VCL.CIUDAD) AS CIUDAD, 
                   VCL.ESTADO, circred.valida_cc.valida_cp(VCL.CP, VCL.CDGEF) AS VAL_CP, VCL.FECHARESIDENCIA,
                   circred.valida_cc.valida_TEL(VCL.NUMEROTELEFONO,'CORRIGE') AS VAL_TEL, circred.valida_cc.valida_TIPODOMICILIO('ESIACOM', 'C') AS VAL_TIPODOMICILIO, VCL.TIPOASENTAMIENTO,    
                   EMP.*,
                   ENC.CVEOTORGANTE CVEACTUAL, ENC.NOMBREOTORGANTE, SAL.CUENTAACTUAL, SAL.TIPORESPONSABILIDAD, SAL.TIPOCUENTA, SAL.TIPOCONTRA, SAL.CLAVEUNIDADMONETARIA, SAL.VALORACTIVOVALUACION,
                   SAL.NUMPAGOS, SAL.FREQPAGOS,SAL.PAGOPACCL, SAL.FECHAAPERTURACUENTA, 
                   TO_CHAR(circred.valida_cc.FUN_FULTDEPCL(sal.CLNS, sal.CDGNS, sal.CDGNS, sal.CDGCL, sal.CICLO, SAL.INICICLO, SAL.FREQPAGOS, pfcorte ), 'YYYYMMDD') AS FULTPAGO,  
                   SAL.FECHAULTIMACOMPRA,  SAL.FECHACIERRECUENTA, SAL.FECHACORTE, SAL.GARANTIA, SAL.CREDITOMAXIMO,
                   SAL.SALDOCL, SAL.limitecredito, SAL.SDOVENCL, SAL.NUMPAGVEN, SAL.pagoactual,  SAL.HISTORICOPAG, SAL.CLAVEPREVENCION, SAL.TOTPAGREP, SAL.CLAVEANTERIOROTORGANTE, 
                   SAL.NOMBREANTERIOROTORGANTE, SAL.NUMEROCUENTAANTERIOR,
                   SAL.SUMSALDO, SAL.sumsdoven, SAL.numcred, SAL.numdirecc, SAL.numempleo, SAL.numctas, ENC.NOMBREOTORGANTE, NULL AS DOMDEVOL       
            FROM
                CIRCRED.VW_ENCABEZADO ENC,
                circred.VW_DATOSPERDOM  VCL,
                ICARO.VW_PROYINVE  SC,
                CIRCRED.EMPLEO  EMP,
                CIRCRED.VW_SALDOINCOB    SAL
            WHERE SAL.FUENTEBD = 'ESIACOM' 
                AND SAL.CDGCL = VCL.CDGCL  
                AND SAL.CDGCL = SC.CDGCL(+) AND SAL.CICLO = SC.CICLO(+) and SAL.INICICLO = SC.INICIO(+)
                AND SAL.FCORTE = pfcorte
                AND SAL.STATUSCC IN ('INCOB', 'CIERR', 'CEROS')  ;                       

    END ReportaCC;

---

Why cant display the results?
(The query works fine if i execute it directly in a TOAD SQL editor)

Thanks again....!!!

Spinescent

You should not be closing your cursor at the end of your procedure. – Openhearted

I suggest you read the mission statement and the FAQ about Stack Overflow. – Openhearted

Where this query is not working? – Warr

Polppan: When i call the procedure.... does not show rows.... DECLARE PFCORTE DATE; BEGIN PFCORTE := '31/08/2012'; ICARO.ICARO_PRUEBA.REPORTACC_SALDOSINCOB ( TO_DATE(PFCORTE,'DD/MM/YYYY') , :CCCURSOR ); END; / – Spinescent

@arzammg_at_gmail.com How do you know your SQL is correct? If no error is being thrown then it sounds as though your select statement is simply not returning any rows. Perhaps nothing matches the criteria, maybe the date/time parameter does not match (try AND TRUNC(SAL.FCORTE) = TRUNC(pfcorte) to remove the time element from the date comparison) or maybe one of your joins is bad. Run the statement outside of your procedure to verify that it should in fact return rows. – Openhearted

Hi wweicker, i did it: i ran the statement outside of the procedure and it returns rows.. only when i call as i explained does not show rows.... thanks – Spinescent

@arzammg_at_gmail.com If it is a sql select statement why not use a function? Have you executed sql statement outside your procedure by passing PFCORTE as to_char? because in your procedure it is to_char. – Warr

Answer 1

After you hit F9 the "Variables" dialog appears and you select Type=Cursor from the dropdown list then press OK:

The reason you are getting the "ORA-24338: statement handle not executed" error is because you are closing your cursor before it is accessed.

This is the process that is happening:

1. Execute procedure
2. OPEN statement returns a pointer to the result set in memory (but does not return any data)
3. CLOSE statement discards the results before they are accessed
4. Procedure call ends
5. The client caller (in this case TOAD) attempts to access the result stream, but the pointer is invalid, so nothing can be read and the error is thrown

Solution: Remove the close lcursor; statement.

Openhearted

@arzammg_at_gmail.com why did you un-accept the answer? You noted that this did answer your original question... if you have another question why don't you post it separately (and with more details about what the error message is)? You could revert your question back to it's original state, accept this answer, and that way anyone else in the future who is searching for this same question will find the answer here. – Openhearted

Answer 2

As your procedure is doing only a select statement better use a function like

CREATE or REPLACE function ReportaCC(pfcorte IN DATE) 
RETURN SYS_REFCURSOR
AS
   lcursor   SYS_REFCURSOR;
BEGIN
   OPEN lcursor FOR
     select c1, c3, c3 from table1 where hdate = pfcorte;
   RETURN lcursor ;
END;
/

Do not close lcursor here, close from your calling statement because if you close lcursor then you wouldn't be able to see any results.

And execute as

select ReportaCC(<>) from dual

from toad, double click cursor in datagrid to see the results.

Warr

Thanks Polppan ... the select from dual works fine .... but How can i save the data shown as excel? .... thanks again – Spinescent