O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Expert

Q:

15-22.

You get this unhandled exception:

ORA-06511: PL/SQL: cursor already open

When you declare a cursor in a package specification, it has global scope. One consequence is that you can open the cursor in one program, and it stays open even after that program terminates. And if you try to close a packaged cursor that has already been closed in another program, you get this error:

ORA-01001: invalid cursor

Q:

15-23.

Here is one possible solution to this requirement:


/* Filename on web site: onecur.pkg */
CREATE OR REPLACE PACKAGE onecur
IS
   CURSOR onerow (
      employee_id_in IN employee.employee_id%TYPE)
   IS
      SELECT * FROM employee
       WHERE employee_id = employee_id_in;

   PROCEDURE open_onerow(
      employee_id_in IN employee.employee_id%TYPE,
      close_if_open IN BOOLEAN := TRUE
      );

   PROCEDURE close_onerow;

END onecur;
/

Before moving to the package body, let’s review the procedures. The open procedure accepts the same parameters as the cursor, plus the close_if_open argument. If you pass TRUE (the default) for this argument, the program closes the cursor if it is already open. If you pass FALSE, it assumes that if the cursor is open, you just want to keep on fetching from the current location in the result set.

Here is the package body:

 /* Filename on web page: onecur.pkg */ CREATE OR REPLACE PACKAGE BODY ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required