Toad World® Forums

Execute Procedure (that returns multiple sys_refcursor results) throws 'Dataset not in edit or insert mode' exception

From the Toad Schema Browser, I get a ‘Dataset not in edit or insert mode’ when I right-click a stored procedure (that returns multiple sys_refcursor results), select ‘Execute Procedure’, select ‘Fetch cursor data’, and click OK.

I never received this exception in previous versions of Toad. It first appeared only after I upgraded to my Toad for Oracle Xpert 64-Bit to 12.8.0.49.

If I click OK on the exception message (one gets generated for each sys_refcursor returned), the procedure executes and I can view the cursor results successfully. But clicking the exception message for each returned result is very inefficient.

Is there a setting that I can configure to suppress this exception?

Regards.

87441.Capture.png

I replied a while ago, but the NSA must have intercepted it. I had an edit to make anyway.

The error occurs when an opened cursor contains no data. There is only one viable workaround. It is fixed for Toad 12.9. On the Output Options tab of the Set Parameters window disable the option to fetch cursor data. You could also close your cursor in your PL/SQL if it contains no data, but this is not a good option unless your PL/SQL is just sandbox code.

Michael

It occurs when an opened cursor contains no data and there is only one viable workaround. It is fixed for Toad 12.9. On the Output Options tab disable the option to show cursor data. You could also close your cursor in your PL/SQL if it contains no data, but this is not a good option unless your PL/SQL is just sandbox code.

Michael

On 01/15/2016 02:39 PM, Allen.Zolondek wrote:

Execute Procedure (that returns multiple sys_refcursor results) throws ‘Dataset not in edit or insert mode’ exception

Thread created by Allen.Zolondek
From the Toad Schema Browser, I get a ‘Dataset not in edit or insert mode’ when I right-click a stored procedure (that returns multiple sys_refcursor results), select ‘Execute Procedure’, select ‘Fetch cursor data’, and click OK.

I never received this exception in previous versions of Toad. It first appeared only after I upgraded to my Toad for Oracle Xpert 64-Bit to 12.8.0.49.

If I click OK on the exception message (one gets generated for each sys_refcursor returned), the procedure executes and I can view the cursor results successfully. But clicking the exception message for each returned result is very inefficient.

Is there a setting that I can configure to suppress this exception?

Regards.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

It looks like only version 12.8.0.49 is available for me to download.

I need to view the cursor data. Since this worked fine (even with empty cursors) with the previous version. I plan to move back to the previous version of Toad for Oracle.

Thanks.

I believe that you can just ignore the error in 12.8. I’ve created the following procedure and when executed I get the “Dataset not in edit or insert mode” error. In the PL/SQL Results grid I also get the output values for P1 and P3 so it doesn’t look like P2 being empty and tossing the error message is anything more than an nuisance. If you downgrade it will be fixed for 12.9 if you want to get back up to speed in the late spring.

CREATE OR REPLACE PROCEDURE empty_cursor (p1 OUT NUMBER,
p2 OUT SYS_REFCURSOR,
p3 OUT VARCHAR2)
AS
BEGIN
p1 := 100;

OPEN p2 FOR
SELECT *
FROM user_tables
WHERE 0 = 1;

p3 := USER;
END;

OK. More of a nuisance for now. I look forward to the 12.9 release.

Thanks!