Strongly typed cursor variables

Arguably the easiest way to return a set of data back to a client application from PL/SQL is via a cursor variable. Oracle simply opens a cursor to a query and the application pulls data from the cursor on demand. Conveniently Oracle provides a generic SYS_REFCURSOR data type that can be used for any cursor variable, as shown below to retrieve employee details:

CREATE OR REPLACE PACKAGE pkg_emp
AS
   PROCEDURE get_emps (p_emp_cur OUT SYS_REFCURSOR);
END pkg_emp;
/
   
CREATE OR REPLACE PACKAGE BODY pkg_emp
AS

PROCEDURE get_emps (p_emp_cur OUT SYS_REFCURSOR)
AS
BEGIN
   OPEN p_emp_cur
   FOR
   SELECT id
   ,      first_name
   ,      last_name
   ,      dept_id
   FROM   emp;
END get_emps;

END pkg_emp;
/

While the above is very quick to code it suffers from a significant flaw; an ill-defined contract between the provider of the data (the database via the PL/SQL routine) and the consumer of the data (whatever application is calling the routine). The key problem here is that the SYS_REFCURSOR type can be used with any query. In this regard it’s like having an “any data” type in an application programming language.

If I were to modify the query inside the get_emps routine to return the department name then I could simply compile a new version as:

CREATE OR REPLACE PACKAGE BODY pkg_emp
AS

   PROCEDURE get_emps (p_emp_cur OUT SYS_REFCURSOR)
   AS
   BEGIN
      OPEN p_emp_cur
      FOR
      SELECT e.id
      ,      e.first_name
      ,      e.last_name
      ,      e.dept_id
      ,      d.dept_name
      FROM   emp e
      ,      dept d
      WHERE  d.id = e.dept_id;
   END get_emps;

END pkg_emp;
/

All done, right? Well, although this code will compile and run correctly I have probably inadvertently broken any application that was using it. The code does nothing to enforce the structure of the cursor that I am returning so any query is deemed acceptable. From Oracle’s perspective this is fine but it won’t be okay to the consumers of the cursor, which will be expecting a cursor of a particular structure.

A better way to code the above routine is to use a strongly typed cursor, which declares in advance the attributes and data types that the cursor will contain. If the cursor is opened using a query that does not match the definition then a compile time error is thrown. Sure, the definition of the strongly type cursor could be altered to match the new query but the error acts as a warning to the database developer that they might be about to break the application.

So, how do we create a strongly typed cursor variable? My preferred approach is to use a record type defined in the package specification as shown below:

CREATE OR REPLACE PACKAGE pkg_emp
AS
   TYPE t_emp_rec IS RECORD (id         emp.id%TYPE
                            ,first_name emp.first_name%TYPE
                            ,last_name  emp.last_name%TYPE
                            ,dept_id    emp.dept_id%TYPE);
   TYPE t_emp_cur IS REF CURSOR RETURN t_emp_rec;                          

   PROCEDURE get_emps (p_emp_cur OUT t_emp_cur);
END pkg_emp;
/
   
CREATE OR REPLACE PACKAGE BODY pkg_emp
AS

PROCEDURE get_emps (p_emp_cur OUT t_emp_cur)
AS
BEGIN
   OPEN p_emp_cur
   FOR
   SELECT id
   ,      first_name
   ,      last_name
   ,      dept_id
   FROM   emp;
END get_emps;

END pkg_emp;
/

The record type is useful for “self documenting” the code as shows what data will be returned by the cursor without having to go into the package body and read the query. It’s a little bit of extra typing but now when I try to modify the package body and replace DEPT_ID with DEPT_NAME I get the following compilation error:

CREATE OR REPLACE PACKAGE BODY pkg_emp
AS

   PROCEDURE get_emps (p_emp_cur OUT t_emp_cur)
   AS
   BEGIN
      OPEN p_emp_cur
      FOR
      SELECT e.id
      ,      e.first_name
      ,      e.last_name
      ,      e.dept_id
      ,      d.dept_name
      FROM   emp e
      ,      dept d
      WHERE  d.id = e.dept_id;
   END get_emps;

END pkg_emp;
/

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY PKG_EMP:

LINE/COL ERROR
-------- ---------------------------------------------
7/7      PL/SQL: SQL Statement ignored
9/7      PLS-00382: expression is of wrong type

The “expression is of wrong type” is telling me that my query no longer matches the record declaration in the package specification. Time for me to revise my code… or change the record type and go searching for the application code that calls this routine and ensure it will continue to work with the new cursor. In a large, complex multi-tier system additional checks and balances like strongly typed cursors are invaluable to prevent inadvertent mistakes.

Advertisement