Toad World® Forums

Execution Console failed to show result set for a cursor variable


#1

My procedures have OUT variables of sys_refcursor type. I need to view result set that return those cursor variables. I have found that Execution Console worked properly when such cursors were opened in the called procedure, but it failed when the cursors were opened in deeper level. (Option “Direct values of OUT parameters to DBMS_OUTPUT” is not checked.)

Let’s look at the test case. Assume we have a package with two procedures (see below). One of them p_test calls p_test_inner to open cursor rs_info. When I executed p_test_inner in Execution Console I could see the value of the rs_info (it shows one field “dummy” from table DUAL). When I did the same test for p_test, I got exceptions:

Execution failed: ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor

I also tested previous version of SQL Navigator 5.5 and got the same error.

Your suggestion how to use Execution Console in the case will be appreciated. Or maybe it’s an issue?

CREATE OR REPLACE
PACKAGE pg
IS
PROCEDURE p_test_inner (
rs_info OUT sys_refcursor
);

PROCEDURE p_test (
rs_info OUT sys_refcursor
);
END;
/

CREATE OR REPLACE
PACKAGE BODY pg
IS
PROCEDURE p_test_inner (
rs_info OUT sys_refcursor
)
IS
BEGIN
OPEN rs_info FOR
SELECT *
FROM DUAL;
END;

PROCEDURE p_test (
rs_info OUT sys_refcursor
)
IS
BEGIN
p_test_inner (rs_info);
END;
END;
/

Thanks


#2

Hi anv,

Thanks for posting your issue to this Community. Unfortunately our product does not support yet ref_cursor on deeper level. BTW, in the next release with new Editor we do not have any more Execution Console.

We take this issue on board as an enhancement request to be implemented possibly after release 6.0.

Did you have a chance to look at teh current Beta?

Rgds,
Andrew


#3

Thanks Andrew, that explains a lot.

I tryed to execute the same procedure in the new Unified Editor and looked at bind variables in Defines area. For cursor variables it says just ‘Object’ in Value column, there is no possibility to look at cursor data.

I hope SQL Navigator Team will add this feature in the future as now I am constantly switching to PL/SQL Developer that has it, though SQL Navigator has been my primary tool for long time.


#4

Hi Alexander,

We will definitely try our best to get this work for you. We would love to keep you in Quest’s tool for your whole work day :-).

We will keep you posted. Please check the beta builds, provide feadback and work with us (as many of our users), that way we can deliver to you the features and work flows which works for you.

Thanks and regards,
Bruce


#5

I’ll second the request to have this back. We make extensive use of OUT parameters that are result sets. Likewise, I switch back and forth to PLSQL Developer.

DK


#6

It works correctly in the 6.4 version.

Thanks,