Skip to Main Content
Advanced Oracle PL/SQL Programming with Packages
book

Advanced Oracle PL/SQL Programming with Packages

by Steven Feuerstein
October 1996
Intermediate to advanced content levelIntermediate to advanced
687 pages
16h 41m
English
O'Reilly Media, Inc.
Content preview from Advanced Oracle PL/SQL Programming with Packages

11.4. Accessing ALL_OBJECTS

Once you have set the current object in PLVobj (with either a call to setcurr or calls to the individual set programs), you can open, fetch from, and close the PLVobj cursor.

11.4.1. Opening and Closing the PLVobj Cursor

To open the cursor, you call the open_objects procedure, defined as follows:

PROCEDURE open_objects;

This procedure first checks to see if the cursor is already open and, if not, takes that action. The implementation of open_objects is shown below:

PROCEDURE open_objects IS
   BEGIN
      IF obj_cur%ISOPEN
      THEN
         NULL;
      ELSE
         OPEN obj_cur;
      END IF;
   END;

When you are done fetching from the cursor, you may close it with the following procedure:

PROCEDURE close_objects;

whose implementation makes sure that the cursor is actually open before attempting to close the cursor:

PROCEDURE close_objects IS
   BEGIN
      IF obj_cur%ISOPEN
      THEN
         CLOSE obj_cur;
      END IF;
   END;

11.4.2. Fetching from the PLVobj Cursor

Once the cursor is open, you will usually want to fetch rows from the result set. You do this with the fetch_object procedure, which is overloaded as follows:

PROCEDURE fetch_object;
PROCEDURE fetch_object (name_out OUT VARCHAR2, type_out OUT VARCHAR2);

If you call fetch_objects without providing any OUT arguments, the name and type will be passed directly into the current object variables, v_currname and v_currtype.

If, on the other hand, you provide two return values in the call to fetch_object, the current object will remain unchanged and you will be ...

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
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin

Publisher Resources

ISBN: 1565922387Supplemental ContentCatalog PageErrata