Public / private cursors

February 12, 2016

1 minute

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

8 responses to “Public / private cursors”

John Flack

February 19, 2016 at 9:09 pm

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.

Reply
pnosko

January 9, 2017 at 3:06 am

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

Reply
1. connormcdonald
  
  January 18, 2017 at 8:03 am
  
  Its rare, but its possible you might want to share the cursor across several packages.
  
  Reply
João Borges Barreto

January 18, 2017 at 7:30 am

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?

Reply
connormcdonald

January 18, 2017 at 8:12 am

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.

Reply
João Borges Barreto

January 23, 2017 at 7:50 am

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

Reply
Steven Feuerstein

September 26, 2018 at 6:30 pm

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.

Reply
Steven Feuerstein

September 27, 2018 at 1:14 am

Just published a post about the topic I mentioned above. Hope it helps!

http://stevenfeuersteinonplsql.blogspot.com/2018/09/packaged-cursors-equal-global-cursors.html

Reply