📄 otl3_bind_variables.htm
字号:
<td style="vertical-align: top;"><span style="font-weight: bold;"><a
name="refcur"></a>refcur</span><br>
</td>
<td style="vertical-align: top;">for Oracle 8/9/10/11. When a
stored procedure returns a reference cursor, a bind variable of <span
style="font-style: italic;">refcur</span> type may be declared in the
PL/SQL block that calls the stored procedure, for example:<br>
<br>
<pre> "begin "<br> " my_pkg.my_proc(:f1<int,in>,:f2<int,in>, "<br> " :str1<char[100],out>, "<br> // :str1 is an output string parameter<br> " :cur1<refcur,out[50]>, "<br> " :cur2<refcur,out[50]>); "<br> // :cur1, :cur2 are a bind variable names, refcur -- their types, <br> // out -- output parameter, 50 -- the buffer size when this<br> // reference cursor will be attached to <a
href="otl4_refcur_stream.htm">otl_refcur_stream<br></a> "end;"</pre>
</td>
</tr>
<tr>
<td style="vertical-align: top;"><b>short</b></td>
<td style="vertical-align: top;">short int (16-bit signed integer)<br>
</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="timestamp"></a><b>timestamp</b></td>
<td style="vertical-align: top;">MS SQL
Server/Sybase
DATETIME,
DB2 TIMESTAMP, Oracle DATE, Oracle 9i TIMESTAMP (when #define <a
href="otl3_compile.htm#OTL_ORA_TIMESTAMP">OTL_ORA_TIMESTAMP</a>
is enabled) ; it requires TIMESTAMP_STRUCT
(OTL/ODBC, OTL/DB2-CLI), or <a
href="otl3_stream_class.htm#otl_datetime">otl_datetime</a>
(ODBC, DB2-CLI, and OCIx). OTL/DB2-CLI
and OTL/ODBC for DB2; requires <a
href="otl3_stream_class.htm#otl_datetime">otl_datetime</a>
as a data container. See example <a href="otl3_ex91.htm">91</a> for
more
detail</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="tz_timestamp"></a><b>tz_timestamp</b><a
href="otl3_stream_class.htm#otl_datetime"></a></td>
<td style="vertical-align: top;">Oracle 9i
TIMESTAMP WITH TIME ZONE, in a combination with #define <a
href="otl3_compile.htm#OTL_ORA_TIMESTAMP">OTL_ORA_TIMESTAMP</a>,
and <a href="otl3_stream_class.htm#otl_datetime">otl_datetime</a></td>
</tr>
<tr>
<td style="vertical-align: top;"><b>unsigned</b></td>
<td style="vertical-align: top;">unsigned int (32-bit unsigned
integer)<br>
</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="varchar_long"></a><b>varchar_long</b></td>
<td style="vertical-align: top;">for Oracle 7:
LONG; for
Oracle 8/9: LONG; for ODBC: SQL_LONGVARCHAR; for DB2: CLOB</td>
</tr>
</tbody>
</table>
<br>
<br>
varchar_long, raw_long clob and blob require the <a
href="otl3_long_string.htm">otl_long_string</a>
class as a data container. In order to set the maximum size for
varchar_long,
raw_long, clob or blob, see the set_max_long_size() function in the <a
href="otl3_connect_class.htm">otl_connect</a>
class for more detail.
<p><a name="access_qualifiers"></a>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:
</p>
<ul>
<li> <b>in</b> -- input variable</li>
<li> <b>out </b>-- output variable</li>
<li> <b>inout </b>-- input/output variable</li>
</ul>
<b>Example 1 (Oracle):</b>
<pre> BEGIN<br> :rc<int,out> := my_func(:salary<float,in>, <br> :ID<int,inout>, <br> :name<char[32],out><br> );<br> END;</pre>
<b>Example 2 (ODBC or DB2-CLI):</b>
<p>New (OTL 4.0/ODBC, OTL 4.0/DB2-CLI) style
</p>
<pre> {<br> call :rc<int,out> := my_func(:salary<float,in>, <br> :ID<int,inout>, <br> :name<char[32],out><br> )<br> }</pre>
<pre>Old (OTL 2.0/ODBC) style:</pre>
<pre> {<br> call :1<int,out> := my_func(:2<float,in>, <br> :3<int,inout>, <br> :4<char[32],out><br> )<br> }</pre>
In the bind variable declaration, spaces in the datatype section and in
the access qualifier section ARE NOT allowed. The following code is
invalid:
<p><b>Example 1</b>
</p>
<pre> insert into tab1 values(:salary< double >, :name< char [ 32 ] > , :emp_id< int>);</pre>
<p><br>
<b>Example 2</b>
</p>
<pre> :rc< int, out > := ...;<br><br></pre>
<h3><b><font><b><font size="+2"><a name="COLON_LITERAL"></a>Embedding
colon (":") literals into SQL statements in OTL/ODBC</font></b></font></b></h3>
<p>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: "<span
style="font-family: monospace;">\\:</span>". OTL/ODBC 4.0.68 and
higher supports the notation.<b><font><b><font size="+2"><br>
</font></b></font></b></p>
<h3><br>
<a name="SELECT_OVERRIDE"></a><b><font><b><font size="+2">Explicit bind
variables in output column definitions in SELECT statements<br>
</font></b></font></b></h3>
<p>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 <a
href="otl3_stream_class.htm#set_column_type">otl_stream::set_column_type</a>()
and <a href="otl3_stream_class.htm#set_all_column_types">otl_stream::set_all_column_types</a>()
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: <span style="font-weight: bold;">:#N<datatype></span>,
where N is 1,2,3 (column's relative position in the SELECT
statement)... SELECT
output columns do not have a direct equivalent of <span
style="font-style: italic;"><span style="font-weight: bold;">:VAR</span>
</span>(OCI), or <span style="font-style: italic; font-weight: bold;">?</span>
(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:<br>
</p>
SELECT f1 <span style="font-style: italic;">:#1<short></span>,
f2<br>
FROM test_tab<br>
...<br>
<p>Here is the resulting SELECT statement that gets passed into the
database API:<br>
</p>
SELECT f1 , f2<br>
FROM test_tab<br>
<br>
<p>The set of datatypes allowed in <span style="font-style: italic;">:#N<datatype></span>
is limited and database API specific, In general, the following types
are allowed:<br>
</p>
<ul>
<li> <char[XXX]></li>
<li> <double></li>
<li> <float></li>
<li> <int></li>
<li> <bigint> (which is supported natively in ODBC /
DB2-CLI, and OTL internally emulates bigints via strings (char[XXX])
for OCIs).<br>
</li>
<li> <unsigned></li>
<li> <short></li>
<li> <long></li>
<li><raw><br>
</li>
<li><raw_long><br>
</li>
<li><timestamp></li>
<li><varchar_long><br>
</li>
</ul>
Also, it depends on the internal type of the column to be overriden
whether the target type (from the list above) is compatible with the
source / internal type of the column or not. For more detail, refer to
the correspoding database API manual. A quick way to figure out if the
types are compatible is through trial and error.<br>
<br>
The OTL internal to external default data type mapping for output
columns in SELECT statement works okay for the most part, except for
rare cases when there may be some special considerations for
performance, or external datatype definitions (for example,
compatibility
with predefined or general-purpose C++ containers).<br>
<br>
<a name="SP_COL_OVERRIDE"></a>In OTL 4.0.117 and higher, it is possible
to do the same kind of overriding (as described above) for output
column datatypes of an implicit result set (ODBC/DB2-CLI), or a
reference cursor (Oracle), for example:<br>
<br>
<span style="font-style: italic;"> Oracle:<br>
<br>
</span> <span style="font-family: monospace;">otl_stream
<br>
s(50,<br>
</span>
"<span style="font-family: monospace;">BEGIN "<br>
"
my_proc1(:f1<int,in>,:res_set); "<br>
" :#1<int,out> "<br>
"
:#2<char[31],out> "<br>
"END;",<br>
db, // otl_connect object<br>
</span><span style="font-style: italic;"></span>
<span style="font-family: monospace;">":res_set" // reference cursor /
result set's placeholder name<br>
);<br>
<br>
</span><span style="font-style: italic;">
ODBC/DB2-CLI:<br>
<br>
</span> <span style="font-family: monospace;">otl_stream
<br>
s(50,<br>
</span>
"<span style="font-family: monospace;">{ "<br>
" my_proc1(:f1<int,in>) "<br>
</span><span style="font-family: monospace;">
" :#1<int,out> "<br>
"
:#2<char[31],out> "<br>
</span><span style="font-family: monospace;">
"}",<br>
db, // otl_connect object<br>
</span><span style="font-style: italic;"></span>
<span
style="font-family: monospace;">otl_implicit_select // implicit result
set / select flag<br>
);</span><br>
<br>
Output column overrides are optional. If a column is not overriden
explicitly, the default datatype mapping applies..<br>
<h3><a name="pl_tab"></a><b><font size="+2">Declaration of PL/SQL
tables
(OTL/OCIx)</font></b></h3>
OTL 3.x/OCIx, release OTL 3.1.0 and higher, supports PL/SQL tables via
the otl_stream class and special <a href="otl3_pl_tab.htm">template
PL/SQL
table container</a> classes. This feature works only for PL/SQL blocks
and stored procedures. For example, a stored procedure, which takes
PL/SQL
tables as arguments, gets called in a block. The PL/SQL table
containers
can be used to read/write the whole PL/SQL table from/to the OTL stream
in one shot. In the OCIx and Pro*C, it is a well known technique, only
the interface is a way too complex.
<p>In OTL 4.0/OCIx, in PL/SQL blocks, a PL/SQL table dimension can be
added
to the access qualifiers <b>in</b>/<b>out</b>/<b>inout</b>, e.g.:
</p>
<pre> BEGIN<br> my_pkg.my_proc(:salary<float,in<b>[100]</b>>, <br> :ID<int,inout<b>[200]</b>>, <br> :name<char[32],out<b>[150]</b>><br> );<br> END;</pre>
[100] is the maximum size of the <i>:salary</i> placeholder, which is
an
input PL/SQL table of float[100]. [200] is the maximum size of the <i>:ID</i>
placeholder, which is an input/output PL/SQL table of int[100]. [150]
is
the maximum size of the <i>:name</i> placeholder, which is an output
PL/SQL
table of char[150][32].
<p>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.
</p>
<p><a name="PLSQL_table_size"></a>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 <a
href="otl3_compile.htm#OTL_STREAM_LEGACY_BUFFER_SIZE_TYPE">OTL_STREAM_LEGACY_BUFFER_SIZE_TYPE</a>.<br>
</p>
<p>For more detail, see examples <a href="otl3_ex49.htm">49</a>, <a
href="otl3_ex52.htm">50,
51, 52.</a>
</p>
<center>
<p></p>
<hr width="100%">
<p><a href="otl3_class.htm">Prev</a> <a href="otl3_connect_class.htm">Next</a><a
href="otl3.htm">Contents</a><a href="home.htm">Go
Home</a></p>
</center>
<p>Copyright © 1996, 2008, Sergei Kuchin, email: <a
href="mailto:skuchin@aceweb.com">skuchin@aceweb.com</a>,
<a href="mailto:skuchin@gmail.com">skuchin@gmail.com</a>
<script language="JavaScript"><!-- hide from old browsers
var modDate = new Date(document.lastModified)
document.write("<i>Last Updated:</i> " + (modDate.getMonth()+1) + "/" +
modDate.getDate() + "/" + "0"+(modDate.getYear())%100);
//-->
</script></p>
<p><i>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.</i>
</p>
<script type="text/javascript">
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
</script>
<script type="text/javascript">
var pageTracker = _gat._getTracker("UA-5456201-1");
pageTracker._trackPageview();
</script>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -