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.