Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 11.2 ALL_OBJECTS ViewChapter 11
PLVobj: A Packaged Interface to ALL_OBJECTS
Next: 11.4 Accessing ALL_OBJECTS

11.3 Setting the Current Object

PLVobj provides a number of different programs to set and change the current object of PLVobj. The current object of PLVobj is defined by three private package variables:


The owner of the object(s).


The name of the object(s). The name can be wildcarded, by including % in the name specified.


The type of the object(s). The type can be wildcarded as well, again by including % in the type specified.

Since the above elements are private variables, a user of PLVobj will never see or reference these variables directly. Instead, I provide the following set of procedures and functions to maintain these variables (I call this layer of code which surrounds variables "get and set" routines): setcurr, set_name, set_type, and set_schema.

The setcurr program calls the other set programs. Its header is:

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

The first argument is the module name, the second, optional argument is the module type. While the first argument is called a "name" argument, you can actually in this single argument supply the name, type, and schema. PLVobj makes it as easy as possible to supply this information. All of the following formats for the name_in argument are acceptable:


An unqualified identifier. In this case, PLVobj determines the type of the object from the data dictionary. This type will be unambiguous if name refers to a procedure or function. If a package, then the type could be either PACKAGE or PACKAGE BODY. PLVobj defaults to PACKAGE.

The name of the object is qualified by the schema name. You will need to specify the schema name if you want to convert the case of a program owned by another user.


Both the type and the name are specified, separated by a colon. Valid type strings for each type of program unit are shown below.

In this case, the user has specified all three elements of a program unit: the type of the program, the owner, and the name.

In fact, the second argument is optional because PLVobj allows you to concatenate the type onto the name argument.

The following table shows different ways of specifying programs and the resulting PLVobj current object values. In this table, pkg is the name of a package, func the name of a function, and proc the name of a procedure. The setcurr program is executed from the PLV account.

Call to setcurr


Program Type

Program Name

PLVobj.setcurr ('proc');



PLVobj.setcurr ('func');



PLVobj.setcurr ('pkg');



PLVobj.setcurr ('b:pkg');



PLVobj.setcurr ('body:pkg');



PLVobj.setcurr ('s:SCOTT.empmaint');



PLVobj.setcurr ('%:plv%');



Like PLV%
PLVobj.setcurr ('s:scott.% ');



All present

The above table assumes, by the way, that the PLV account has execute authority on SCOTT's empmaint package.

Notice that when I specify a function or procedure, I do not have to provide the type at all. There can only be one object of a given name in a schema, and the object_type is therefore unambiguously set to PROCEDURE. On the other hand, when I am working with packages, the situation is more ambiguous. A package can have up to two objects: the specification and the body. So if you provide a package name but do not supply a type, PLVobj will set the current type to PACKAGE. If you want to set the current object to a package body, you must supply a valid object type or object type abbreviation.

Here are the valid options for object type:

Program Type

Valid Entries for Type in Call to setcurr

Package Specification


Package Body






So I can set the current object to the PACKAGE BODY of the testcase package with any of the following calls to setcurr:

PLVobj.setcurr ('b:testcase');
PLVobj.setcurr ('pb:testcase');
PLVobj.setcurr ('body:testcase');
PLVobj.setcurr ('package body:testcase');

NOTE: The setcurr program relies on DBMS_UTILITY.NAME_RESOLVE to uncover all the components of a non-wildcarded object entry. This builtin only returns non-NULL values for PL/SQL stored code elements. Consequently, you cannot use setcurr to set the current object to non-PL/SQL elements such as tables and indexes. Instead, you will need to call the individual set programs explored in the next section.

You can use the PLVobj.setcurr program to convert your entry and set the current object accordingly. In some cases, however, you may want simply to change the current object type. Or you may want to take advantage of the parsing and conversion algorithms of PLVobj without actually changing the current object. To provide this flexibility, PLVobj offers a number of additional programs which are explained below.

11.3.1 Setting Individual Elements of Current Object

You can change the current schema, name, or type independently with the set programs. You can also retrieve the current object values with corresponding functions. The "get and set" programs are shown below:

PROCEDURE set_schema (schema_in IN VARCHAR2 := USER);
PROCEDURE set_type (type_in IN VARCHAR2);

PROCEDURE set_name (name_in IN VARCHAR2);

I use these programs in the PLVvu.err procedure, which displays compile errors for the specified program. The main body of err is shown below:

PLVobj.setcurr (name_in);
IF PLVobj.currtype = PLVobj.c_package AND
   INSTR (name_in, ':') = 0
   /* Show errors for package spec, then body. */
   PLVobj.set_type (PLVobj.c_package_body);
   show_errors (TRUE);
   show_errors (TRUE);

Translation: I call the setcurr procedure to set the current object. Then I call currtype to see if the program type is PACKAGE. If it is, I need to check to see if the developer has requested to see errors for just the package specification or both specification and body. If both were requested (there is no colon in the name, therefore no type specified), then I show errors for the specification, explicitly set the type to PACKAGE BODY -- overriding the existing value -- and then show errors for the body.

11.3.2 Converting the Program Name

All the logic to convert a string into the separate components of schema, name, and type are handled by the convobj program, whose header is shown below:

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

All three arguments of convobj are IN OUT parameters, so that you can provide a value and also have a value sent back for each of the components of an object.

The logic inside convobj is complex. If the name or type passed in to convobj contains a wildcard, those wildcarded strings are returned (in their separate components) by the procedure. If, on the other hand, no wildcards are present, convobj relies on the DBMS_UTILITY.NAME_RESOLVE builtin procedure to automatically resolve the program name into its individual components.

NAME_RESOLVE resolves the specified object name into the owner or schema, first name, second name (if in a package), program type, and database link if any. The program type is one of the following numbers:









NAME_RESOLVE is so useful because it automatically determines the appropriate schema of the named element you pass in. Notice that NAME_RESOLVE does not distinguish between a package body and its specification (they both have the same name, so that would be something of a challenge). The nameres.sql script in the plvision\use subdirectory provides an easy way to call and see the results from DBMS_UTILITY.NAME_RESOLVE.

You can call convobj when you want to convert a name to its different components without changing the current object in the PLVobj package. I do this, for example, in the setcase.sql script, which provides an easy-to-use frontend to PLVcase for case conversion of your code. I call PLVobj.convobj even before I call any PLVcase programs because I want to display the program you have just requested for conversion. This code is shown below:

PLVobj.convobj (modname, modtype, modschema);
modstring := modtype || ' ' || modname;
p.l ('=========================');
p.l ('PL/Vision Case Conversion');
p.l ('=========================');
p.l ('Converting ' || modstring || '..');

The PLVobj package also offers a convert_type procedure, which encapsulates the logic by which it converts any number of different abbreviations and strings to a valid object type for the ALL_OBJECTS view. The header for convert_type is as follows:

PROCEDURE convert_type (type_inout IN OUT VARCHAR2);

You pass it a string and it changes that screen to a valid type.

11.3.3 Displaying the Current Object

PLVobj provides the showcurr procedure to display the current object. Its header is:

PROCEDURE showcurr (show_header_in IN BOOLEAN := TRUE);

You can display the current object with a header (the default) or without, in which case you will simply see the schema, name, and type (note that this full name is constructed with a call to the fullname function, also available for your use). Some examples follow:

SQL> exec PLVobj.set_name ('PLVctlg');
SQL> exec PLVobj.set_type ('table);
SQL> exec PLVobj.showcurr; -- Displays a header
SQL> exec PLVobj.setcurr ('PLVio');
SQL> exec PLVobj.showcurr (FALSE); -- Suppresses header

The showobj1.sql SQL*Plus script uses showcurr to display all the objects specified by your input. This script is described below in the section called "Accessing ALL_OBJECTS."

11.3.4 Saving and Restoring the Current Object

PLVobj provides programs to both save the current object and restore it from the last save. The headers for these programs are shown below:

PROCEDURE savecurr;
PROCEDURE restcurr;

You will want to use these programs if you are using PLVobj (in particular, the current object of PLVobj) more than once in a given program execution. Suppose, for example, that you have nested loops. In the outer loop, you call PLVobj.setcurr to scan through a set of program units. Inside the inner loop, you need to use PLVobj.setcurr to change the focus of activity to another object. When you are done with the inner loop execution, however, you will want to set the current object back to the outer loop values.

PL/Vision runs into this scenario because of the extensive work manipulating PL/SQL code objects.

Previous: 11.2 ALL_OBJECTS ViewAdvanced Oracle PL/SQL Programming with PackagesNext: 11.4 Accessing ALL_OBJECTS
11.2 ALL_OBJECTS ViewBook Index11.4 Accessing ALL_OBJECTS

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