Toad World® Forums

Display refcursor in Toad 10.1.1.8


#1

INAPPLICATION VARCHAR2(32767);

PCURSOR_SETTINGS PHXADM.APPLICATION_SETTING.OUTPUTCURSOR;

INAPPLICATION := ‘CQH’;

PHXADM.APPLICATION_SETTING.SETTINGSALL_GET ( INAPPLICATION, :PCURSOR_SETTINGS );

END;

It can’t be this hard. I am able to display the results of a refcursor when calling a stored proc in version 9.7.2.5, but not in 10.1.1.8. How can I get this to work in 10 like it works in 9??? Here’s what works in 9:

DECLARE BEGIN


#2

Browser, load up your procedure.

Right – Click > Execute

Toad auto brings back the refcursor in a grid.


#3

In the “Set Parameters” dialog, you have to click the “output
options” toolbar button (the last one) to enable the loading of ref cursor
results to grid.


#4

That’s fine if I have a static procedure taking in no parameters, or I only care about testing one set of values, but if I want to test muliple inputs with different permutations, I then need to go through the same process numerous times. I’d like to have the code in an editor window so that I can simply change the input values. Can this be done?


#5

Try something like this and when prompted for the variable, select CURSOR as the
type. Also execute with and not .

DECLARE

INAPPLICATION VARCHAR2 ( 32767 );

PCURSOR_SETTINGS PHXADM . APPLICATION_SETTING . OUTPUTCURSOR ;

BEGIN

INAPPLICATION := ‘CQH’ ;

PHXADM . APPLICATION_SETTING . SETTINGSALL_GET ( INAPPLICATION ,
:PCURSOR_SETTINGS );

:rc0_RetVal := PCURSOR_SETTINGS ;

END;
image001.png


#6

Try something like this and when prompted for the variable, select CURSOR as the
type. Also execute with and not .

DECLARE

INAPPLICATION VARCHAR2 ( 32767 );

PCURSOR_SETTINGS PHXADM . APPLICATION_SETTING . OUTPUTCURSOR ;

BEGIN

INAPPLICATION := ‘CQH’ ;

PHXADM . APPLICATION_SETTING . SETTINGSALL_GET ( INAPPLICATION ,
:PCURSOR_SETTINGS );

:rc0_RetVal := PCURSOR_SETTINGS ;

END;
image002.png


#7

Yes, I’ve tried that but the type options do not include “cursor”.


#8

I did not get an answer for my question earlier, so I will try this topic that
looks very close.

How can I view the value of the “out” parameter in TOAD?

begin
pk_idw_recon.pr_recon_results(p_run_id => :p_run_id,
p_summary_report_sql => :p_summary_report_sql);
end;

The second parameter is “out” type clob, I would like to run the
procedure and see the result. I do not want to declare a variable and use
dbms_output which does work. Please advise. Other oracle tools (like PL/SQL
Developer let you do it).

Many thanks,

George
image001.jpeg


#9

I did not get an answer for my question earlier, so I will try this topic that
looks very close.

How can I view the value of the “out” parameter in TOAD?

begin
pk_idw_recon.pr_recon_results(p_run_id => :p_run_id,
p_summary_report_sql => :p_summary_report_sql);
end;

The second parameter is “out” type clob, I would like to run the
procedure and see the result. I do not want to declare a variable and use
dbms_output which does work. Please advise. Other oracle tools (like PL/SQL
Developer let you do it).

Many thanks,

George
image001.png


#10

I did not get an answer for my question earlier, so I will try this topic that
looks very close.

How can I view the value of the “out” parameter in TOAD?

begin
pk_idw_recon.pr_recon_results(p_run_id => :p_run_id,
p_summary_report_sql => :p_summary_report_sql);
end;

The second parameter is “out” type clob, I would like to run the
procedure and see the result. I do not want to declare a variable and use
dbms_output which does work. Please advise. Other oracle tools (like PL/SQL
Developer let you do it).

Many thanks,

George
image002.png


#11

it’s always kind of bugged me that output parameters never work in spool
sql. I’ll see if I can fix that.

I don’t know of any way in Toad to do this already other than dbms_output
or debugging
image001.png


#12

it’s always kind of bugged me that output parameters never work in spool
sql. I’ll see if I can fix that.

I don’t know of any way in Toad to do this already other than dbms_output
or debugging
image002.png


#13

Yeah, if you’re talking about the ‘test’ feature in PL/SQL
Developer, we don’t do that

We can capture a collection or a REFCURSOR that’s returned, but nothing
automatic for your example.
image002.png


#14

Yeah, if you’re talking about the ‘test’ feature in PL/SQL
Developer, we don’t do that

We can capture a collection or a REFCURSOR that’s returned, but nothing
automatic for your example.
image001.png


#15

we can see the cursor result in Toad by using like this

exec procedure_name(param1,param2,:cv1);

run above command in toad editor

It will give you a popup of variables.

In that select type cursor for :cv1