[Chapter 11] 11.5 Binding Objects to a Dynamic Cursor Chapter 11 PLVobj: A Packaged Interface to ALL_OBJECTS 11.5 Binding Objects to a Dynamic CursorPLVobj 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 OccurThe 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 bindobjThe 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 377And 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/VisionThe 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.