How to print content of refcursor within a PL/SQL procedure?
0
votes
1
answer
393
views
**This question concerns Oracle PL/SQL (Oracle version 12c/19c) and Oracle SQL Developer (version 20.4).**
I have a procedure which calls a dynamically generated SELECT statement and save results into
sys_refcursor
. I would like to print content of the refcursor within the procedure and show it in ouput window of Oracle SQL Developer.
So far, I was able to only return the refcursor from procedure through OUT parameter and bind variable, and then print it. My current code of the procedure looks like this:
create or replace procedure cursor_show (rc_out OUT sys_refcursor) is
v_sql varchar2(1000);
v_rc sys_refcursor;
begin
--other statements generating SELECT
v_sql := 'select ...'; --my SELECT
open v_rc for v_sql; --get data
rc_out := v_rc; --return refcursor
end;
To print results, I need to call these statements:
var x refcursor;
execute cursor_show (:x);
print x;
I would like to encapsulate print
into procedure cursor_show
to get something like this:
create or replace procedure cursor_show is
v_sql varchar2(1000);
v_rc sys_refcursor;
begin
--other statements generating SELECT
v_sql := 'select ...'; --my SELECT
open v_rc for v_sql; --get data
print v_rc; --print data
end;
After that, I would be able to call the procedure and print the refcursor content with one-row statement execute cursor_show;
.
However, once I tried to compile such procedure, I received this error message:
Error(51,11): PLS-00103: Encountered the symbol "V_RC" when
expecting one of the following: := . ( @ % ;
The symbol ":=" was substituted for "V_RC" to continue.
Could you please advise how to call print
statement within body of the procedure?
Asked by user311946
Oct 21, 2024, 12:43 PM
Last activity: Jul 18, 2025, 07:40 AM
Last activity: Jul 18, 2025, 07:40 AM