Closed Cursors - SQL Best Practice

Question

Hi all,

I work in a large, separated development team. The project I'm currently working on has an Oracle DB team that develops the stored procedures and other related components that our J2EE web-tier talks to.

One thing that's arisen from development is the concept of a closed cursor when results aren't found.

From my experience, closed cursors signifies an exceptional circumstance; a programming error that isn't necessarily data related. In the current context it is to signify that no data has been found, when an empty result set/cursor would make more sense to me.

What do people think or have experience with from their perspective? Any Oracle SQL developers here that could shed the light should I be wrong from a conventional perspective?

Best practices?

Cheers!

Answer 1

I'm with you. Returning an empty resultset makes the most sense to me.

It is all about separation of concerns. Data retrieval is a service. Whereas handling NO_DATA_FOUND exceptions belongs to the calling application.

edit

I would expect to find cursor%NOTFOUND in a PL/SQL procedure which processes a ref cursor. For instance, a PAYROLL routine might make use of a function in the SALES subsystem which returns a ref cursor of all the orders taken by salesmen (in a given department, for a given quarter, whatever).

I would expect the PAYROLL routine to cycle through the returned result set and check for cursor%NOTFOUND. I would not expect the SALES function to do that and return an empty cursor if there are not no matching salesmen. Apart from violating the Principle of Least Surprise, it also means either the retrieving function is doing more work (opening the ref cursor twice) or it is returning the wrong results.

SQL> create function get_emps(dno number) return sys_refcursor is
  2    rc sys_refcursor;
  3  begin
  4    open rc for select * from emp where deptno = dno;
  5    return rc;
  6  end;
  7  /

Function created.

SQL> var rc refcursor
SQL>
SQL> exec :rc := get_emps(10)

PL/SQL procedure successfully completed.

SQL> print rc

EMPNO ENAME      JOB              MGR HIREDATE    SAL COMM DEPTNO
----- ---------- --------- ---------- ---------- ---- ---- ------
7782 BOEHMER     MANAGER         7839 09-06-1981 2450          10
7839 SCHNEIDER   PRESIDENT            17-11-1981 5000          10
7934 KISHORE     CLERK           7782 23-01-1982 1300          10

SQL>
SQL> create or replace function get_emps(dno number) return sys_refcursor is
  2    rc sys_refcursor;
  3    lrow emp%rowtype;
  4  begin
  5    open rc for select * from emp where deptno = dno;
  6    fetch rc into lrow;
  7    if rc%notfound then
  8      close rc;
  9    end if;
 10    return rc;
 11  end;
 12  /

Function created.

SQL> exec :rc := get_emps(15)

PL/SQL procedure successfully completed.

SQL> print rc
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "rc"
SQL> exec :rc := get_emps(10)

PL/SQL procedure successfully completed.

SQL> print rc

EMPNO ENAME      JOB              MGR HIREDATE    SAL COMM DEPTNO
----- ---------- --------- ---------- ---------- ---- ---- ------
7839  SCHNEIDER  PRESIDENT            17-11-1981 5000          10
7934  KISHORE    CLERK           7782 23-01-1982 1300          10

SQL>