PLSQL using cursors or functions to query repeated data Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)?
Whats the difference?
What are the best practices?
What are the other considerations?
Or is there another better way?

1) using a package cursor

cursor getLicenseStatus_cur (in_license_no varchar2) is
    SELECT status, status_dt from tbl_license where licence_no=in_license_no;

--use:
OPEN getLicenseStatus_cur('123');
fetch getLicenseStatus_cur into l_status, l_status_dt;
EXIT WHEN sql%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);
close getLicenseStatus_cur('123');


2) or using a function and passing a rowtype

create or replace function getLicenseStatus(in_license_no varchar2(10))
RETURN tbl_license%ROWTYPE
    as
    output_rec tbl_license%ROWTYPE;
begin
    SELECT * into output_rec 
    from tbl_license where licence_no=in_license_no;
    return output_rec;
end;

--use
lic_rec users%ROWTYPE;
lic_rec := getLicenseStatus('123');
DBMS_OUTPUT.PUT_LINE('l_status= ' || lic_rec.status || ', l_status_dt= ' || lic_rec .status_dt);


3) or using a function and passing a sys refcursor

create or replace function getLicenseStatus(in_license_no varchar2(10)) 
return sys_refcursor as
    v_curs sys_refcursor;
begin
    open v_curs for SELECT status, status_dt from tbl_license where licence_no=in_license_no;
    return v_curs;
end;

--use:
v_rc := getLicenseStatus('123'); 
fetch v_rc into l_status , l_status_dt;
exit when sql%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('l_status= ' || l_status || ', l_status_dt= ' || l_status_dt);
close v_rc ;
This is a 4th option, using out parameters?

create or replace function getLicenseStatus
    (in_license_no varchar2, l_status out varchar2, l_status_dt out varchar2)
    as
begin
    SELECT status, status_dt 
       into into l_status, l_status_dt 
    from tbl_license 
    where licence_no=in_license_no;
end;