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


Advertisements

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

  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

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