Sample Header Ad - 728x90

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