create table my_table 
( my_NUMBER number primary key 
  , description varchar2(24) 
  , is_default varchar2(1) 
  )

insert into my_table values (1, 'Default', 'Y')

insert into my_table values (2, 'Test value', 'Y')

insert into my_table values (3, 'Other value', 'N')

create or replace package test1 as 
    type cur1 is ref cursor return my_table%rowtype; 
     
    procedure get_param (p_param in number, p_rs out cur1); 
    procedure get_def_param (p_rs out cur1); 
end; 

create or replace package body test1 as 
     
    procedure get_param (p_param in number, p_rs out cur1) is 
    begin 
        open p_rs for 
             select t.*  
             from my_table t 
             where t.my_number = p_param; 
    end  get_param; 
     
    procedure get_def_param (p_rs out cur1) is 
    begin 
        open p_rs for 
             select t.*  
             from my_table t 
             where t.my_number = 1; 
    end  get_def_param; 
end; 

declare 
    rc test1.cur1; 
    rec my_table%rowtype; 
begin 
    test1.get_param(42, rc); 
    fetch rc into rec; 
    if rc%notfound then 
        close rc; 
        test1.get_def_param(rc); 
        fetch rc into rec; 
    end if; 
    dbms_output.put_line('result='||rec.description); 
    close rc; 
end; 

create or replace package test1 as 
    type cur1 is ref cursor return my_table%rowtype; 
    type nt is table of my_table%rowtype; 
     
    procedure get_param (p_param in number, p_rs out cur1); 
end; 

create or replace package body test1 as 
     
     
    procedure get_param (p_param in number, p_rs out cur1) is 
        rc cur1; 
        recs nt; 
    begin 
        open rc for 
             select t.*  
             from my_table t 
             where t.my_number = p_param; 
        fetch rc bulk collect into recs; 
        if recs.count() = 0 then 
            close rc; 
            open rc for 
                select t.*  
                from my_table t 
                where t.my_number = 1; 
            fetch rc bulk collect into recs; 
        end if; 
        close rc; 
        open p_rs for  
             select * from table(recs); 
    end  get_param; 
     
end; 

select * from all_errors

declare 
    rc test1.cur1; 
    rec my_table%rowtype; 
begin 
    test1.get_param(42, rc); 
    fetch rc into rec; 
    dbms_output.put_line('result='||rec.description); 
    close rc; 
end;