Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table params as 
    select 'key'||level key, 'value'||level value , 'memo about this param' memo
    from dual connect by level<=3
3 rows affected

create or replace package democursor as
    type paramtype is record (key varchar2 (8), value varchar2 (16));
    type weakcurtype is ref cursor;
    type strongcurtype is ref cursor return paramtype;
    type paramstype is table of paramtype;
    
    function getParam (key varchar2) return paramtype;  
    procedure openParams (cur out strongcurtype);  
    function getParams (likevalue varchar2) return paramstype;  
end;
/
create or replace package body democursor as
    function getParam (key varchar2) return paramtype is
        param paramtype;
    begin 
        select key, value into param
        from params
        where key = getParam.key;
        return param;
    exception when no_data_found then null;     
    end getParam;
    procedure openParams (cur out strongcurtype) is  
    begin
        open cur for 
            select key, value 
            from params order by key;
    end openParams;
    function getParams (likevalue varchar2) return paramstype is
        cur weakcurtype;
        params paramstype; 
    begin
        open cur for select key, value from params 
        where value like likevalue||'%' order by key;
        fetch cur bulk collect into params;
        close cur; 
        return params;
    end;
end;
/
declare
    rc sys_refcursor;  
    param1 democursor.paramtype;
    param2 democursor.paramtype;
    params democursor.paramstype;
    what varchar (32);
    param varchar (32);

    function getParam (nth int) return democursor.paramtype is
        cur democursor.strongcurtype;   
        ret democursor.paramtype; 
    begin 
        democursor.openParams (cur);
        <<fechnth>> loop 
            fetch cur into ret; 
            exit fechnth when cur%rowcount = nth or cur%notfound;  
        end loop; 
        return ret;
    end; 
begin 
    param1 := democursor.getParam (key=>'key1');
    param2 := getParam (nth=>2); 
    params := democursor.getParams (likevalue=>'val');
    
    open rc for 
        select 'got first param' what, param1.key||'='||param1.value param from dual union all
        select 'got nth=2 param' what, param2.key||'='||param2.value param from dual union all
        select 'got val% params' what, params.key||'='||params.value from table (params) params;
    dbms_output.put_line (rpad ('what', 32)||'param');
    dbms_output.put_line (rpad ('-', 30, '-')||'  '||rpad ('-', 16, '-'));
    loop 
        fetch rc into what, param;
        exit when rc%notfound;
        dbms_output.put_line (rpad (what, 32)||param);
    end loop;    
end;
/ 
1 rows affected

dbms_output:
what                            param
------------------------------  ----------------
got first param                 key1=value1
got nth=2 param                 key2=value2
got val% params                 key1=value1
got val% params                 key2=value2
got val% params                 key3=value3