To open the cursor, you call the open_objects procedure, defined as follows:
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:
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;
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 able to do what you want with the fetched values. The call to fetch_object without arguments is, therefore, equivalent to:
PLVobj.fetch_object (v_name, v_type); PLVobj.setcurr (v_name, v_type);
FUNCTION more_objects RETURN BOOLEAN;
This function returns TRUE when the obj_cur is open and when obj_cur%FOUND returns TRUE. In all other cases, the function returns FALSE (including when the PLVobj cursor is not even open).
To see how all of these different cursor-oriented programs can be utilized, consider the following script (stored in showobj1.sql).
DECLARE first_one BOOLEAN := TRUE; BEGIN PLVobj.setcurr ('&1'); PLVobj.open_objects; LOOP PLVobj.fetch_object; EXIT WHEN NOT PLVobj.more_objects; PLVobj.showcurr (first_one); first_one := FALSE; END LOOP; PLVobj.close_objects; END; /
It sets the current object to the value passed in at the SQL*Plus command line. It then opens and fetches from the PLVobj cursor, exiting when more_objects returns FALSE. Finally, it closes the PLVobj cursor. This cursor close action is truly required. The PLVobj cursor is not declared in the scope of the anonymous block; instead, it is defined in the package body. After you open it, it will remain open for the duration of your session, unless you close it explicitly.
In the following example of a call to showobj1.sql, I ask to see all the package specifications in my account whose names start with "PLVC". I see that I have four packages.
SQL> start showobj1 s:PLVc% Schema.Name.Type PLV.PLVCASE.PACKAGE PLV.PLVCAT.PACKAGE PLV.PLVCHR.PACKAGE PLV.PLVCMT.PACKAGE
If you are not working in SQL*Plus, you can easily convert the showobj1.sql script into a procedure as follows:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.