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(?,?,?,?)OTL 2.x/ODBC also had a positional notation for placeholders:
INSERT INTO my_table values(:1<int>,:2<char[32]>,:3<double>,:4<char[128]>)
:<Number> gets translated into ?.OTL 4.0/ODBC still supports the :<Number> notation for placeholders. However, OTL 4.0 for Oracle, ODBC, and DB2-CLI have the named notation for placeholders, so it is recommended to use it in both cases. There is only one restriction on named placeholders in OTL 4.0/ODBC and OTL 4.0/DB2-CLI: the same placeholder may not be referenced more than once in the same SQL statement. This restriction is imposed by ODBC/DB2-CLI (see above).
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]>)It makes the placeholder declaration complete, so there is no need to declare host arrays in the program and bind them by calling special bind functions. It is sufficient to define scalar data containers to hold just one row. In OTL 4.0, placeholders extended with data type declarations are called extended placeholders or simply bind variables.
The following data types for declaring extended placeholder are
available in OTL 4.0:
bigint | Signed 64-bit integer, for
binding with BIGINT table columns (or stored procedure
parameters) in MS SQL Server, DB2, MySQL, PostrgeSQL, etc.
ODBC, and DB2 CLI support this kind bind variables natively,
so does OTL. 32-bit OCIs prior to OCI 11.2 do not have
native support for 64-bit integers, so OTL has to emulate it
via string (<char[XXX]>) bind variables internally and
does string-to-bigint and bigint-to-string conversion. See
also (1) OTL_BIGINT,
(2) OTL_BIGINT_TO_STR,
(3)
OTL_STR_TO_BIGINT.
With OTL/OCIx all three #defines (1), (2), and (3) need to
be defined in order to make OTL compilable, because the
bigint data type name, the bigint-to-string conversion code,
and the string-to-bigint conversion code get expanded into
the source code of the OTL header file in the process of
compilation. 64-bit OCIs have support for 64-bit integers in LP64 compliant platforms (which have 64-bit "long int"). 64-bit Windows is LLP64 ("long int" is 32 bits), so it's recommended to use OTL's "bigint". #define OTL_ORA_MAP_BIGINT_TO_LONG can be used to map <bigint> to 64-bit longs on LP64 platforms, which is more efficient than the char[XXX] OCI binding for <bigint>. OCI 11.2 and higher has native support for signed 64-bit ints, so it's sufficient to define OTL_BIGINT and OTL_ORA11G_R2, in order to enable native support for signed 64-bit ints |
blob | for Oracle 8 and higher; BLOB |
bdouble |
Same as double
for OTL/OCI10 and higher. Should only be used when PL/SQL tables of
type BINARY_DOUBLE are used. bdouble is actually a
workaround for the following Oracle error: PLS-00418: array bind type
must match PL/SQL table row type. Normally, the
internal OCI data type that is used to bind BINARY_DOUBLE
table columns / scalar PL/SQL procedure parameters works
fine, except for PL/SQL tables of BINARY_DOUBLE. PL/SQL
engine does not like what OTL tries to bind with a PL/SQL
table of BINARY_DOUBLE. bdouble should be used instead of
double in cases like that. |
bfloat |
Same as float for OTL/OCI10 and higher. Should only be used when PL/SQL tables of type BINARY_FLOAT are used. bfloat is actually a workaround for the following Oracle error: PLS-00418: array bind type must match PL/SQL table row type. Normally, the internal OCI data type that is used to bind BINARY_FLOAT table columns / scalar PL/SQL procedure parameters works fine, except for PL/SQL tables of BINARY_FLOAT. PL/SQL engine does not like what OTL tries to bind with a PL/SQL table of BINARY_FLOAT. bfloat should be used instead of float in cases like that. |
char[length] OTL 4.0.118 and higher: char(length) |
null terminated string;
length is database dependent: for Oracle in the range of
[3..32545] when the corresponding C++ side bind variable is
used with a PL/SQL VARCHAR2 data type, or in the range of
[3..2GB] when the C++ side variable is used with CLOB/ LONG
SQL data types; for ODBC it depends on the database backend
and the ODBC driver; for DB2-CLI >2. In Unicode OTL (see
#define OTL_UNICODE),
this type of bind variable declaration means a null
terminated Unicode character string (two bytes per
character). The length field of this declaration
needs to include an extra byte / Unicode character, in order
to accommodate the null terminator itself (for example
char[11] can be used in binding with a VARCHAR(9) column),
unless #define OTL_ADD_NULL_TERMINATOR_TO_STRING_SIZE
is enabled. "char" declaration without a specified length is invalid and is going to result in an otl_exception, for example: "INSERT INTO test_tab VALUES(:f1<int>,:f2<char>)" Error code: 32013 Error message: Invalid bind variable declaration var info: f2 char |
charz |
Same as char[] for OTL_ORA7, OTL_ORA8, OTL_ORA8I, OTL_ORA9I, OTL_ORA10G. Should
be used only when PL/SQL
tables of type CHAR(XXX) are used. charz is actually a
workaround for the following Oracle error: PLS-00418: array bind type
must match PL/SQL table row type. Normally, the
internal OCI data type that is used to bind VARCHAR2/CHAR
table columns / scalar PL/SQL procedure parameters works
fine, except for PL/SQL tables of CHAR(XXX). PL/SQL engine
does not like what OTL tries to bind with a PL/SQL table of
CHAR(XXX). charz[] should
be used instead of char[] in cases like that. |
clob | for Oracle 8 and higher: CLOB, NCLOB |
db2date | for DB2 DATEs; should be used in the binding of a placeholder with a DB2 DATE column in case of both |
db2time | for DB2 TIMEs; should be used in the binding of a placeholder with a DB2 TIME column in case of both OTL/DB2-CLI and OTL/ODBC for DB2; requires otl_datetime as a data container. See example 91 for more detail. |
double | 8-byte floating point number |
float | 4-byte floating point number |
int | signed 32-bit int |
long |
If you develop code for a single platform for a single database type, <long> is okay to use. |
ltz_timestamp | Oracle 9i TIMESTAMP WITH LOCAL TIME ZONE, in a combination with #define OTL_ORA_TIMESTAMP, and otl_datetime |
nchar[length] |
Same as char[] + otl_connect::set_character_set(SQLCS_NCHAR) for Oracle 8i/9i/10g only, under #define OTL_UNICODE., or #define OTL_ORA_UTF8. nchar[] is required only when both VARCHAR2/CHAR and NVARCHAR2/NCHAR need to be declared in the same SQL statement, or PL/SQL block. |
nclob |
Same as clob
+ otl_connect::set_character_set(SQLCS_NCHAR)
for
Oracle
8i/9i/10g only, under #define OTL_UNICODE,
or #define OTL_ORA_UTF8.
nclob is required only when both CLOB and NCLOB need to be
declared in the same SQL statement, or PL/SQL block. |
raw[length] | for Oracle 7/8/9/10/11: RAW,
LONG RAW; for ODBC: SQL_BINARY (BINARY in MS SQL,
Sybase 15, MySQL; CHAR (XXX) BYTE in SAP/MAX DB),
SQL_VARBINARY (VARBINARY in MS SQL, Sybase 15, MySQL; BYTEA
in PostgreSQL, VARCHAR(XXX) BYTE in SAP/MAX DB). Columns of
this type can be written to / read from with otl_long_string()'s. The
maximum allowed size is specific to each supported database
type. Also, see #define OTL_MAP_SQL_VARBINARY_TO_RAW_LONG,
#define OTL_MAP_SQL_GUID_TO_CHAR,
#define OTL_MAP_SQL_BINARY_TO_CHAR |
raw_long | for Oracle 7: RAW, LONG RAW; for Oracle 8/9/10/11: RAW, LONG RAW; for ODBC: SQL_LONGVARBINARY, SQL_VARBINARY; for DB2: BLOB |
refcur |
for Oracle 8/9/10/11. When a
stored procedure returns a reference cursor, a bind variable
of refcur type may
be declared in the PL/SQL block that calls the stored
procedure, for example:"begin " |
short | short int (16-bit signed
integer) |
sdo_geometry |
under #define OTL_ORA_SDO_GEOMETRY,
type for reading / writing oci_spatial_geometry
values. |
timestamp | MS SQL Server/Sybase DATETIME, DB2 TIMESTAMP, Oracle DATE, Oracle 9i TIMESTAMP (when #define OTL_ORA_TIMESTAMP is enabled) ; it requires TIMESTAMP_STRUCT (OTL/ODBC, OTL/DB2-CLI), or otl_datetime (ODBC, DB2-CLI, and OCIx). OTL/DB2-CLI and OTL/ODBC for DB2; requires otl_datetime as a data container. See example 91 for more detail |
tz_timestamp | Oracle 9i TIMESTAMP WITH TIME ZONE, in a combination with #define OTL_ORA_TIMESTAMP, and otl_datetime |
ubigint |
Natively supported unsigned
64-bit int. #define OTL_UBIGINT
needs to be enabled. |
unsigned | unsigned int (32-bit unsigned
integer) |
varchar_long | for Oracle 7: LONG; for Oracle 8/9: LONG; for ODBC: SQL_LONGVARCHAR; for DB2: CLOB |
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:
BEGINExample 2 (ODBC or DB2-CLI):
: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:
{In the bind variable declaration, spaces in the datatype section and in the access qualifier section ARE NOT allowed, yet a whitespace in a bind variable declaration was found in the SQL statement. The following code is invalid:
: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 > := ...;
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.
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:
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[100] is the maximum size of the :salary placeholder, which is an input PL/SQL table of float[100]. [200] is the maximum size of the :ID placeholder, which is an input/output PL/SQL table of int[100]. [150] is the maximum size of the :name placeholder, which is an output PL/SQL table of char[150][32].
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.
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.