Declaration of bind variables

A SQL statement, PL/SQL block or a stored procedure call may have placeholders which are usually connected with bind variables in the program. OTL 4.0 has a small parser that parses the SQL statement / PL/SQL block / stored procedure call and allocates the corresponding bind variables dynamically inside the stream.

In Oracle, the naming convention of placeholders is quite different from the one in ODBC/DB2-CLI. Oracle placeholders are names, prefixed with the colon, e.g. :f1, :supervisor_name, :employee_id. A placeholder may be referenced more than once in the same SQL statement.

In ODBC/DB2-CLI, placeholders are positional, presented as question marks, for example:

     INSERT INTO my_table values(?,?,?,?)

     INSERT INTO my_table values(:1<int>,:2<char[32]>,:3<double>,:4<char[128]>)

:<Number> gets translated into ?.

The Oracle traditional named notation for placeholders was extended with data type specifications, e.g.:

   INSERT INTO my_table2 values(:employee_id<int>,:supervisor_name<char[32]>)

The following data types for declaring extended placeholder are available in OTL 4.0:

              "begin "
              " my_pkg.my_proc(:f1<int,in>,:f2<int,in>, "
              "         :str1<char[100],out>, "
      // :str1 is an output string parameter
              "         :cur1<refcur,out[50]>, "
              "         :cur2<refcur,out[50]>); "
     // :cur1, :cur2 are a bind variable names, refcur -- their types, 
     // out -- output parameter, 50 -- the buffer size when this
     // reference cursor will be attached to otl_refcur_stream
              "end;"

For PL/SQL blocks (OTL 4.0/OCI7, OTL4.0/OCI8/9) or stored procedure calls (OTL 4.0/ODBC, OTL 4.0/DB2-CLI), special qualifiers are introduced to distinguish between input and output variables:

• in -- input variable
• out -- output variable
• inout -- input/output variable

 BEGIN
   :rc<int,out> := my_func(:salary<float,in>,  
                           :ID<int,inout>, 
                           :name<char[32],out>
                          );
 END;

New (OTL 4.0/ODBC, OTL 4.0/DB2-CLI) style

   {
    :rc<int,out> = call my_func(:salary<float,in>,  
                                :ID<int,inout>, 
                                :name<char[32],out>
                                )
   }

Old (OTL 2.0/ODBC) style:

   {
    :1<int,out> = call my_func(:2<float,in>,  
                               :3<int,inout>, 
                               :4<char[32],out>
                               )
   }

Example 1

  insert into tab1 values(:salary< double >, :name< char [ 32 ] > , :emp_id< int>);

Example 2

  :rc< int, out > := ...;

Embedding colon (":") literals into SQL statements in OTL/ODBC

Informix Call Level Interface (CLI) can be used in a combination with OTL/ODBC. Informix SQL allows colons (":") as legitimate characters in Informix SQL statements. Therefore, there is a need to embed colon literal into such SQL statements. The following notation can be used for embedding colons into SQL statements under OTL/ODBC: "\\:". OTL/ODBC 4.0.68 and higher supports the notation.

Explicit bind variables in output column definitions in SELECT statements

OTL describes output column names, data types, and column lengths dynamically right after the SELECT statement is parsed. OTL maps column internal data types into C++ external data types by default. The default mapping can be overridden by otl_stream::set_column_type() and otl_stream::set_all_column_types() function calls. However, it is not as convenient as defining explicit bind variables right in the text of the SQL statement.In OTL 4.0.73, a special kind of bind variables is introduced: explicit bind variables for defining data types of output columns in SELECT statements. The format for defining explicit bind variables in SELECT statement's columns is as follows: :#N<datatype>, where N is 1,2,3 (column's relative position in the SELECT statement)... SELECT output columns do not have a direct equivalent of :VAR (OCI), or ? (ODBC, DB2-CLI), so if an explicit bind variable is defined for a column in a SELECT statement, the bind variable has to be blanked out in the resulting format of the SELECT statement that gets passed into the database API. For example:

Here is the resulting SELECT statement that gets passed into the database API:

The set of datatypes allowed in :#N<datatype> is limited and database API specific, In general, the following types are allowed:

•  <char[XXX]>
•  <double>
•  <float>
•  <int>
•  <bigint> (which is supported natively in ODBC / DB2-CLI  / OCI11.2 or higher, and OTL internally emulates bigints via strings (char[XXX]) for the rest of the OCIs).
• <ubigint> (natively supported unsigned 64-bit ints for database APIs that native support for unsigned 64-bit ints, for example: Oracle 11.2, DB2)
  
•  <unsigned>
• <sdo_geometry>
  
•  <short>
•  <long>
• <raw[XXX]>
  
• <raw_long>
  
• <timestamp>
• <varchar_long>
  

Declaration of PL/SQL tables (OTL/OCIx)

In OTL 4.0/OCIx, in PL/SQL blocks, a PL/SQL table dimension can be added to the access qualifiers in/out/inout, e.g.:

 BEGIN
   my_pkg.my_proc(:salary<float,in[100]>,  
                  :ID<int,inout[200]>, 
                  :name<char[32],out[150]>
                 );
 END;

The size of the otl_stream with the definition in the example above needs to be set to 1, since stored procedures cannot be called in bulk. However, PL/SQL table type parameters are not scalars, they are vectors with maximum sizes, predefined in the definitions of the bind variables.

Starting with OTL 4.0.115 and on, the maximum PL/SQL table size is no longer limited to 32767. In order to turn the old limit of 32767 back on, use #define OTL_STREAM_LEGACY_BUFFER_SIZE_TYPE.

For more detail, see examples 49, 50, 51, 52.

Declaration of SQL tool neutral bind variables for Oracle

OTL 4.0.209 and higher supports an alternative bind variable notation that is neutral to SQL tools for Oracle. Neutral in this context means that the same SQL statements / PLSQL blocks can be executed both in the SQL tools and OTL. All OTL extended bind variable declarations are made SQL comments, for example:

	INSERT INTO test_tab VALUES(:f1/*int*/,:f2/*char[31]*/);

In general, the regular :var<...> syntax can be converted to :var/*...*/. The same rule of "no space characters in between" still applies. The rule was made in order to keep the OTL SQL statement parser as light as possible (short of writing a full-blown SQL parser). OTL also has SELECT column override syntax. The SELECT column override variables get replaced with spaces in the SQL statement, because they are not real placeholders. So, the SQL tool neutral syntax for the SELECT column override variables puts the variables into SQL comments, for example:

	regular syntax ==> SELECT f1, :#1<int> f2 :#2<char[31]> FROM test_tab WHERE f1=:f1<int>

	SQL neutral syntax ==> SELECT f1, /*:#1<int>*/ f2 /*:#2<char[31]>*/ FROM test_tab WHERE f1=:f1/*int*/

The SELECT statement that uses the SQL neutral syntax for its bind variables can be copied and pasted into an SQL tool, and executed without any changes. "EXPLAIN PLAN" can be run on the SELECT statement.

Copyright © 1996-2023, Sergei Kuchin, email: skuchin@gmail.com, skuchin@gmail.com

Permission to use, copy, modify and redistribute this document for any purpose is hereby granted without fee, provided that the above copyright notice appear in all copies. THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.