How to obtain a SYS_REFCURSOR having an explicit cursor in a stored procedure Oracle PL/SQL

I want to write a piece of code in an Oracle stored procedure in which I have defined an explicit cursor and I want to translate it in a generic SYS_REFCURSOR.

I want to avoid to define any specific object-type based on the SQL of the explicit cursor.

Thank you in advance.

The code should be:

CREATE OR REPLACE procedure TransformExplicitCursor(out_sysrefcursor out sys_refcursor) AS
BEGIN
    DECLARE
        CURSOR lc_explicit_cursor IS SELECT ....-- here a complex select
    -- todo something in order to obtain a sys_refcursor from lc_explicit_cursor
END TransformCursor;

Ref cursors can open a SQL using a variable to provide the SQL statement rather than a string literal, so you can define your SQL in one place only and use it in multiple places:

declare
  tmp_cursor sys_refcursor;
  tmp_sql varchar2(32767) := 'select * from dual';
begin
  if true
  then
    open tmp_cursor for tmp_sql;
  else
    open tmp_cursor for tmp_sql;
  end if;
end;

If you need to use a complex SQL on many lines with lots of single quoted strings within it, use the alternative quoting syntax q'{...}' so you don't have to escape those single quotes with the SQL and you can also use as many lines as you need. You can also inject parameters via bind variables:

declare
  var_dummy varchar(1);
  tmp_cursor sys_refcursor;
  tmp_sql varchar2(32767) := q'{
  select :dummy1 
    from dual
   where dummy = :dummy2
  }';
begin
  if true
  then
    open tmp_cursor for tmp_sql using 'A','X';
    fetch tmp_cursor into var_dummy;
    dbms_output.put_line(var_dummy);
  else
    open tmp_cursor for tmp_sql using 'B','X';
    fetch tmp_cursor into var_dummy;
    dbms_output.put_line(var_dummy);
  end if;
end;

If however you intend to use the SQL within your procedure, you should do normal cursors and not ref cursors. Ref cursors are used when the consuming code doesn't know and doesn't care what the SQL is, only what the results are (with a specific set of columns, of course), and usually are consumed by calling procs, not within the same procedure that opened them.

So, if you are using this within the procedure defining them and the only change each time you reuse the same SQL is a parameter, use cursor parameters:

declare
  var_dummy varchar(1);
  CURSOR tmp_cursor (dummy1 IN varchar2,dummy2 IN varchar2)
  IS
  select dummy1 
    from dual
   where dummy = dummy2;
begin
  open tmp_cursor ('A','X');
  fetch tmp_cursor into var_dummy;
  dbms_output.put_line(var_dummy);
  close tmp_cursor;
    
  open tmp_cursor ('B','X');
  fetch tmp_cursor into var_dummy;
  dbms_output.put_line(var_dummy);
  close tmp_cursor;
end;