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....!!!

You should not be closing your cursor at the end of your procedure. — wweicker, Oct 05 '12 at 16:33
I suggest you read the [mission statement](http://stackoverflow.com/about) and the [FAQ](http://stackoverflow.com/faq) about Stack Overflow. — wweicker, Oct 05 '12 at 17:57
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; / — Edgar Fco Delgado Zamarripa, Oct 05 '12 at 18:49
@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. — wweicker, Oct 05 '12 at 19:52
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 — Edgar Fco Delgado Zamarripa, Oct 05 '12 at 20:54
@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. — Jacob, Oct 06 '12 at 03:34

wweicker · Accepted Answer · 2012-10-05T16:52:33.400

25

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

Select Type=Cursor

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:

Execute procedure
OPEN statement returns a pointer to the result set in memory (but does not return any data)
CLOSE statement discards the results before they are accessed
Procedure call ends
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.

edited Oct 05 '12 at 16:52

answered Oct 05 '12 at 16:29

wweicker

4,833
5
35
60

3

@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. – wweicker Oct 05 '12 at 17:52

Jacob · Answer 2 · 2012-10-05T16:06:51.603

1

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.

edited Oct 05 '12 at 16:06

answered Oct 05 '12 at 15:37

Jacob

14,463
65
207
320

Thanks Polppan ... the select from dual works fine .... but How can i save the data shown as excel? .... thanks again – Edgar Fco Delgado Zamarripa Oct 05 '12 at 20:18

How to display a sys_refcursor data in TOAD's DataGrid

2 Answers2

Linked