2

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 ;
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.