Advanced Oracle PL/SQL Programming with Packages

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 ...