How do you display the value of a refcursor in SQL Developer?

Question

I've been trying to view a resultset from a stored procedure call in SQL Developer, but all I can see in "Script Output" tab are the result set's column names. The same code in Toad displays the records in "Grid" but I'm having trouble displaying the same output in SQL Developer.

Code I'm using as follows:

create or replace PROCEDURE test_proc (
                          r_recordset      OUT SYS_REFCURSOR)
    IS
      sql_stmt   CLOB := NULL;
    
    BEGIN
      sql_stmt := 
        'select * from test_table';
      OPEN r_recordset FOR sql_stmt;
    END;

then

set serveroutput on;
variable Out_Ref_Cursor refcursor;
EXECUTE test_proc(:Out_Ref_Cursor);
print :Out_Ref_Cursor;

I've turn on DBMS output and connected it to the database but there's absolutely nothing displayed in that window. Buffer size is 20000. Like this answer suggests: Printing the value of a variable in SQL Developer I'm wondering if I'm missing a setting or a view. FYI: total number of records in test_table are 48.

I have also tried

DECLARE
Out_Ref_Cursor refcursor
BEGIN
test_proc (:Out_Ref_Cursor);
dbms_output.put_line(Out_Ref_Cursor);
END;

Does this answer your question? [How to display the results of a ref cursor using SQL Developer](https://stackoverflow.com/questions/54486796/how-to-display-the-results-of-a-ref-cursor-using-sql-developer) — astentx, Mar 31 '23 at 21:48
no, I've tried everything in that answer but didn't work. that answer also relates to a function and not a store proc so I assumed that's why it didn't work for me — muttonchops, Mar 31 '23 at 22:46

score 0 · Answer 1 · answered Mar 31 '23 at 19:52

0

I have everything I need to test your issue except for your TEST_TABLE. Your example, re-written for HR.COUNTRIES

answered Mar 31 '23 at 19:52

thatjeffsmith

20,522
6
37
120

how strange... what's your SQL Developer version no.? mine is 21.4 – muttonchops Mar 31 '23 at 20:00
a year or 2 newer than that but this code hasn't changed in years. like i said, what's in your table? blobs, clobs, XMLTYPE, something more exotic than a varchar2 or integer? – thatjeffsmith Mar 31 '23 at 20:06
add this to your 3 lines of code that you're running 'set serveroutput on' – thatjeffsmith Mar 31 '23 at 20:15
yeah I tried that already. didn't work – muttonchops Mar 31 '23 at 20:18
1

something is up with your ENV then - if you want more help, come over to our forums https://forums.oracle.com/ords/apexds/domain/dev-community/category/sql_developer?tags=sql-developer – thatjeffsmith Mar 31 '23 at 20:22

score 0 · Answer 2 · answered Apr 01 '23 at 02:06

0

Solved! uninstalling and installing a new version of SQL Developer with a new JDK did the trick

answered Apr 01 '23 at 02:06

muttonchops

104
8

How do you display the value of a refcursor in SQL Developer?

2 Answers2