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 ;