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
Browser, load up your procedure.
Right – Click > Execute
Toad auto brings back the refcursor in a grid.
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.
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?
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;
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;
Yes, I’ve tried that but the type options do not include “cursor”.
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
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
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
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
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
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.
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.
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