Public / private cursors

As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic.

However, not many are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component.

Cursors can also be defined in the same way as the following example shows:




CREATE PACKAGE my_pkg AS
   CURSOR c_recent_hires RETURN emp%ROWTYPE;
   ...
END emp_stuff;
/

CREATE PACKAGE BODY my_pkg AS
   CURSOR c_recent_hires RETURN emp%ROWTYPE IS
      SELECT * FROM emp 
      WHERE hiredate > sysdate-30;
   ...
END emp_stuff;
/


5 thoughts on “Public / private cursors

  1. I’ve been using PL/SQL since Oracle 6 (yes, before there were stored procedures) and I never knew that. Thanks. Now excuse me while I move some cursor definitions into the package body.

  2. I have defined cursors globally in the package body declaratives. But what is gained by exposing it publicly in the package specification?

  3. Hi Connor,

    I know this is almost a year old but how what if this cursor’s row values weren’t in any particular construct, i.e. they joined attributes from two or more tables? I know I could create my own PL/SQL record type on a package to hold these values and use that same type on the return but is there a way of doing this more “dynamically” without creating a multitude of types?
    I currently have some packages that provide only some cursors that are shared among other packages. These cursors generally have all attributes of a single table but I add a final column with total rows found:

    SELECT funny_table.*, COUNT (funny_table.id) OVER () total_rows_found
    FROM funny_table
    WHERE select_star_only = for_examples
    AND never_for = production;

    These queries’ purpose is to serve data to some front-end grids hence the need of knowing total rows besides the paging that I did not put in the example.

    Any suggestion on how to do this? Or any general suggestion on how one should write a query with paging + offset + total rows of the set?

  4. Couple of things here

    1) in terms of a type, one possible mechanism would be to create a view so that you have a %rowtype definition to work with. Note – I’m not saying put your *analytic* function in the view (because that gives a potentially different result). You could do something like: create view V as select f.*, 0 tot from funny_table, and then use v%rowtype;

    2) Showing total number of rows (where the number could be *anything*) is a bad bad idea… If you *know* that the resultset is of limited size (say less than 1000 rows), then that might be fine – but if you *know* that in advance, there’s an argument for fetching them all and paging at the client level. If you *dont* know the size…then going ahead and counting them all is a performance headache waiting to happen.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s