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(?,?,?,?)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 3.2/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 datatype specificions, 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 suffecient to define scalar data containers to hold just one row. In OTL 4.0, placeholders extended with datatype declarations are called extended placeholders or simply bind variables.
The following datatypes for declaring extended placeholder are
available
in OTL 4.0:
bigint | 64-bit signed 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. OCIs 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 datatype 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. |
blob | for Oracle 8/9; BLOB |
char[length] OTL 4.0.118 and higher: char(length) |
null
terminated
string;
length is database dependent; for Oracle in [3,32545]; 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 declarator needs to include an extra byte / Unicode character, in
order to accomodate 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_exceprtion, 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 datatype 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/9: 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 | 32-bit signed int |
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. 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.
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: 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: RAW, LONG RAW; for ODBC: SQL_LONGVARBINARY, SQL_VARBINARY; for DB2: BLOB |
short | short int (16-bit signed integer) |
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 |
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
{
call :rc<int,out> := 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. The following code is invalid:
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 > := ...;
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, 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:
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 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.