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
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.
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
Version 3.2 is about 9 years old, can you get version 19.4?
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
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
I don’t understand your question.
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’);
I accidentaly closed ‘output variables’ panel in sql developer
(not dbms_output ) how to get it back?
it is not under view menu
View – Log. Execute another stored proc that returns something, it’ll open on its own.
Thanks it worked
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
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!
if you open o_cursor for select * from hr.employees, does it display as it should?
Yes. I tried a select * from one of my tables and yes, I get the same problem.
can you share a screenshot?
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.
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
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?
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)
try Windows > Reset Windows to Factory Settings
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
And so it’s working now?
Also, upgrade 🙂
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?
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 .
Ref Cursor kind has been obtainable to permit record positions to be revisit from stored procedures & functions.
Can an anonymous block be run on SQL worksheet?
yes
what is meant by ->-Cusrsor?
What is this symbol “->-“
it’s how we capture the output
I have a similar anonymous block which is not getting executed on sql worksheet in sql developer……do i need to do something?
what are you expecting it to do?
Thank you smith, your inputs to this site help a lot!
-Ashish
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
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.
I’m confused – BETA_APP is a global user in the CDB, or is a local user in the PDB?
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
I am using TNS Names
Thanks , that works..!
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.
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.
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