Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 5.19 PLVmsg: Message HandlingChapter 5
PL/Vision Package Specifications
Next: 5.21 PLVprs: String Parsing

5.20 PLVobj: Object Interface

The PLVobj (PL/Vision OBJect) package provides a programmatic interface to the PL/SQL objects stored in the ALL_OBJECTS data dictionary view. See Chapter 20 for details.

5.20.1 Tracing PLVobj activity

PROCEDURE display;

Turns on display of information about activity occurring in PLVobj.

PROCEDURE nodisplay;

Turns off display of PLVobj activity.


Returns TRUE if showing activity in PLVobj.

5.20.2 General constants and exceptions

no_name_specified EXCEPTION;

Exception raised when you try to perform an operation but have not specified the name of the object (the "current object" has not been set).

c_pkg_spec CONSTANT VARCHAR2(1) := 'S';
c_pkg_body CONSTANT VARCHAR2(1) := 'B';
c_entire_pkg CONSTANT VARCHAR2(2) := 'SB';
c_proc CONSTANT VARCHAR2(2) := 'P';
c_func CONSTANT VARCHAR2(2) := 'F';

Names for the different types of program units. You can use these in calls to set_type or simply pass the literal values as part of a single type-name string.

c_procedure CONSTANT VARCHAR2(30) := 'PROCEDURE';
c_function CONSTANT VARCHAR2(30) := 'FUNCTION';
c_synonym CONSTANT VARCHAR2(30) := 'SYNONYM';
c_package CONSTANT VARCHAR2(30) := 'PACKAGE';
c_package_body CONSTANT VARCHAR2(30) := 'PACKAGE BODY';

Full names of program unit types as they are found in ALL_OBJECTS.

5.20.3 Setting the current object

(name_in IN VARCHAR2, type_in IN VARCHAR2 := NULL);

Sets the current object for other PLVobj modules. When you call setcurr, you set the schema, name, and type for the object. You can also call the individual programs listed below to set a single part of the current object.

PROCEDURE set_schema (schema_in IN VARCHAR2 := USER);

Sets the schema for the current object.

PROCEDURE set_type (type_in IN VARCHAR2);

Sets the type for the current object.

PROCEDURE set_name (name_in IN VARCHAR2);

Sets the name for the current object.

5.20.4 Accessing the current object


Returns the name of the current object.


Returns the type of the current object.


Returns the schema of the current object.


Returns the full name of the current object (the different elements concatenated together).

PROCEDURE showcurr (show_header_in IN BOOLEAN := TRUE);

Displays the full name of the current object.

5.20.5 Interfacing with the PLVobj cursor

PROCEDURE open_objects;

Opens the PLVobj cursor for the current object settings.

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

Two overloaded versions to fetch the next row from the PLVobj cursor. The first version fetches the next object into the current object. The second version allows you to fetch the next object into local variables, leaving the current object unchanged.


Returns TRUE if the last fetch from the PLVobj cursor returned a record.

PROCEDURE close_objects;

Closes the PLVobj cursor.

5.20.6 Programmatic cursor FOR loop elements

PROCEDURE loopexec
   (module_in IN VARCHAR2,
    exec_in IN VARCHAR2 := c_show_object,
    placeholder_in IN VARCHAR2 := c_leph,
    name_format_in IN VARCHAR2 := c_modspec);

The loopexec procedure simulates a cursor FOR loop through a programmatic interface using dynamic PL/SQL. You can modify the behavior of loopexec through the use of the following constants.

c_leph CONSTANT VARCHAR2(10) := ':rowobj';

The default Loop Exec PlaceHolder string.

c_show_object CONSTANT VARCHAR2(100) := 'p.l (:rowobj)';

The default action for loopexec, which is to display the set of objects that are fetched by the cursor.

c_modspec CONSTANT VARCHAR2(1) := 'S';
c_modname CONSTANT VARCHAR2(1) := 'N';

Named constants for the two different formats for object names manipulated by loopexec: S for module specification and N for module name.

v_letab PLVtab.vc2000_table;
v_lerowind INTEGER;

The PL/SQL table and row count variable used to store all the objects retrieved by the programmatic cursor FOR loop, loopexec.

5.20.7 Saving and restoring PLVobj settings

PROCEDURE savecurr;

Saves the current object to private variables so that it can be restored.

PROCEDURE restcurr;

Restore the current object from the saved setting.

5.20.8 Miscellaneous PLVobj programs

PROCEDURE vu2pstab
(module_in IN VARCHAR2,
table_out OUT PLVtab.vc2000_table,
num_objects_inout IN OUT INTEGER);

Copies the set of objects identified by the PLVobj cursor to a PL/SQL table.

(name_inout IN OUT VARCHAR2,
type_inout IN OUT VARCHAR2,
schema_inout IN OUT VARCHAR2);

Converts a single object string (which can have a complex format such as

(cur_in IN INTEGER,
name_col_in IN VARCHAR2 := 'name',
type_col_in IN VARCHAR2 := 'type',
schema_col_in IN VARCHAR2 := NULL);

Encapsulates calls to DBMS_SQL.BIND_VARIABLE to allow binding of the different elements of the current object into the specified cursor. You can bind all three elements as a subset; binding will only occur for those arguments that have non-NULL values.

PROCEDURE convert_type (type_inout IN OUT VARCHAR2);

Converts a variety of abbreviations for program unit types into the strings employed in the ALL_OBJECTS data dictionary view. The string "BODY", for example, is converted to the full "PACKAGE BODY".

Previous: 5.19 PLVmsg: Message HandlingAdvanced Oracle PL/SQL Programming with PackagesNext: 5.21 PLVprs: String Parsing
5.19 PLVmsg: Message HandlingBook Index5.21 PLVprs: String Parsing

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference