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!
thatjeffsmith
Author

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

118 Comments

  1. Hi Jeff,

    I have a procedure that returns DATES in a SYS_REFCURSOR in SQL Developer 4. In the Output Variables grid I am only seeing the time part of the date. The time part is always some variation on 00:00:00 based on my Date Format preferences. Is this expected behaviour in the grid?

    Cheers,

    Chris

    • thatjeffsmith

      everything’s based on your date format preferences…unless you’re doing something explicit in your code or your session

      dates returned in a refcursor

    • Hi Jeff,

      Thanks for replying. My settings for Date Format are DD-MON-RR HH24:MI:SS. If I execute the procedure the Output Variables grid shows dates like 19-NOV-14 00:00:00, but if I run the elements of the procedure separately in a worksheet then the Query Result shows 19-NOV-14 14:22:45.

      Cheers,

      Chris

  2. Hi, Any options available in Oracle to execute query statements in REF CURSOR result sets?. If so, please explain the concepts. We need a pre-loaded table to retrieve additional values from that table without retrieve the data from the base table every iteration.

    Thanks in advance.

    Regards,
    Mani

  3. Hi Jeff,
    I have a function(function_consumer) which will take a ref cursor as input parameter.
    I am using SQL Developer version 4.0.2.15.21. To generate the ref cursor I am using another function( function_creator) which will return the ref cursor. And I am calling the function_creator from dynamic value input option of SQL Developer.
    But while I am calling this test implementation, it is throwing error as ‘Invalid SQL Statement’.
    One interesting thing is if I am calling the function_consumer below it is working nice and providing correct output.
    Select function_consumer(function_creator(1)) from dual;
    Is there anything I am missing? Please advice.

    Best Regards,
    Ayan

  4. SQLDev4-User Reply

    Hi, I am currently using sql developer 4.1 to replace toad 9.
    Which i have no clue to do what i used to be doing in toad

    declare
    s_cur sys_refcussor;
    begin
    open s_cur for
    select * from dual;
    :to_grid:= s_cur;
    end;

    Then it will prompt an enter bind and then select cursor, which will return a grid.

    However, when i do this in the SQL Developer 4.1
    it requests me to enter binds without any option (uh, unlike in the toad)
    and if i try to ignore or just type anything with that,

    it returns

    Error report –
    ORA-06550: line 7, column 17:
    PLS-00382: expression is of wrong type
    ORA-06550: line 7, column 1:
    PL/SQL: Statement ignored
    06550. 00000 – “line %s, column %s:\n%s”
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    Please suggest how can i do the same thing in the sql developer.
    Thanks

    • thatjeffsmith

      We don’t capture refcursors to grids in the worksheet, but we do it in the Procedure Editor – see my reply to Edilay’s similar question in the comments section.

      In the worksheet, you’ll need to use the SQL*Plus syntax…

      VAR RC REFCURSOR
      EXEC :RC := GET_EMPS(30);
      print rc

      [text]
      anonymous block completed
      RC
      ———————————————————————————————————————————————————————————————
      EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
      ———– ——————– ————————- ————————- ——————– ————————- ———- ———- ————– ———- ————-
      114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-94 12.00.00 PU_MAN 11000 100 30
      115 Alexander Khoo AKHOO 515.127.4562 18-MAY-95 12.00.00 PU_CLERK 3100 114 30
      116 Shelli Baida SBAIDA 515.127.4563 24-DEC-97 12.00.00 PU_CLERK 2900 114 30
      117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-97 12.00.00 PU_CLERK 2800 114 30
      118 Guy Himuro GHIMURO 515.127.4565 15-NOV-98 12.00.00 PU_CLERK 2600 114 30
      119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-99 12.00.00 PU_CLERK 2500 114 30
      [/text]

  5. Hello,
    I’m having troubles testing a stored procedure that contains a user defined cursor type output parameter. Stored proc. signature is:

    TYPE ref_cursor_tst IS REF CURSOR;
    TYPE arrWarningCode_tst IS TABLE OF VARCHAR2 (4000)
    INDEX BY BINARY_INTEGER;

    PROCEDURE SP_ITF_CU_DOCUMENT_Test (
    p_projectNumber IN VARCHAR2,
    p_tag IN VARCHAR2,
    p_title IN VARCHAR2,
    out_document_curs OUT ref_cursor_tst,
    out_errorCode OUT VARCHAR2,
    out_arrWarningCode OUT arrWarningCode_tst);

    I wanted to try out your tip:

    declare

    outDocCurs PD360BADMIN.PKG_ITF_GENERAL_TST.ref_cursor_tst;
    begin
    PKG_ITF_GENERAL_TST.SP_ITF_CU_DOCUMENT_TEST (‘XXX’, docRef, ‘Doc title’, outDocCurs, outErrorCode, arrWarningCodes2);
    DBMS_OUTPUT.PUT_LINE(outDocCurs);
    end;
    /

    But I get “PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE'” error.

    Would you have any clue ?
    Thank you very much for your help and for supporting the community!

    Seb!

    PS1: SQL Developer 3.2.20.09
    PS2: with SQL*Plus syntax I managed to handle the cursor but not the “out_arrWarningCode” (of type “TABLE of VARCHAR2”)…

  6. Hello Jeff,

    I tried to open the Output variables window but it is not possible because it is not in the Log option from the Viw tab, is it problem of the sqldeveloper version or something else?

    Thank you!!

    • Oracle SQL Developer 4.0.2 (4.0.2.15.21)

      Thanks for the quick answer 🙂

    • thatjeffsmith

      Hmmm, it could be the desktop panel needs reset. Try Window > Reset Windows To Factory Settings

      If that doesn’t work, i would run SQLDev in Debug mode and check out the log panel for errors after your stored proc has finished running.

  7. Is it possible to export this output?
    I know you can copy and paste into a spreadsheet, however when you do that you don’t copy the header, only the values.
    How can I copy those headers too?

  8. Apologies for being dumb, but how can I test this functionality?
    I have run the “create function…” code, which created the function without any problem.
    But when I try to execute the function using the code supplied, i.e.

    DECLARE
    v_Return SYS_REFCURSOR;
    BEGIN
    v_Return := REFCURSOR_FUNCTION();
    :v_Return := v_Return; –<– Cursor
    END;

    , I get a pop-up that asks me for a value for the bind variable. If I enter nothing, it fails; if I enter XXXX it fails. What should I enter for the bind variable? Anyway, why do I need a bind variable? The variable v_Return has already been declared.
    Also, I can't see any "Output Variables" pane. Confused.
    Thanks,
    Jason

    • thatjeffsmith

      That code is generated for you when you click the ‘Execute’ button in the Procedure Editor. Find function in the tree, and execute it from there.

      You’re not dumb, more likely I wasn’t clear enough in my explanation of the feature. I’ll polish it up a bit later today so no one else has to feel ‘dumb.’ Sorry about that!

    • Ah, I see! I’ve only just installed and started using SQLDeveloper. I would never have expected to be able to just run a function like that – coming from SQL*Plus I would have expected to have to set up a wrapper with DBMS_OUTPUT.PUT_LINEs. Very useful.
      Thanks Jeff for the speedy response.
      Jason

    • thatjeffsmith

      If you’d like, we can make it harder 🙂

      Welcome to the world of the GUI! SQL*Plus is a great tool, and I think most folks will find they use both.

  9. Hi
    I am using Sql Developer 3.2.20.09 (64-bit)
    I started a Database Copy session and I dont see anything in the log.
    Just some rows : Start …End
    Any ideas how can I debug this?
    Thanks,
    Rudi

    • thatjeffsmith

      Can you start a thread on the forums? I can get the developer on-line and we can triage your issue more efficiently there. But my guess is that it’s a privilege issue with the account your using for hte copy on the target/destination connection. If you want to debug on your end, I have two posts here on how to collect debug info for SQL Developer you can try.

  10. Paul, London Reply

    Hi Jeff,

    I don’t know if exporting to XLS clipboard would actually add anything in terms of format. That’s not central – the angle I’m trying to get at is what a newbie / infrequent user would do in trying to get data copied onto an excel spreadsheet. Essentially a usability / perception / ease of use / workflow issue.

    They would think – I want to copy the contents of this grid onto an excel spreadsheet. They would probably CTRL-C, or even Edit-Copy (there is no copy via a right click).

    After doing that, they would see that the headings haven’t been copied. They would scratch their heads and may hopefully try again, right clicking and selecting Export.

    The key task that they wish to do is copy to an already open xls worksheet. Bearing this in mind, I would expect them to select xls as a format, and clipboard as a destination. That gives rise to the error seen previously.

    If, rather than displaying the error, Sqldeveloper instead allowed the action to continue as a CSV cut and paste to the clipboard, the user could then paste into their destination worksheet. They wouldn’t have to create an interim export worksheet and cut’n’paste from it to a destination worksheet. Quicker, fewer steps, with no error message.

    Of course CTRL SHIFT C is easier and achieves the same, but blocking the above workflow with an error message doesn’t achieve anything at all. A CSV copy to clipboard would be preferable.

    Not a major thing, but definately a usability improvement.

    • thatjeffsmith

      Copying to an existing instance of Excel, now I got ya.

      still, copying to CSV > clipboard will do the trick. Agreed, it’s not as intuitive as it could be.

      Thanks for clarifying your position, really helps.

  11. Paul, London Reply

    thanks Jeff, it’d be one less thing for the naysayers to complain about while trying to wean them off the old paid for tools they use.

    To clarify the comments about exporting, try doing the following:

    1. Right Click on a SQL worksheet data grid.
    2. Pick “xls” as Format.
    3. Pick “Clipboard” as Save As.
    4. Click “Next”
    At this point, I get the following error message: “Saving as Clipboard is not supported for exporting data that is formatted as xls”.

    Of course, I happen to know that CTRL SHIFT C will allow me to cut and paste into excel, but many other users, especially new ones, mightn’t

    • thatjeffsmith

      change it from XLS to CSV – that will give you want you want

      exporting to XLS clipboard doesn’t grok for me…what would that do exactly?

  12. Paul, London Reply

    Hi Jeff,

    is there any way to capture column headers when copying the data to excel. appears to be the standard (albeit undocumented) way of doing this elsewhere within SQLDeveloper, but doesn’t work here.

    • Paul, London

      should read “CTRL SHIFT C appears to be the standard … ” – the keys were stripped out by whatever validation is used when submitting comments.

    • thatjeffsmith

      I see what you mean Paul. Would you be happy with the header included on the clipboard, or would you rather have the Export dialog supported on a context menu? Then you could send the cursor to clipboard, file, worksheet in various formats.

    • Paul, London

      Hi Jeff,

      thanks for the quick reply. Talking to colleagues here, we think the ref cursor grid (and all other grids) should be consistent with the SQL worksheets, ie have both export methods.

      That being said, however, we also think that the standard Export dialog should also have an Export grid to clipboard option on it as well. Or a footnote message telling people that they can use CTRL-SHIFT-C.

      The reason being that a lot of people don’t know about CTRL-SHIFT-C until they’re told by someone in the know. They wrongly assume that you have to export to an excel spreadsheet first, then navigate to and open that spreadsheet, before pasting the copied contents into the final destination spreadsheet / report. All a bit of a faff when you could CTRL-SHFT-C and then directly paste into the destination spreadsheet / report.

    • thatjeffsmith

      Consistency, I’m all for it. No promises, but look forward to improvements in future releases.

      I’m not sure I understand your comment regarding the Export dialog and ‘clipboard’ option. It’s already there. On the Save As control, use the drop down selector and choose ‘Clipboard.’

      I don’t know of a better way to publicize the SHIFT-CTRL-C other than adding it to the ‘Edit’ menu, and that’s not ideal, as that only applies when a Grid has context, and the Edit menu is for editors. Open to suggestions.

  13. On a sql worksheet I can run this as a script and see ref cursor results but in a text format. I am not getting a grid. ?

    Thanks, Michael

    VAR V_RETURN REFCURSOR
    SET AUTOPRINT ON
    DECLARE
    v_rc SYS_REFCURSOR;
    BEGIN
    OPEN v_rc FOR select * from XXXX;
    :v_Return := v_rc ;
    END;
    /

    • Have you got a time to review my code?

      Following function is not executing from SQL Developer by Run/Debug it returns error PLS-00382: expression is of wrong type.

      CREATE OR REPLACE FUNCTION GET_EMPLIST (p_deptno IN NUMBER,p_emp OUT sys_refcursor) RETURN NUMBER
      as
      v_id NUMBER;
      BEGIN
      OPEN p_emp FOR
      SELECT * FROM emp where deptno=p_deptno;
      SELECT COUNT(*) INTO v_id FROM emp WHERE deptno=p_deptno;
      RETURN v_id;
      END GET_EMPLIST;

    • sql> variable v_rs refcursor
      sql> variable v_no number
      sql>exec :v_no:=get_emplist(10,:v_rs);

    • thatjeffsmith

      I see the problem now, or at least the beginning of the problem. We’re trying to capture a bind output param when it’s a function. This line needs commented out like so
      [sql]
      — :v_Return := v_Return
      [/sql]

      Let me know if that works. This would be a bug…I think.

    • Sorry, i did not get your point where i have to comment the code, during calling it & how?

    • thatjeffsmith

      When you hit the execute button, you get a popup dialog with the actual PL/SQL script used to execute your function. Comment out that last line I mentioned previously.
      [sql]
      — :v_Return := v_Return
      [/sql]

    • Thanks Jeff, no luck, the error this time is Connecting to the database Dev-D.
      Invalid column index
      Process exited.
      Disconnecting from the database Dev-D.
      There is no result displayed in output variable only a message invalid column index.

    • thatjeffsmith

      It’s a bug! And the developer has fixed it for the next version.

      For now, if you change the ANON block to THIS, it will work:

      [sql]

      DECLARE
      P_DEPTNO NUMBER;
      P_EMP sys_refcursor;
      v_Return NUMBER;
      BEGIN
      P_DEPTNO := 30;

      v_Return := GET_EMPLIST(
      P_DEPTNO => P_DEPTNO,
      P_EMP => P_EMP
      );

      :v_Return := v_Return; –we are switching the order of the vars, which shouldnt matter but it does, weird
      :P_EMP := P_EMP; –<– Cursor

      END;
      [/sql]

    • thatjeffsmith

      Open it in the Procedure Editor via the connection tree. To run, click the green ‘play’ button. To debug, compile with debug, then click the red ‘bug’ debug button. This will open the anon block dialog. Set the Target to ‘INST_EMP’ then supply the inputs for v_eno and v_ename, then click ‘OK.’ Your program will either then execute or start a debug session.

  14. Thank you Jeff, you are right that with return clause only one value can be returned from function but we can use OUT parameter in functions. The above function execute successfully from sql prompt but Sql Developer returns error. For returning of out parameter please see
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:561822956834
    Here is almost the same type of function which I have sent you so people are using function like it.
    http://stackoverflow.com/questions/6360244/how-to-call-an-oracle-function-with-a-ref-cursor-as-out-parameter-from-c

    Thank you for your time.

    • so there is no option in Sql Developer to debug this type of function?

    • thatjeffsmith

      You should be able to debug any valid pl/SQL object. Let me have another look at your code…

    • This is a valid pl/sql object and can be executed from sqlplus but it returns error in sql developer

  15. Following function is not executing from SQL Developer by Run/Debug it returns error PLS-00382: expression is of wrong type.

    CREATE OR REPLACE FUNCTION GET_EMPLIST (p_deptno IN NUMBER,p_emp OUT sys_refcursor) RETURN NUMBER
    as
    v_id NUMBER;
    BEGIN
    OPEN p_emp FOR
    SELECT * FROM emp where deptno=p_deptno;
    SELECT COUNT(*) INTO v_id FROM emp WHERE deptno=p_deptno;
    RETURN v_id;
    END GET_EMPLIST;

    • thatjeffsmith

      You have a Function with a RETURN value AND an OUT parameter. A function should only ever return one value. This should probably be a procedure with two OUT parameters.

      Try this instead

      [sql]
      create or replace
      procedure GET_EMPLIST_AND_COUNT (p_deptno IN NUMBER,p_emp OUT sys_refcursor, p_emp_count OUT number)
      as
      v_id NUMBER;
      BEGIN
      OPEN p_emp FOR
      SELECT * FROM scott.emp where deptno=p_deptno;
      SELECT COUNT(*) INTO p_emp_count FROM scott.emp WHERE deptno=p_deptno;

      END GET_EMPLIST_and_count;
      [/sql]

      When I run this, Oracle will return 2 values, the count, and the records.

    • JeffS
      JeffS

      You’re quite welcome Prasad! If you ever want to write a post on your blog about your experience with SQL Developer, let me know if you need any help with that!

  16. Hi Jeff,
    Thanks for your quick reply. Am downloading the new version 3.1EA.
    On the Count Rows point: Some time while generating a report we might need to count the rows and report the same. I agree that it will return only the no of total rows fetched. But some time this feature is required one..
    Need your valuable feed back

  17. Hi Jeff,
    In addition to above I am not able to count the rows from cursor out put. A count rows option is there but when i execute it, it not displaying the total count as such.

    Please reply asap… am in a middle of UAT… 🙁

    • JeffS
      JeffS

      How many rows do you think you are working with? Less than 10,000?

      Here’s an interesting take on worrying about the size of a refcursor from AskTom.

      I agree that the ‘Count Rows’ grid feature should probably be implemented here…but understand it will never count higher than what was fetched.

  18. Hi Jeffs,
    I can see the refcursor o/p. but it display only a 100 no rows.. How can I see the rest of the rows? How can I save/extract the output to xls?

    • JeffS
      JeffS

      I ran into the same limitation Srabanjit. So after some tense negotiation, I managed to get the limit increased to 10,000 rows for version 3.1. You can download v3.1EA3 and start using it today.

      Something on my list for v3.2 (think version.next.next) is consistent grid features throughout the product. So you could indeed take advantage of the worksheet export to Excel feature in this REFCURSOR output panel. Today, you need to select all, copy, & paste. It pastes as tab delimited text, so your office software should be able to parse that into a valid workbook.

    • Mahendra Prabu

      Hi Jejjs,

      Is there a way to increase ref cursor output limit from 10000 to 20000 ?. Need quick response and will help a lot.

      Regards,
      Mahendra Prabu

  19. Jeff,

    I actually managed to figure out the problem. I had wrote a manual test script that worked when ran normally as a script outside the debugger, but would not run under the debugger using the ‘From File’ option.

    I finally started getting the hunch that the DBMS_OUTPUT lines were the cause of the problem so I removed those. It turned out this was not the case…but a very subtle thing at the top of my script I had was causing a ‘Vendor code 900’ error.

    I had:
    CLEAR SCREEN;
    SET SERVEROUTPUT ON;

    When I removed these 2 lines my existing script finally started working with the debugger.

  20. Hi, I am having trouble viewing my sys_refcursor from a procedure as an output parameter. Here is the auto script that gets generated when I click the debug button:

    DECLARE
    P_INPUT SYS_REFCURSOR;
    P_OUTPUT SYS_REFCURSOR;

    BEGIN

    OPEN P_INPUT FOR
    SELECT ‘test_user’, ‘test_app’, ‘test_app_type’, ‘test_obj’
    FROM DUAL;

    DST_ECT_MOD2.DST_ECT_LOOKUPEXPBYUSER_BULK(
    P_INPUT => P_INPUT,
    P_OUTPUT => P_OUTPUT
    );

    :P_OUTPUT := P_OUTPUT; –<– Cursor

    END;

    I get a Missing IN or OUT parameter at index:: 1 when I hit the OKAY button after the popup with the script appears. I am using sql developer 3.1.

    • JeffS
      JeffS

      Can you send me the source for DST_ECT_MOD2.DST_ECT_LOOKUPEXPBYUSER_BULK() or can you code up a sample using HR data that I can use as a test/example?

      I wonder if that P_OUTPUT:=P_OUTPUT line is causing problems too…

      You can contact me directly at [email protected]

    • Hi Jeff,

      I am facing the same issue while debugging through SQL developer. Could you please let me know the reason in case it is sorted already?

      Here is the error message -:
      Missing IN or OUT parameter at index:: 8

      In my procedure, we have arrays that are returned as output. Moreover, I have used the index assignment as
      :p_var(1) := p_var(1);
      where p_var is an user defined array.

      Regards,
      Prerna

  21. Hi, this is very cool, but there is exists an option that let me see the tab [output variables] from the worksheet?
    Thanks

    • JeffS
      JeffS

      We’re only going to display the standard output there, much like SQL*Plus would. You would need to build DBMS_OUTPUT or some other mechanism into your PL/SQL to make the feedback available in the worksheet.

      The worksheet doesn’t parse the source code of the object being executed, so we don’t know what to be looking for before we execute your code.

      The worksheet does support the SQL*Plus PRINT command, which can be used to show refcursor output.

Write A Comment