Public / private cursors

Posted by

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:

   CURSOR c_recent_hires RETURN emp%ROWTYPE;
END emp_stuff;

   CURSOR c_recent_hires RETURN emp%ROWTYPE IS
      SELECT * FROM emp 
      WHERE hiredate > sysdate-30;
END emp_stuff;


  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 ( 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.

  5. Liked the first suggestion – must try it.
    I know counting all in the analytic is a bad idea and counting them in a separate query is even worse… We chose to always page at the database side for performance but mainly for consistency (keeping a record of which grid has paging where would be a huge headache). For most grids we do know that the resultset is really small (<100 rows) but we have no way of *knowing* that in advance all we do know is that they are going to grow both by "normal production" and server consolidations that will most certainly happen after we go live.

    Thanks again

  6. One other thing to keep in mind about package-based cursors is that they have global scope. After you open the cursor in a block, the PL/SQL runtime engine will not close the cursor when the block terminates.

Got some thoughts? Leave a comment

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.