Working with packaged cursors

Now let's see how we can take advantage of packaged cursors. First of all, you do not need to learn any new syntax to open, fetch from, and close packaged cursors; you just have to remember to prepend the package name to the name of the cursor. So if I want to get information about all the books having to do with PL/SQL, I can write a block like this:

DECLARE onebook book_info.bytitle_cur%ROWTYPE;BEGIN OPEN book_info.bytitle_cur ('%PL/SQL%'); LOOP EXIT WHEN book_info.bytitle_cur%NOTFOUND; FETCH book_info.bytitle_cur INTO onebook; book_info.display (onebook); END LOOP; CLOSE book_info.bytitle_cur;END;

As you can see, I can %ROWTYPE a packaged cursor and check its attributes just as I would with a locally defined explicit cursor. Nothing new there!

There are some hidden issues lurking in this code, however. Because my cursor is declared in a package specification, its scope is not bound to any given PL/SQL block. Suppose that I run this code:

BEGIN -- Only open... OPEN book_info.bytitle_cur ('%PEACE%');END;

and then, in the same session, I run the anonymous block with the LOOP shown above. I will then get this error:

ORA-06511: PL/SQL: cursor already open

This happened because in my "only open" block, I neglected to close the cursor. Even though the block terminated, my packaged cursor did not close.

Given the persistence of packaged cursors, you should always keep the following rules in mind:

· Never assume that a packaged cursor is closed (and ready to be opened).

· Never assume that a packaged cursor is opened (and ready to be closed).

· Always be sure to explicitly close your packaged cursor when you are done with it.

These three rules also apply to working with other kinds of cursors—such as locally defined explicit cursors and DBMS_SQL cursors—but they are absolutely crucial for packaged cursors.

 

If you neglect these rules, you might well execute an application that makes certain assumptions, and then pays the price in unexpected and unhandled exceptions. So the question then becomes: how best can you remember and follow these rules? My suggestion is to buildprocedures that perform the open and close operations for you—and take all these nuances and possibilities into account.

The following package offers an example of this technique.

/* File on web: openclose.sql */CREATE OR REPLACE PACKAGE personnelIS CURSOR emps_for_dept (deptno_in IN employee.department_id%TYPE) IS SELECT * FROM employee WHERE department_id = deptno_in; PROCEDURE open_emps_for_dept(deptno_in IN employee.department_id%TYPE, close_if_open IN BOOLEAN:= TRUE); PROCEDURE close_emps_for_dept; END personnel;

I have a packaged cursor along with procedures to open and close the cursor. So if I want to loop through all the rows in the cursor, I would write code like this:

DECLARE one_emp personnel.emps_for_dept%ROWTYPE;BEGIN personnel.open_emps_for_dept (1055); LOOP EXIT WHEN personnel.emps_for_dept%NOTFOUND; FETCH personnel.emps_for_dept INTO one_emp;... END LOOP; personnel.close_emps_for_dept;END;

I don't use explicit OPEN and CLOSE statements; instead, I call the corresponding procedures, which handle complexities related to packaged cursor persistence. I urge you to examine the openclose.sql file available from the O'Reilly site to study the implementation of these procedures.

You have a lot to gain by creating cursors in packages and making those cursors available to the developers on a project. Crafting precisely the data structures you need for your application is hard and careful work. These same structures—and the data in them—are used in your PL/SQL programs, almost always through the use of a cursor. If you do not package up your cursors and provide them "free of charge and effort" to all developers, each of them will write his or her own variations of these cursors, leading to all sorts of performance and maintenance issues. Packaging cursors is just one example of using packages to encapsulate access to data structures, which is explored further in Section 17.5.

One of the technical reviewers of this book, JT Thomas, offers the following alternative perspective: "Rather than working with packaged cursors, you can get exactly the same effect by encapsulating logic and data presentation into views and publishing these to the developers. This allows the developers to then be responsible for properly maintaining their own cursors; the idea is that it is not possible to enforce proper maintenance given the toolset available with publicly accessible package cursors. Specifically, as far as I know, there is no way to enforce the usage of the open/close procedures, but the cursors will always remain visible to the developer directly opening/closing it; thus, this construct is still vulnerable. To make matters worse, however, the acceptance of publicly accessible packaged cursors and the open/close procedures might lull a team into a false sense of security and reliability."