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 rows. 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:
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:
BEGIN :rc<int,out> := my_func(:salary<float,in>, :ID<int,inout>, :name<char[32],out> ); END;Example 2 (ODBC or DB2-CLI):
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>
)
}
In the bind variable declaration, spaces in the datatype section and in
the access qualifier section ARE NOT allowed. The following code is invalid:
Example 1
insert into tab1 values(:salary< double >, :name< char [ 32 ] > , :emp_id< int>);
Example 2
:rc< int, out > := ...;
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;[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].
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.
The maximum PL/SQL table size is limited to 32767.
For more detail, see examples 49, 50, 51, 52.
Copyright © 1996, 2001, Sergei Kuchin, email: skuchin@sprynet.com, kuchin@hotmail.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.