#

| Chapter 11 PLVobj: A Packaged Interface to 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:

`v_currschema`

  The owner of the object(s).

`v_currname`

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

`v_currtype`

  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:

>     PROCEDURE setcurr
>        (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:

`name`

  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.

`schema.name`

  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.

`type:name`

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

`type:schema.name`

  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 | Schema | Program Type | Program Name
--- | --- | --- | ---
>     PLVobj.setcurr ('proc');
> | PLV | PROCEDURE |
>     proc
>     PLVobj.setcurr ('func');
> | PLV | FUNCTION |
>     func
>     PLVobj.setcurr ('pkg');
> | PLV | PACKAGE |
>     pkg
>     PLVobj.setcurr ('b:pkg');
> | PLV | PACKAGE BODY |
>     pkg
>     PLVobj.setcurr ('body:pkg');
> | PLV | PACKAGE BODY |
>     pkg
>     PLVobj.setcurr ('s:SCOTT.empmaint');
> | SCOTT | PACKAGE |
>     empmaint
>     PLVobj.setcurr ('%:plv%');
> | PLV | ALL TYPES |
>     Like PLV%
>     PLVobj.setcurr ('s:scott.% ');
> | SCOTT | PACKAGE |
>     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 | `S PS SPEC` or `SPECIFICATION`
Package Body | `B PB BODY` or `PACKAGE BODY`
Procedure | `P PROC` or `PROCEDURE`
Function | `F FUNC` or `FUNCTION`

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);
>     FUNCTION currschema RETURN VARCHAR2;
>     PROCEDURE set_type (type_in IN VARCHAR2);
>     FUNCTION currtype RETURN VARCHAR2;

>     PROCEDURE set_name (name_in IN VARCHAR2);
>     FUNCTION currname RETURN 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
>     THEN
>        /* Show errors for package spec, then body. */
>        show_errors;
>        PLVobj.set_type (PLVobj.c_package_body);
>        show_errors (TRUE);
>     ELSE
>        show_errors (TRUE);
>     END IF;

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:

>     PROCEDURE convobj
>        (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:

5

  (synonym)

7

  (procedure)

8

  (function)

9

  (package)

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
>     Schema.Name.Type
>     -----------------------------------------
>     PLV.PLVCTLG.TABLE
>     SQL> exec PLVobj.setcurr ('PLVio');
>     SQL> exec PLVobj.showcurr (FALSE); -- Suppresses header
>     PLV.PLVIO.PACKAGE

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.

11.2 ALL_OBJECTS View |  | 11.4 Accessing ALL_OBJECTS

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