Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page