Toad World® Forums

Ability to view cursors from script output


#1

I have a sql script used to test a procedure which returns a ref cursor. The
parameters to the procedure are passed as substitution variables (with : in
front). In TOAD v9.7.2.5, when I run the script I get a dialog that shows each
parameter (including the cursor variable) and the option to choose the type for
the variables. In the list I have “Cursor” as an available entry. When I choose
that and run the script, the procedure runs and I get a grid output showing the
contents of the cursor. In TOAD v10.0.0.41 (have not applied patch due to
problems with tabbed browsing) there is no Cursor option for the type and any
attempt to run the script gives the error “wrong number or types of arguments
…”. In fact, there is quite a lot of types that are no longer available. Was
this something that was removed and no longer available? Running the procedure
from the schema browser and choosing to output the cursor results does work
fine. The problem is when running from a saved sql script that performs some
needed tasks prior to running the procedure.

Thanks,

David

This email transmission and any documents, files or previous email messages
attached to it may contain information that is confidential or legally
privileged. If you are not the intended recipient or a person responsible for
delivering this transmission to the intended recipient, you are hereby notified
that you must not read this transmission and that any disclosure, copying,
printing, distribution or use of this transmission is strictly prohibited. If
you have received this transmission in error, please immediately notify the
sender by telephone or return email and delete the original transmission and its
attachments without reading or saving in any manner.


#2

I applied the latest patch and tried this again with no luck. Using TOAD
v10.1.1.8, is there a way to run a SQL script and have the output of a cursor
displayed in a grid without having to use the Execute option in the Schema
Browser?

Here is a simple example of what I am trying to do that works in TOAD 9.7.2.5:

DECLARE

TYPE cur_Protocol IS REF CURSOR;

PROCEDURE Test_Proc (r_cur OUT cur_protocol) AS

BEGIN

OPEN r_cur FOR

SELECT dummy from dual;

END;

BEGIN

Test_Proc(:cur);

END;

When the dialog appears, I could just choose Cursor for the variable type and
all was well.

Thanks,

David


#3

David,

When running this as a script (F5), in 9.7 I get

ORA-01008: not all variables bound

and if I run it in SQL*Plus, I get:

SP2-0552: Bind variable “CUR” not declared

Are you trying to run this as a script (F5 ) or as a single statement (F9 )?

Greg
Outlook.jpeg


#4

David,

When running this as a script (F5), in 9.7 I get

ORA-01008: not all variables bound

and if I run it in SQL*Plus, I get:

SP2-0552: Bind variable “CUR” not declared

Are you trying to run this as a script (F5 ) or as a single statement (F9 )?

Greg
Outlook.jpeg


#5

It is run using F9. Do you have the prompt for substitution variables option
checked? I don’t believe this will work in SQL*Plus since it uses the : style
substitution variable since I’m asking to show the contents of the cursor.

Thanks,

David
Outlook.jpeg


#6

It is run using F9. Do you have the prompt for substitution variables option
checked? I don’t believe this will work in SQL*Plus since it uses the : style
substitution variable since I’m asking to show the contents of the cursor.

Thanks,

David
Outlook.jpeg


#7

I see this too and will fix.

Michael
Outlook.jpeg


#8

I see this too and will fix.

Michael
Outlook.jpeg


#9

Thanks Michael!