#

| Chapter 11 PLVobj: A Packaged Interface to ALL_OBJECTS |

## 11.5 Binding Objects to a Dynamic Cursor

PLVobj provides the `bindobj` procedure to make it easier for you to utilize the PLVobj current object inside dynamic SQL. This program can be used when you have placed bind variables in your dynamically constructed cursor that correspond to one or more of the elements of the current object.

The header for `bindobj` is as follows:

>     PROCEDURE bindobj
>        (cur_in IN INTEGER,
>         name_col_in IN VARCHAR2 := 'name',
>         type_col_in IN VARCHAR2 := 'type',
>         schema_col_in IN VARCHAR2 := 'owner');

The first, and only required, argument is the handle to the DBMS_SQL cursor handle. The other parameters provide the strings which are the placeholders in the string that was parsed for that cursor handle. The default values for these placeholders correspond to the names of the columns in the ALL_SOURCE data dictionary view.

### 11.5.1 Specifying Which Binds Occur

The `bindobj` procedure will only call BIND_VARIABLE for those placeholders for which a non-NULL column name is provided. For example, in the following call to `bindobj`, BIND_VARIABLE will only be executed for the name placeholder.

>     PLVobj.bindobj (cur_handle, 'objname', NULL, NULL);

Notice that since the default values for these column names are not NULL, you must explicitly pass a NULL value in to `bindobj` in order to turn off a binding for that placeholder (if you do not, DBMS_SQL will raise an exception). If you only want to turn off one of the trailing bind operations (such as for the schema), while leaving the earlier column names with their defaults, you can use named notation to specify an override for just that column as shown below:

>     PLVobj.bindobj (cur_handle, schema_col_in => NULL);

### 11.5.2 Using bindobj

The `bindobj` procedure comes in handy when you are using PLVobj to manage a current object, but you are not using PLVobj to query records from the ALL_OBJECTS view. You might, as does PL/Vision, want to read information from another data dictionary view that also contains object-related information, such as USER_SOURCE or USER_ERRORS.

I'll take you through a simple example of how to use `bindobj`. The script below (found in `inline.sql`) uses PLVobj and PLVdyn to display the line numbers of the stored source code which contains the specified string. With this script you answer such questions as: "How many (and which) lines of code in the PLVio package use the SUBSTR function?" Here, in fact, is the answer to that question:

>     SQL> start inline b:PLVprs SUBSTR
>     Lines with SUBSTR in PLV.PLVPRS.PACKAGE BODY
>     54
>     63
>     76
>     141
>     144
>     219
>     242
>     282
>     303
>     306
>     312
>     315
>     377

And here is the `inline.sql` script:

>     SET VERIFY OFF
>     DECLARE
>        v_sql VARCHAR2(2000) :=
>          'SELECT line FROM user_source ' ||
>          ' WHERE name = :name ' ||
>          '   AND type = :type ' ||
>          '   AND INSTR (text, ''&2'') > 0' ||
>          ' ORDER BY line';
>        v_line INTEGER;
>        cur INTEGER;
>     BEGIN
>        PLVobj.setcurr ('&1');

>        cur := PLVdyn.open_and_parse (v_sql);
>        DBMS_SQL.DEFINE_COLUMN (cur, 1, v_line);
>        PLVobj.bindobj (cur, schema_col_in => NULL);
>        PLVdyn.execute (cur);

>        p.l ('Lines with &2 in ' || PLVobj.fullname);
>        LOOP
>           EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
>           DBMS_SQL.COLUMN_VALUE (cur, 1, v_line);
>           p.l (v_line);
>        END LOOP;
>     END;
>     /

In the `inline.sql` script, I use `PLVobj.setcurr` to set the current object (passed in as the first argument to the script). I then perform several steps of dynamic SQL to open and parse the cursor and then define the single column for the SELECT statement. Before I can execute the cursor, I need to provide bind values for the `:name` and `:type` placeholders.

Since I have called `PLVobj.setcurr`, I can take advantage of the current object by calling the `bindobj` procedure. It automatically binds the name and type of the current object to my locally defined dynamic SQL statement. Since I am working with USER_SOURCE, I specify in my call to `bindobj` that I do not have any schema placeholder to be bound.

Following the bind, I execute and then loop through all the rows in the result set, displaying the line number.

### 11.5.3 Using bindobj in PL/Vision

The PLVio package contains a private procedure that makes use of `PLVobj.bindobj`. The `prepsrc` procedure prepares the source when it is a database table. This preparation phase involves calling the necessary dynamic SQL programs to define and execute a cursor against the table. Here is a simplified version of `prepsrc`:

>     PROCEDURE prepsrc (cur_in IN OUT INTEGER)
>     IS
>        v_namecol PLV.plsql_identifier%TYPE := srcrep.name_col;
>        v_typecol PLV.plsql_identifier%TYPE := srcrep.type_col;
>        v_schemacol PLV.plsql_identifier%TYPE := srcrep.schema_col;
>     BEGIN
>        cur_in := PLVdyn.open_and_parse (srcselect);

>       /* Check to see if placeholders need to be bound. */

>        IF INSTR (srcselect, ':' || v_namecol) = 0
>        THEN
>           v_namecol := NULL;
>        END IF;

>        IF INSTR (srcselect, ':' || v_typecol) = 0
>        THEN
>            v_typecol := NULL;
>         END IF;

>        IF INSTR (srcselect, ':' || v_schemacol) = 0
>        THEN
>            v_schemacol := NULL;
>         END IF;

>        PLVobj.bindobj (cur_in, v_namecol, v_typecol, v_schemacol);

>        PLVdyn.execute (cur_in);

>     END prepsrc;

Translation: Use the PLVdyn (PL/Vision DYNamic SQL) package to open and parse a select statement which has already been constructed (and is returned by the call to the function `srcselect`). Since the user of PLVio can modify the contents of the SELECT statement, I then use IF statements to check to see whether the standard name and type placeholders are in the dynamic SQL string. I use the INSTR builtin combined with the default column names to see if placeholders for name, type, or schema appear in the SELECT statement. If not, I set the corresponding column names to NULL.

Next, I call the `bindobj` procedure to bind this cursor for the current object (`PLVmod.currschema`, `PLVmod.currname`, and `PLVmod.currtype`), but only for those placeholders that are present. At the end of `prepsrc`, I execute the cursor using PLVdyn.

11.4 Accessing ALL_OBJECTS |  | 11.6 Populating a PL/SQL Table with Object Names

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