SQL Developer Tip: Viewing REFCURSOR Output

thatjeffsmith SQL Developer 109 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 Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 109

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

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

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

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

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

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

          2. thatjeffsmith Post
            Author
  5. 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
      1. I have a similar anonymous block which is not getting executed on sql worksheet in sql developer……do i need to do something?

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

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

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

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

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

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

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

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

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

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

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

    1. thatjeffsmith Post
      Author

      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]

  15. 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”)…

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

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          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.

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

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

    1. thatjeffsmith Post
      Author

      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!

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

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

    1. thatjeffsmith Post
      Author

      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.

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

    1. thatjeffsmith Post
      Author

      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.

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

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

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

      1. thatjeffsmith Post
        Author

        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.

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

          1. thatjeffsmith Post
            Author

            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.

  23. 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;
    /

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

      1. thatjeffsmith Post
        Author
          1. thatjeffsmith Post
            Author

            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.

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

          3. thatjeffsmith Post
            Author

            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]

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

          5. thatjeffsmith Post
            Author

            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]

          6. thatjeffsmith Post
            Author

            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.

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

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

    1. thatjeffsmith Post
      Author

      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.

    1. JeffS Post
      Author

      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!

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

  27. 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… πŸ™

    1. JeffS Post
      Author
  28. 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?

    1. JeffS Post
      Author

      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.

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

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

    1. JeffS Post
      Author
  30. 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.

    1. JeffS Post
      Author

      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 jeff.d.smith@oracle.com

    1. JeffS Post
      Author

      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.

Leave a Reply

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