Oracle Dynamic SQL: generic search - REF CURSOR

This post is a continuation of a topic, I've raised previously - dynamic implementation of generic searches. As  I mentioned in that post, there are circumstances, when it is much more convenient to return a pointer to a row-set instead of that row-set. Or in terms of Oracle - REF CURSOR instead of a collection.

Here is a variation of my search procedure that uses REF CURSOR:

create or replace function f_search_ref
    (i_limit_nr number:=null,
     i_param_empno_nr   number:=null,
     i_param_ename_tx varchar2:=null,
     i_param_job_tx varchar2:=null)
return SYS_REFCURSOR
is
    v_sql_tx varchar2(32767);
    v_ref sys_refcursor;
begin
    -- opening
    v_sql_tx:=
     'declare '||chr(10)||
     '  lv_count_nr constant number:=:1;'||chr(10)||        
     '  lv_empno_nr constant number:=:2;'||chr(10)||
     '  lv_ename_tx constant varchar2(50):=:3;'||chr(10)||
     '  lv_job_tx  constant varchar2(50):=:4;'||chr(10)||
     'begin '||chr(10)||
    'open :5 for select empno from emp '||chr(10)||
        ' where rownum <=lv_count_nr ';

    -- i_param_empno
    if i_param_empno_nr is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and empno = lv_empno_nr ';
    end if;

    if i_param_ename_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and ename like ''%''||lv_ename_tx||''%'' ';
    end if;

    if i_param_job_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and job = lv_job_tx ';
    end if;

    -- closing
    v_sql_tx:=v_sql_tx||';'||chr(10)||
              'end;';

    dbms_output.put_line(v_sql_tx);

    execute immediate v_sql_tx
        using nvl(i_limit_nr,50),
              i_param_empno_nr,
              i_param_ename_tx,
              i_param_job_tx,
              v_ref;
              
    return v_ref;
end;

Key changes are highlighted:

• Function should return SYS_REFCURSOR - it's a "weak" REF CURSOR that can point to any type of a row-set, exactly as needed
• To open that REF CURSOR I am using "OPEN...FOR..." statement
• Please, notice that even we "think" that REF CURSOR is an output of out Dynamic SQL, for Oracle it is still IN-parameter.

And here is an example of how that REF CURSOR can be used.

declare
    v_ref SYS_REFCURSOR;
    v_tt id_tt;
begin
    v_ref:=f_search_ref(10,null,null,null);
   
    fetch v_ref bulk collect into v_tt;
    close v_ref;
   
    dbms_output.put_line('Fetched:'||v_tt.count);
end;

The reason I've included it is clear - PLEASE, do not forget to close REF CURSORS when you finished using it! Resources are limited, so let's not waste it unnecessarily!