SQL Developer Tip: Viewing REFCURSOR Output

thatjeffsmith SQL Developer 116 Comments

Tell Others About This Story:

If you work with PL/SQL, then this feature might be the #1 reason I recommend you immediately upgrade to at least version 3.0 of Oracle SQL Developer –
SQL Developer now automatically renders SYS.REFCURSOR output from your PL/SQL calls.

Prior to version 3.0, you needed to use the SQL*PLUS PRINT command to view the refcursor output. I hated this. Now in v3.0 and higher, it’s just automatic. No options to enable, no widgets to mess with, just hit the ‘execute’ button and you are good to go. This is a good example of the Developer Exchange having an impact on the product. Many of you voted for this feature, and now it is here. It would have happened eventually, but customer demand does have a big impact when it comes to prioritizing development requests.

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;

Execute the PL/SQL

When you open 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're only 1 click away!

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

SQL Developer makes debugging SYS.REFCURSORS easy!

Related Posts

Tell Others About This Story:

Comments 116

  1. 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’);

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

    1. thatjeffsmith Post
      Author
  3. 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

  4. 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!

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
    3. thatjeffsmith Post
      Author

      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.

    4. 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.

  5. 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

  6. 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?

    1. thatjeffsmith Post
      Author

      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?

    2. 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)

    3. thatjeffsmith Post
      Author
    4. 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

    5. thatjeffsmith Post
      Author
    6. 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?

  7. 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

    1. thatjeffsmith Post
      Author

      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.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
    2. I have a similar anonymous block which is not getting executed on sql worksheet in sql developer……do i need to do something?

    3. thatjeffsmith Post
      Author
  8. 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.

  9. 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

    1. thatjeffsmith Post
      Author

      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.

    2. thatjeffsmith Post
      Author
    3. 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

    4. thatjeffsmith Post
      Author
    5. 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?

    6. thatjeffsmith Post
      Author

      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

  10. 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.

  11. 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)

    1. thatjeffsmith Post
      Author

      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.

  12. 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

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *