How to display the results of a ref cursor using SQL Developer

Question

I have a stored procedure with several cursors. They are defined as IN OUT parameters. I would like to display the result of the cursors using SQL Developer. This is an example of the stored procedure:

SET serveroutput on;
DECLARE
         p_input_stream       VARCHAR2(200);
        p_msg_code            NUMBER;
         p_msg_parms                         VARCHAR2(200);
         p_return_code                       NUMBER;
         p_trailer_cur                    sl_globals.curtype_weak;

 BEGIN
    /* Assign values to IN parameters */
    p_input_stream := '24954286Mnull|5155035|2|436|SCAN|47720|XTRA|0105||5155035||||N|~|\r';
    p_trailer_cur := null;

EXEC TRAILER_INFO(p_input_stream, 
        p_msg_code, p_msg_parms, p_return_code, 
        p_trailer_cur)

 /* Display OUT parameters */
    dbms_output.put_line('p_msg_code: ' || p_msg_code);
    dbms_output.put_line('p_msg_parms: ' || p_msg_parms);
    dbms_output.put_line('p_return_code: ' || p_return_code);

I have tried creating a refcursor variable and using it in place of p_trailer_cur like this

VARIABLE trailer_cur refcursor;
 EXEC TRAILER_INFO(p_input_stream, 
        p_msg_code, p_msg_parms, p_return_code, 
        :trailer_cur)
 print trailer_cur;

I get the error:

SP2-0552: Bind Varialbe "trailer_cur is not declared.

The variable is declared so I don't understand the error.

What is EXEC in your PL/SQL block? There is no such command in PL/SQL. — William Robertson
– William Robertson, Commented Feb 2, 2019 at 10:50
EXEC is a SQLPlus command - it's not part of the plsql language — thatjeffsmith
– thatjeffsmith, Commented Feb 6, 2020 at 15:46

thatjeffsmith · Accepted Answer · 2019-02-01 20:57:48Z

7

Two ways SQL Developer supports this - the GUI and the Code.

The GUI

If you execute your stored procedure from the Code Editor, find the stored procedure in the tree, click on it, use the Execute button - we'll grab ALL of your output, and show it below in the output panels:

And your attempt, the Code:

If you're in the SQL Worksheet and you have your anonymous block, you can run it with F5, including your print command.

Like so -

create or replace function get_emps(dno in number) return sys_refcursor
    is
      return_value sys_refcursor;
    begin
      open return_value for
        select * from employees where department_id = dno;
      return return_value;
    end;
    /



 var rc refcursor
 exec :rc := get_emps(90)

 print rc

answered Feb 1, 2019 at 20:57

thatjeffsmith

22.7k6 gold badges44 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gloria Santin Over a year ago

Since I have several parameters to output, I would like to do it from the Worksheet. However, when I run it from the Worksheet, I get the error: Bind Varialbe "trailer_cur is not declared. The cursor is an IN OUT parameter.

thatjeffsmith Over a year ago

@GloriaSantin your code block isn't complete? it shows some of what you're running, but not everything? Plus I don't have access to TRAILER_INFO() - can you try the code I shared? if you don't have an HR.EMPLOYEES table, then change the cursor query to whatever you have laying around

Gloria Santin Over a year ago

If it is an IN OUT parameter that is the cursor, what is the syntax to output the cursor?

thatjeffsmith Over a year ago

...to print it? should be the same, but see stackoverflow.com/a/22505596/1156452

thatjeffsmith Over a year ago

fwiw, SQLDev handles it either way for you, since you tagged sqldev, i showed you the GUI solution, it's literally 'push button' ready

Collectives™ on Stack Overflow

How to display the results of a ref cursor using SQL Developer

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related