How to print content of refcursor within a PL/SQL procedure?

Question

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?

• mustaccio: Can you please point to the documentation reference showing print as a valid PL/SQL command?
• user311946: @mustaccio: See here docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/…
• mustaccio: Nope, wrong documentation. You're looking and SQL*Plus commands, which is not the same.
• user311946: @mustaccio: I see. So, print command can be used within a script but not inside a procedure, right?

Answer 1

Or you can modify your SP as below:-

create or replace procedure cursor_show (rc_out OUT sys_refcursor) is
 v_rc sys_refcursor;
begin 
 
 open rc_out for 
 select <params> from <table_name>; --my SELECT 
-- put an exception block to get/log the errors
end;

To print the output of 'rc_out'

DECLARE 
 LV_CR_OUT SYS_REFCURSOR;
BEGIN
 cursor_show(LV_CR_OUT );
DBMS_SQL.RETURN_RESULT(LV_CR_OUT); --- check oracle documentation to enable this.
END;