📄 otl4_ex153.htm
字号:
<!DOCTYPE html PUBLIC "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<meta name="Author" content="Sergei Kuchin">
<meta name="GENERATOR"
content="Mozilla/4.77 [en] (Win95; U) [Netscape]">
<meta name="KeyWords"
content="OTL, Oracle, ODBC, DB2, CLI, database API, C++, Template Library">
<title>OTL 4.0, Example 153 (create_stored_proc_call)</title>
</head>
<body>
<center>
<h1>OTL 4.0, Example 153 (create_stored_proc_call)</h1>
</center>
<h1>
Example 153 (create_stored_proc_call)</h1>
This example demonstrates the use of otl_stream::<a
href="otl3_stream_class.htm#create_stored_proc_call">create_stored_proc_call</a>()
in OTL/OCI8/9.
<h2>Source Code</h2>
<pre>#include <iostream><br>using namespace std;<br><br>#include <stdio.h></pre>
<pre>#define OTL_ORA8 // Compile OTL 4.0/OCI8<br>//#define OTL_ORA8I // Compile OTL 4.0/OCI8i<br>//#define OTL_ORA9I // Compile OTL 4.0/OCI9i<br>#include <otlv4.h> // include the OTL 4.0 header file<br><br><a
href="otl3_connect_class.htm">otl_connect</a> db; // connect object<br><br><br>void print_proc_type(const int stm_type)<br>{<br> switch(stm_type){<br> case otl_inout_stream_type:<br> cout<<"OTL IN/OUT stream";<br> break;<br> case otl_refcur_stream_type:<br> cout<<"OTL reference cursor stream";<br> break;<br> case otl_constant_sql_type:<br> cout<<"OTL constant statement without any parameters";<br> break;<br> }<br>}<br><br>void stored_proc()<br>{ <br> <a
href="otl3_stream_class.htm">otl_stream</a> s; // stream variable, external to the create_stored_proc_call()<br><br> char sql_stm[4096];<br> int stm_type; <br> char refcur_placeholder[128];<br><br> otl_stream::<a
href="otl3_stream_class.htm#create_stored_proc_call">create_stored_proc_call</a> <br> (db, // connect object<br> s, // an external stream variable is needed here<br> sql_stm, // output buffer for generating a stored procedure call<br> stm_type, // output paremeter, indicating what type of stored procedure<br> refcur_placeholder, // output parameter, which gets populated<br> // in the case of a stored procedure that returns<br> // a reference cursor.<br> "my_proc1", // stored procedure name<br> "my_pkg" // PL/SQL package name<br> );<br> cout<<"SQL_STM1="<<sql_stm<<endl;<br> cout<<"STM_TYPE1=";<br> print_proc_type(stm_type);<br> cout<<endl;<br> cout<<"REF.CUR.NAME1="<<refcur_placeholder<<endl;<br> cout<<endl;<br><br> otl_stream::<a
href="otl3_stream_class.htm#create_stored_proc_call">create_stored_proc_call</a> <br> (db, // connect object<br> s, // an external stream variable is needed here<br> sql_stm, // output buffer for generating a stored procedure call<br> stm_type, // output paremeter, indicating what type of stored procedure<br> refcur_placeholder, // output parameter, which gets populated<br> // in the case of a stored procedure that returns<br> // a reference cursor.<br> "my_proc2", // stored procedure name<br> "my_pkg" // PL/SQL package name<br> );<br> cout<<"SQL_STM2="<<sql_stm<<endl;<br> cout<<"STM_TYPE2=";<br> print_proc_type(stm_type);<br> cout<<endl;<br> cout<<"REF.CUR.NAME2="<<refcur_placeholder<<endl;<br> cout<<endl;<br><br> otl_stream::<a
href="otl3_stream_class.htm#create_stored_proc_call">create_stored_proc_call</a> <br> (db, // connect object<br> s, // an external stream variable is needed here<br> sql_stm, // output buffer for generating a stored procedure call<br> stm_type, // output paremeter, indicating what type of stored procedure<br> refcur_placeholder, // output parameter, which gets populated<br> // in the case of a stored procedure that returns<br> // a reference cursor.<br> "my_func1", // stored function name<br> "my_pkg" // PL/SQL package name<br> );<br> cout<<"SQL_STM3="<<sql_stm<<endl;<br> cout<<"STM_TYPE3=";<br> print_proc_type(stm_type);<br> cout<<endl;<br> cout<<"REF.CUR.NAME3="<<refcur_placeholder<<endl;<br> cout<<endl;<br><br> otl_stream::<a
href="otl3_stream_class.htm#create_stored_proc_call">create_stored_proc_call</a> <br> (db, // connect object<br> s, // an external stream variable is needed here<br> sql_stm, // output buffer for generating a stored procedure call<br> stm_type, // output paremeter, indicating what type of stored procedure<br> refcur_placeholder, // output parameter, which gets populated<br> // in the case of a stored procedure that returns<br> // a reference cursor.<br> "my_proc3", // stored procedure name<br> "my_pkg" // PL/SQL package name<br> );<br> cout<<"SQL_STM4="<<sql_stm<<endl;<br> cout<<"STM_TYPE4=";<br> print_proc_type(stm_type);<br> cout<<endl;<br> cout<<"REF.CUR.NAME4="<<refcur_placeholder<<endl;<br> cout<<endl;<br><br> otl_stream::<a
href="otl3_stream_class.htm#create_stored_proc_call">create_stored_proc_call</a> <br> (db, // connect object<br> s, // an external stream variable is needed here<br> sql_stm, // output buffer for generating a stored procedure call<br> stm_type, // output paremeter, indicating what type of stored procedure<br> refcur_placeholder, // output parameter, which gets populated<br> // in the case of a stored procedure that returns<br> // a reference cursor.<br> "my_func2", // stored function name<br> "my_pkg" // PL/SQL package name<br> );<br> cout<<"SQL_STM5="<<sql_stm<<endl;<br> cout<<"STM_TYPE5=";<br> print_proc_type(stm_type);<br> cout<<endl;<br> cout<<"REF.CUR.NAME5="<<refcur_placeholder<<endl;<br> cout<<endl;<br><br>}<br><br>int main()<br>{<br> <a
href="otl3_connect_class.htm">otl_connect::otl_initialize</a>(); // initialize OCI environment<br> try{<br><br> db.rlogon("scott/tiger"); // connect to Oracle<br><br> <a
href="otl3_const_sql.htm">otl_cursor::direct_exec<br></a> (<br> db,<br> "drop table test_tab",<br> otl_exception::disabled // disable OTL exceptions<br> ); // drop table<br><br> <a
href="otl3_const_sql.htm">otl_cursor::direct_exec<br></a> (<br> db,<br> "create table test_tab(f1 number, f2 varchar2(30))"<br> ); // create table<br><br> // create a PL/SQL package<br> <a
href="otl3_const_sql.htm">otl_cursor::direct_exec<br></a> (db,<br> "CREATE OR REPLACE PACKAGE my_pkg IS "<br> " "<br> " TYPE my_cursor IS REF CURSOR; "<br> " "<br> " PROCEDURE my_proc1; "<br> " PROCEDURE my_proc2 "<br> " (f1 IN NUMBER, "<br> " f2 IN OUT VARCHAR2, "<br> " f3 OUT DATE); "<br> " "<br> " FUNCTION my_func1 "<br> " (f1 IN NUMBER, "<br> " f2 IN VARCHAR2) "<br> " RETURN DATE; "<br> " "<br> " PROCEDURE my_proc3 "<br> " (f1 IN NUMBER, "<br> " f2 IN VARCHAR2, "<br> " f3 OUT my_cursor); "<br> " "<br> " FUNCTION my_func2 "<br> " (f1 IN NUMBER, "<br> " f2 IN VARCHAR2) "<br> " RETURN my_cursor; "<br> " "<br> "END; "<br> );<br><br> <a
href="otl3_const_sql.htm">otl_cursor::direct_exec<br></a> (db,<br> "CREATE OR REPLACE PACKAGE BODY my_pkg IS "<br> " "<br> " PROCEDURE my_proc1 "<br> " IS "<br> " BEGIN "<br> " NULL; "<br> " END; "<br> " "<br> " PROCEDURE my_proc2 "<br> " (f1 IN NUMBER, "<br> " f2 IN OUT VARCHAR2, "<br> " f3 OUT DATE) "<br> " IS "<br> " BEGIN "<br> " NULL; "<br> " END; "<br> " " <br> " FUNCTION my_func1 "<br> " (f1 IN NUMBER, "<br> " f2 IN VARCHAR2) "<br> " RETURN DATE "<br> " IS "<br> " BEGIN "<br> " RETURN SYSDATE; "<br> " END; "<br> " " <br> " PROCEDURE my_proc3 "<br> " (f1 IN NUMBER, "<br> " f2 IN VARCHAR2, "<br> " f3 OUT my_cursor) "<br> " IS "<br> " BEGIN "<br> " NULL; "<br> " END; "<br> " " <br> " FUNCTION my_func2 "<br> " (f1 IN NUMBER, "<br> " f2 IN VARCHAR2) "<br> " RETURN my_cursor "<br> " IS "<br> " lv_cur my_cursor; "<br> " BEGIN "<br> " OPEN lv_cur FOR "<br> " SELECT * FROM test_tab; "<br> " RETURN lv_cur; "<br> " END; "<br> " " <br> "END; "<br> );<br><br> stored_proc(); // generate a stored procedure call<br><br> }<br><br> catch(<a
href="otl3_exception_class.htm">otl_exception</a>& p){ // intercept OTL exceptions<br> cerr<<p.msg<<endl; // print out error message<br> cerr<<p.stm_text<<endl; // print out SQL that caused the error<br> cerr<<p.var_info<<endl; // print out the variable that caused the error<br> }<br><br> db.logoff(); // disconnect from Oracle<br><br> return 0;<br><br>}</pre>
<h2>
Output</h2>
<pre>SQL_STM1=BEGIN my_pkg.my_proc1; END;<br>STM_TYPE1=OTL constant statement without any parameters<br>REF.CUR.NAME1=<br><br>SQL_STM2=BEGIN my_pkg.my_proc2(:f1<double,in> ,:f2<char[2001],inout> ,:f3<timestamp,out> ); END;<br>STM_TYPE2=OTL IN/OUT stream<br>REF.CUR.NAME2=<br><br>SQL_STM3=BEGIN :rc__<timestamp,out> := my_pkg.my_func1(:f1<double,in> ,:f2<char[2001],in> ); END;<br>STM_TYPE3=OTL IN/OUT stream<br>REF.CUR.NAME3=<br><br>SQL_STM4=BEGIN my_pkg.my_proc3(:f1<double,in> ,:f2<char[2001],in> ,:f3 ); END;<br>STM_TYPE4=OTL reference cursor stream<br>REF.CUR.NAME4=:f3<br><br>SQL_STM5=BEGIN :rc__ := my_pkg.my_func2(:f1<double,in> ,:f2<char[2001],in> ); END;<br>STM_TYPE5=OTL reference cursor stream<br>REF.CUR.NAME5=:rc__<br><br><br><hr
width="100%"></pre>
<center><a href="otl3_examples.htm">Examples</a> <a href="otl3.htm">Contents</a><a
href="home.htm">Go
Home</a></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
<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></a>.
</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>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -