Oracle PL/SQL Programming: A Developer's Workbook

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