Home » SQL & PL/SQL » Client Tools » REF CURSOR data not shown in TOAD
REF CURSOR data not shown in TOAD [message #141063] Thu, 06 October 2005 15:15 Go to next message
rogger
Messages: 15
Registered: October 2005
Junior Member
Hi,

I am using this procedure
--------------------------------------------------------
CREATE OR REPLACE PROCEDURE CUR_EXP_1 (p_return_cur OUT SYS_REFCURSOR)
IS
CURSOR casedetail
IS
SELECT case_no, REFERENCE, amount_3
FROM casm
WHERE status = 0 AND corr_acc_no = 100009074;

caserec casedetail%ROWTYPE;
v_price sec_market_value.price%TYPE;
v_sfactor sec_market_value.scale_factor%TYPE;
BEGIN

OPEN p_return_cur FOR
SELECT * FROM case_rec;
CLOSE casedetail;
DBMS_OUTPUT.put_line('Executed');
END;
/

---------------------------------------------------------

Procedure is executing correctly but in TOAD I am not able to to see data in ref cursor tab. I executed the same procedure in SQL NAVIGATOR and I was able to see the data under REF CURSOR tab.

Does any one faced the same problem. Any help will be appreciated.
Re: REF CURSOR data not shown in TOAD [message #141075 is a reply to message #141063] Thu, 06 October 2005 18:37 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You've made this proc far more complex than it needs to be - it should be just:

CREATE OR REPLACE PROCEDURE CUR_EXP_1 (p_return_cur OUT SYS_REFCURSOR)
IS
begin
  open p_return_cur for
    SELECT case_no, REFERENCE, amount_3
      FROM casm
     WHERE status = 0 AND corr_acc_no = 100009074;
END;
/


I'm assuming you are trying this with TOAD 8.5.3.2? The ref cursor output in TOAD is cumbersome to say the least, but it works - I use it when I have to...
Re: REF CURSOR data not shown in TOAD [message #141227 is a reply to message #141075] Fri, 07 October 2005 10:36 Go to previous messageGo to next message
rogger
Messages: 15
Registered: October 2005
Junior Member
Thanks for helping with the PL/SQl. I am using the version 8.0.0.47. Can you tell me what configuration I have to make in order to see the resultset in ref cursor tab. It would be very helpful. I didn't have any problem with SQL Navigator.
Re: REF CURSOR data not shown in TOAD [message #141230 is a reply to message #141227] Fri, 07 October 2005 11:27 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Toad 8.0.0.47 only has limited support for REF CURSOR output and only with the Debugging option and only with strongly-typed cursors (which you do not have in your example).

The latest version of TOAD supports both strongly and weakly-typed cursors and does not require the Debugging option. You do have to edit the generated code everytime you run it though because it chokes on the sys_refcursor type (it assumes it is a user-defined type instead of a built-in).
Re: REF CURSOR data not shown in TOAD [message #141240 is a reply to message #141230] Fri, 07 October 2005 12:20 Go to previous message
rogger
Messages: 15
Registered: October 2005
Junior Member
Todd,

Thanks for replying. I guess I might have to upgrade to new version.
Previous Topic: PL/SQL Developer Debugging Problem
Next Topic: TOAD GUI question - what is the red block for
Goto Forum:
  


Current Time: Wed Aug 05 19:20:22 CDT 2020