Updated Feb 5, 2021

There are two ways you can view your REFCURSOR output from calling PL/SQL stored procedures and functions in Oracle SQL Developer.

Using the PRINT command in a SQL Worksheet

Using the classic SQL*PLUS PRINT command to view the refcursor output will work in SQL Developer just like it would work in your command line tools. You execute your program, you create a local variable or 3 to ‘catch’ said output, and then you PRINT it.

It’s good, but if you’re using a tool, you’re not really taking advantage of the tool. I do think it’s good to know how it works, because sometimes all you have is the command line.

Let’s look at a VERY simple example:

-- I don't want to see ANY code like this in production,
-- but it's about as simple as we can make it for an example
 CREATE OR REPLACE FUNCTION refcursor_function
  RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR SELECT * FROM hr.countries;
  RETURN c;
END;
/
 
-- And then we need to call said FUNCTION, and we'll do it with a SELECT INTO.
The old ways are still sometimes the ok/good ways.

Using the Procedure Editor / Execute Button

When you open the plsql program from your connection tree, OR open it from a file and associate it with a database connection… and execute your function, SQL Developer automatically adds some code to the ANON block that allows us to grab the refcursor coming back from the database and assign it to a grid in SQL Developer.

You can see this code here

DECLARE
  v_Return SYS_REFCURSOR;
BEGIN
 
  v_Return := REFCURSOR_FUNCTION();
  /* Legacy output: 
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
*/ 
  :v_Return := v_Return; --<-- Cursor
END;

You might initially think that this isn’t working in SQL Developer. Be patient! You need to click on the ‘Output Variables’ panel.

You’ll see this panel after you execute a stored procedure using our PLSQL editor

Your output window will now have an ‘Output Variables’ pane that has a grid with the first 100 records in your cursor.

viewing a refcursor returned from a plsql function in Oracle SQL Developer
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

125 Comments

  1. Hello Jeff,

    thanks for this info, I just tried it using a function returning a refcursor and I indeed got the output.
    However, is there a reason for this grid not to have the same features as the ‘normal’ grid like sorting, exporting, .. ?

    • yes, because it’s not a query result – go run that function as a select function() from dual, then you’ll get those features again.

  2. I am running a stored procedure and one of the columns is a CLOB. The output shows as something like this: oracle.sql.CLOB@1f9a0a8

    I don’t see a way to view the text through the Log window. Am I missing something or is this a limitation?
    I am on Version 3.2.20.10

  3. Hi Jeff,

    Do you know if theres a limit of number of characters per each column on output variables log? I am displaying a column with a clob type. And when i copy these values it is cutting off at 4000 characters only.

    Best regards,
    Kate

  4. create or replace procedure p1(id in number, o out sys_refcursor)
    as
    begin
    open o for
    select first_name
    from emp
    where department_id=90;
    end;
    /

    how can i get result using plsql

  5. Sumeet Lalvani Reply

    Hi Jeff,
    I want to print all columns in place of one.
    below is my code.

    CREATE OR REPLACE PROCEDURE CHECK_RANGE_BEETWEEN(p_table_name VARCHAR2,
    p_col_names VARCHAR2,p_PK_FILED VARCHAR2,p_MIN_RANGE varchar2 , p_MAX_RANGE VARCHAR2)
    AS
    p_output SYS_REFCURSOR;
    l_temp varchar2(500); –> need some change in this code , but I am unable to find correct way.
    l_temp_2 clob;
    BEGIN
    OPEN p_output FOR ‘SELECT ‘
    ||p_col_names –> I want to pass here * or required columns
    ||’ FROM ‘
    || p_table_name
    || ‘ where ‘ || p_col_names || ‘ NOT between ‘ || p_MIN_RANGE || ‘ and ‘ || p_MAX_RANGE;
    –|| ‘ order by ‘ || p_col_names ;
    loop
    fetch p_output into l_temp;
    exit when p_output%NOTFOUND;
    dbms_output.put_line(‘Invalid values in table ‘||p_table_name || ‘ is ‘ || l_temp);
    end loop;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
    exec CHECK_RANGE_BEETWEEN(‘Exception_Records_Test’,’age’,’EMP_ID’,’20’,’60’);

  6. I accidentaly closed ‘output variables’ panel in sql developer
    (not dbms_output ) how to get it back?
    it is not under view menu

  7. Is it at all possible to increase the size of a column?
    Like I get output, but I can’t see all of what the variable shows and there doesn’t seem to be a way to increase the size.

    Version is 4.1.3.20

  8. My output is stuffed into a single column when I run it from a select() from dual. Even though the output variable shows output formatted just fine in grid format, just like your example.

    I’m running the function like this:

    SELECT function_with_sql(sysdate) from dual;

    The funciton has a cursor defined like this:

    RETURN sys_refcursor
    AS o_cursor SYS_REFCURSOR;
    Begin
    open o_cursor for

    select……………
    Return o_cursor;
    End;
    /

    How do I get the output formatted in normal grid format?

    Thanks!

    • Yes. I tried a select * from one of my tables and yes, I get the same problem.

    • how many other places besides here and Reddit are you asking? you should also let us know what version of SQL Developer and what version of Oracle Database you are working with.

    • the oracle community. Not a lot of activity on reddit sub. I appreciate your response. My query runs in developer and produces the desired result, with a ton of columns. I’ve also tried limiting the columns, but even 1 column does the same thing. I have to embed this is a 3rd party application, so it has to be rapped in a procedure, function or package. Not my forte, but it’s time to learn.

      “Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production”
      “PL/SQL Release 11.2.0.4.0 – Production”

      screenshot of results – https://community.oracle.com/thread/3928052?sr=inbox

      Thanks, and I will link my other post back here, if that’s OK with you.

  9. Greg Arroyo Reply

    Hi Jeff,

    While using Unit Testing feature in SQL Developer with SYS_REFCURSOR as IN/OUT parameter, I keep getting an ‘Unsupported feature’ error. Have you encountered this one please?

    Greg

  10. Hello,

    I’m currently trying to run a procedure, but I can’t get the Output Variables Log, it simply doesn’t appear when i click ok/run.

    Is there a posibility to enable this log manually or something?

    • Just to confirm, you’re running it from a procedure editor, not the SQL Worksheet? And if so, do you see the code in the popup window that ‘catches’ the cursor to display?

    • Laurentiu

      Yes, I have a popup where I have the code to run the PL/SQL.
      And I add the needed data that I’m searching for and click ok.
      I run the procedure on other machines and I saw that the Output Variables Log appears in another popup. (I quess it is a configuration thing but I cannot find it)

    • Laurentiu

      I have Version 3.2.10.09.. and I don’t have that option.
      But I found how to do so for this version and took the settings from one of my colegues.

      Thanks

    • Hello! I know this is a long shot but i am currently having the same issue you were having with the Output Variables Log. You mention that you were able to fix it, How did you do so?

  11. Hi Jeff,

    Thanks for the above post..Its really helpful.
    I am trying to run simple procedure in SQL Developer 4.0.

    create or replace procedure try_refcursor(table_name in varchar2,refcursor out sys_refcursor)
    is
    type ref_cursor is REF CURSOR;
    output ref_cursor;
    begin
    if table_name=’EMP’ then
    open output for select empno,ename from emp;
    else
    dbms_output.put_line(‘Wrong program’);
    end if;
    end;

    the procedure got successfully executed but I am unable to see the output in SQL Developer. Is there any way?

    Thanks in advance

    • you’re writing to dbms_output’s buffer – so you have to ask to see the output. View > DBMS_OUTPUT. turn it on for your connection. run the code, look at the panel.

      or if you’re running it from a script in a SQL Worksheet, add SET SERVEROUTPUT ON to the top of your script, before your BEGIN block.

    • Thanks Jeff for quick reply.
      Its working now .

  12. Sahil Agarwal Reply

    what is meant by ->-Cusrsor?
    What is this symbol “->-“

  13. This is not working for me. Neither in SQL developer 4.0 or 3.0.

    Also the what is the prupose of the “:v_Return := v_Return; –<– Cursor" line. For me that only causes a prompt to occur allowing me to set the value of v_Return.

    • I have the same issue – just get a window prompting for a value of v_return.

      I’m running SQL Developer v18.3.

    • open your procedure from the tree – you should see the source in the code editor – in that editor is a toolbar with a green play button – that will EXECUTE your procedure. hit that button and set any inputs as required

  14. Hi Jeff,
    I am using SQL Developer 4.0.2.15 , and connecting to Oracle 12c database. I have created a local user BETA_APP in a Pluggable Database PDBORCL12C3. I have a package in the REGO_APP schema , BETA_DATA_PKG, I am unable to execute a stored procedure GET_BETA_INFO in that package using the Package execute option as it fails with PLPLS-00201: identifier ‘BETA_APP.BETA_DATA_PKG’ must be declared. Can you please let me know if you know how to set a container database in SQL Developer so that it knows that the session runs in a particular PDB.
    Thank you for your guidance.
    Kajal

    • With version 4.1 of the Modeler, we’re just now stepping into doing full compares for Physical models. You can see that in the new features page here.

      But I don’t think it looks at stored proc. And for a few reasons, packages can many tens of thousands of lines of code long. I’m sure there’s a solution there somewhere, we just haven’t gotten to that step yet.

    • Hi Jeff,
      Thank you for your response.
      BETA_APP is not a global user like C##BETA_APP.
      Its a local user in PDB . To access the packages and functions in that Schema I have to set the CONTAINER to PDBORCL12C3.

      Thanks,
      Kajal

    • So you’re connected to the CDB and not the PDB? Just connect to the database you want to work with (PDBORCL12C3.)

    • Yes, I am only connected to te CDB not the PDB, didnt know I can separately connect to the PDB, please tell me how to create just the connection that connects to a PDB?

    • the PDB should self-register with the listener – poll the listener on the server, lsnrctl -status, and get the service name for the PDB, and use that to connect

  15. Hi Jeff,

    We are attempting to execute a procedure that opens a cursor from a collection using the table() function:

    OPEN p_result_out FOR
    SELECT * FROM TABLE(v_otb_msn_mon);

    When we run the proc, it disconnects from the database, we get the error “no more data read from socket” and are unable to see the cursor output results. If we open a cursor by selecting from a normal table it works fine. Any ideas? We can run a test script and output the results of the above cursor using a dbms_output.put_line, so we know the proc is executing correctly and the collection variable does have data in it.

  16. Edward Fabian Betancourt Reply

    When I’m doing a debug, look the type that it show, I can´t see the variables value, it shows $oracle.bultin.OPAQUE, In watch only shows ?(ask character)

    • Hmmm, it SHOULD work, but w/o your code, I can’t be sure. As a workaround, you can always declare local variables and assign the type’d variable values to them for debug purposes, but you really shouldn’t have to.

  17. Edward Fabian Betancourt Reply

    Hi jeff, I have this question. I have this declaration:

    TYPE vty_recCliente IS record
    (
    –Nuevos Campos
    Nombre_Cliente VARCHAR2(100),
    Apellidos_Cliente VARCHAR2(100),
    Tipo_Cliente VARCHAR2(40)
    );

    When i was making a debug I like to se the content of the variable:
    IF (LENGTH(precCliente.Nombre_Cliente)>100) THEN

    But neither by watch or inspector i can see the value. Where Is my mistake? Can you help me? Thanks

    • if this is a standalone type, it needs to be compiled with Debug also to be able to step into it or use watches on it

Write A Comment