如何在TOAD的DataGrid中显示sys_refcursor数据 [英] How to display a sys_refcursor data in TOAD's DataGrid

问题描述

请我需要帮助. (我进行了很多搜索，变得更加困惑.)

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

我使用Toad 9.7.25，并执行了此过程(在包装中)

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;

---

在Toad的sql编辑器中，我想执行该过程并将光标结果显示在toad的datagrid中:

---

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;

---

当我执行脚本(F9)并设置变量:to_grid类型的游标时， 我收到下一个错误:

---

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

"ORA-24338:语句句柄未执行"

"ORA-24338: statement handle not executed"

可能是什么问题

谢谢.

感谢您的四个帖子……效果很好！

Thanks four your posts... worked fine!

但是现在还有另一个问题... 如果我替换一个简单的查询(从表中选择c1，c2，c3 ...)来查找这样的mor复合体:

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;

---

为什么不能显示结果?
(如果我直接在TOAD SQL编辑器中执行查询，则查询工作正常)

---

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

再次感谢.... !!!

Thanks again....!!!

推荐答案

点击F9后，将出现变量"对话框，然后从下拉列表中选择Type = Cursor，然后按OK:

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

出现"ORA-24338:语句句柄未执行"错误的原因是，您在访问游标之前就将其关闭.

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

这是正在发生的过程:

1. 执行过程
2. OPEN语句返回指向内存中结果集的指针(但不返回任何数据)
3. CLOSE语句在访问结果之前将其丢弃
4. 程序调用结束
5. 客户端调用方(在本例中为TOAD)尝试访问结果流，但是指针无效，因此无法读取任何内容并引发错误

解决方案:删除close lcursor;语句.

这篇关于如何在TOAD的DataGrid中显示sys_refcursor数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！