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.
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.
Your output window will now have an ‘Output Variables’ pane that has a grid with the first 100 records in your cursor.
125 Comments
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
everything’s based on your date format preferences…unless you’re doing something explicit in your code or your session
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
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
Your REF CURSOR returns…dynamic SQL – and you want to then execute that?
Okay. Is there any way around? Please suggest.
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
Sounds like a bug.
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
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]
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”)…
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!!
Maybe. What version are you running?
Oracle SQL Developer 4.0.2 (4.0.2.15.21)
Thanks for the quick answer 🙂
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.
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?
Just copy and paste today, and yeah, no way to get the column headers.
Is it possible to expand the window length?
In what way, are you not able to scroll through the grid to see your data?
Is there any way to increase the limit of 100 rows?
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
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
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.
Hi
I already started a thread here : https://forums.oracle.com/forums/thread.jspa?threadID=2482891&tstart=0, but I didn’t get any replies
I would like very much to get the developer on-line and get some help.
Where are your posts regarding debug info for SQL Developer?
Thanks,
Rudi
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
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.
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.
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.
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
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?
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.
should read “CTRL SHIFT C appears to be the standard … ” – the keys were stripped out by whatever validation is used when submitting comments.
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.
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.
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.
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;
/
Running as a script emulates SQL*Plus, so what you’re seeing is the expected behavior.
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;
How are you calling this from SQL*Plus?
sql> variable v_rs refcursor
sql> variable v_no number
sql>exec :v_no:=get_emplist(10,:v_rs);
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?
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.
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]
Thank you. this worked.
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.
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?
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
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;
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.
Thanks for the good information. Its really valuable.
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!
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
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… 🙁
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.
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?
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.
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
records? I don’t think so. I can raise an ER with the developer though.
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.
Less work for me AND you got it to work? Double-win!
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.
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
give me an entire code block that demonstrates the issue, i’ll try to take a look
Hi, this is very cool, but there is exists an option that let me see the tab [output variables] from the worksheet?
Thanks
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.