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 statament / PL/SQL block / stored procedure call and allocates the corresponding bind variables dynamically inside the stream.

In Oracle, the naming convension of placholders 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 placholder 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 datatype specificions, e.g.:

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

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

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

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

Old (OTL 2.0/ODBC) style:

   {
    call :1<int,out> := 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, datatypes, and column lengths dynamically right after the SELECT statement is parsed. OTL maps column internal datatypes into C++ external datatypes by default. The default mapping can be overriden 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 datatypes 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, and OTL internally emulates bigints via strings (char[XXX]) for OCIs).
  
•  <unsigned>
•  <short>
•  <long>
• <raw>
  
• <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 defintions 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.

Copyright © 1996, 2007, Sergei Kuchin, email: skuchin@ispwest.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.