#include <iostream>
using namespace std;
#include <stdio.h>
#define OTL_ORA8 // Compile OTL 4.0/OCI8
#define OTL_ORA_CREATE_STORED_PROC_CALL_MAPS_RAW_TO_RAW_LONG
//#define OTL_ORA8I // Compile OTL 4.0/OCI8i
//#define OTL_ORA9I // Compile OTL 4.0/OCI9i
#include <otlv4.h> // include the OTL 4.0 header file
otl_connect db; // connect object
void print_proc_type(const int stm_type)
{
switch(stm_type){
case otl_inout_stream_type:
cout<<"OTL IN/OUT stream";
break;
case otl_refcur_stream_type:
cout<<"OTL reference cursor stream";
break;
case otl_constant_sql_type:
cout<<"OTL constant statement without any parameters";
break;
case otl_mixed_refcur_stream_type:
cout<<"OTL mixed reference cursor stream";
break;
}
}
void stored_proc()
{
otl_stream s; // stream variable, external to the create_stored_proc_call()
char sql_stm[4096];
int stm_type;
char refcur_placeholder[128];
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"my_proc1", // stored procedure name
"my_pkg" // PL/SQL package name
);
cout<<"SQL_STM1="<<sql_stm<<endl;
cout<<"STM_TYPE1=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME1="<<refcur_placeholder<<endl;
cout<<endl;
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"my_proc2", // stored procedure name
"my_pkg" // PL/SQL package name
);
cout<<"SQL_STM2="<<sql_stm<<endl;
cout<<"STM_TYPE2=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME2="<<refcur_placeholder<<endl;
cout<<endl;
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"my_func1", // stored function name
"my_pkg" // PL/SQL package name
);
cout<<"SQL_STM3="<<sql_stm<<endl;
cout<<"STM_TYPE3=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME3="<<refcur_placeholder<<endl;
cout<<endl;
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"my_proc3", // stored procedure name
"my_pkg" // PL/SQL package name
);
cout<<"SQL_STM4="<<sql_stm<<endl;
cout<<"STM_TYPE4=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME4="<<refcur_placeholder<<endl;
cout<<endl;
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"my_func2", // stored function name
"my_pkg" // PL/SQL package name
);
cout<<"SQL_STM5="<<sql_stm<<endl;
cout<<"STM_TYPE5=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME5="<<refcur_placeholder<<endl;
cout<<endl;
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"my_proc4", // stored function name
"my_pkg", // PL/SQL package name
0, // schema name
false, // schema name not included
2001, // varchar paratemer default size
otl_var_double, // default type to map NUMERIC parameters to
100 // refernce cursor buffer size
);
cout<<"SQL_STM6="<<sql_stm<<endl;
cout<<"STM_TYPE6=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME6="<<refcur_placeholder<<endl;
cout<<endl;
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"my_func3", // stored function name
"my_pkg", // PL/SQL package name
0, // schema name
false, // schema name not included
2001, // varchar paratemer default size
otl_var_double, // default type to map NUMERIC parameters to
100 // refernce cursor buffer size
);
cout<<"SQL_STM7="<<sql_stm<<endl;
cout<<"STM_TYPE7=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME7="<<refcur_placeholder<<endl;
cout<<endl;
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"prc_glob",// stored procedure name
0 // package name
);
cout<<"SQL_STM8="<<sql_stm<<endl;
cout<<"STM_TYPE8=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME8="<<refcur_placeholder<<endl;
cout<<endl;
otl_stream::create_stored_proc_call
(db, // connect object
s, // an external stream variable is needed here
sql_stm, // output buffer for generating a stored procedure call
stm_type, // output paremeter, indicating what type of stored procedure
refcur_placeholder, // output parameter, which gets populated
// in the case of a stored procedure that returns
// a reference cursor.
"my_proc5", // stored procedure name
"my_pkg" // PL/SQL package name
);
cout<<"SQL_STM9="<<sql_stm<<endl;
cout<<"STM_TYPE9=";
print_proc_type(stm_type);
cout<<endl;
cout<<"REF.CUR.NAME9="<<refcur_placeholder<<endl;
cout<<endl;
}
int main()
{
otl_connect::otl_initialize(); // initialize OCI environment
try{
db.rlogon("scott/tiger"); // connect to Oracle
otl_cursor::direct_exec
(
db,
"drop table test_tab",
otl_exception::disabled // disable OTL exceptions
); // drop table
otl_cursor::direct_exec
(
db,
"create table test_tab(f1 number, f2 varchar2(30))"
); // create table
// create a PL/SQL package
otl_cursor::direct_exec
(db,
"CREATE OR REPLACE PACKAGE my_pkg IS "
" "
" TYPE my_cursor IS REF CURSOR; "
" SUBTYPE my_raw IS RAW(123); "
" "
" PROCEDURE my_proc1; "
" PROCEDURE my_proc2 "
" (f1 IN NUMBER, "
" f2 IN OUT VARCHAR2, "
" f3 OUT DATE); "
" "
" FUNCTION my_func1 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2) "
" RETURN DATE; "
" "
" PROCEDURE my_proc3 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2, "
" f3 OUT my_cursor); "
" "
" PROCEDURE my_proc4 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2, "
" f3 OUT NUMBER, "
" f4 OUT my_cursor, "
" f5 OUT my_cursor); "
" "
" FUNCTION my_func2 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2) "
" RETURN my_cursor; "
" "
" FUNCTION my_func3 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2, "
" f3 OUT NUMBER) "
" RETURN my_cursor; "
"END; "
);
otl_cursor::direct_exec
(db,
"CREATE OR REPLACE PACKAGE BODY my_pkg IS "
" "
" PROCEDURE my_proc1 "
" IS "
" BEGIN "
" NULL; "
" END; "
" "
" PROCEDURE my_proc2 "
" (f1 IN NUMBER, "
" f2 IN OUT VARCHAR2, "
" f3 OUT DATE) "
" IS "
" BEGIN "
" NULL; "
" END; "
" "
" FUNCTION my_func1 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2) "
" RETURN DATE "
" IS "
" BEGIN "
" RETURN SYSDATE; "
" END; "
" "
" PROCEDURE my_proc3 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2, "
" f3 OUT my_cursor) "
" IS "
" BEGIN "
" NULL; "
" END; "
" "
" PROCEDURE my_proc4 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2, "
" f3 OUT NUMBER, "
" f4 OUT my_cursor, "
" f5 OUT my_cursor) "
" IS "
" BEGIN "
" NULL; "
" END; "
" "
" PROCEDURE my_proc5 "
" (f1 IN my_raw, "
" f2 IN OUT LONG RAW); "
" "
" FUNCTION my_func2 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2) "
" RETURN my_cursor "
" IS "
" lv_cur my_cursor; "
" BEGIN "
" OPEN lv_cur FOR "
" SELECT * FROM test_tab; "
" RETURN lv_cur; "
" END; "
" "
" FUNCTION my_func3 "
" (f1 IN NUMBER, "
" f2 IN VARCHAR2, "
" f3 OUT NUMBER) "
" RETURN my_cursor "
" IS "
" lv_cur my_cursor; "
" BEGIN "
" OPEN lv_cur FOR "
" SELECT * FROM test_tab; "
" RETURN lv_cur; "
" END; "
" "
" PROCEDURE my_proc5 "
" (f1 IN my_raw, "
" f2 IN OUT LONG RAW) "
" IS "
" BEGIN "
" NULL; "
" END; "
" "
"END; "
);
otl_cursor::direct_exec
(db,
"CREATE OR REPLACE PROCEDURE prc_glob IS "
"BEGIN "
" NULL; "
"END;"
);
stored_proc(); // generate a stored procedure call
}
catch(otl_exception& p){ // intercept OTL exceptions
cerr<<p.msg<<endl; // print out error message
cerr<<p.stm_text<<endl; // print out SQL that caused the error
cerr<<p.var_info<<endl; // print out the variable that caused the error
}
db.logoff(); // disconnect from Oracle
return 0;
}
SQL_STM1=BEGIN my_pkg.my_proc1; END;
STM_TYPE1=OTL constant statement without any parameters
REF.CUR.NAME1=
SQL_STM2=BEGIN my_pkg.my_proc2(:f1<double,in> ,:f2<char[2001],inout> ,:f3<timestamp,out> ); END;
STM_TYPE2=OTL IN/OUT stream
REF.CUR.NAME2=
SQL_STM3=BEGIN :rc__<timestamp,out> := my_pkg.my_func1(:f1<double,in> ,:f2<char[2001],in> ); END;
STM_TYPE3=OTL IN/OUT stream
REF.CUR.NAME3=
SQL_STM4=BEGIN my_pkg.my_proc3(:f1<double,in> ,:f2<char[2001],in> ,:f3 ); END;
STM_TYPE4=OTL reference cursor stream
REF.CUR.NAME4=:f3
SQL_STM5=BEGIN :rc__ := my_pkg.my_func2(:f1<double,in> ,:f2<char[2001],in> ); END;
STM_TYPE5=OTL reference cursor stream
REF.CUR.NAME5=:rc__
SQL_STM6=BEGIN my_pkg.my_proc4(:f1<double,in> ,:f2<char[2001],in> ,:f3<double,out> ,:f4<refcur,out[100]>,:f5<refcur,out[100]>); END;
STM_TYPE6=OTL mixed reference cursor stream
REF.CUR.NAME6=
SQL_STM7=BEGIN :rc__<refcur,out[100]> := my_pkg.my_func3(:f1<double,in> ,:f2<char[2001],in> ,:f3<double,out> ); END;
STM_TYPE7=OTL mixed reference cursor stream
REF.CUR.NAME7=
SQL_STM8=BEGIN prc_glob; END;
STM_TYPE8=OTL constant statement without any parameters
REF.CUR.NAME8=
SQL_STM9=BEGIN my_pkg.my_proc5(:f1<raw_long,in> ,:f2<raw_long,inout> ); END;
STM_TYPE9=OTL IN/OUT stream
REF.CUR.NAME9=
For more detail on "mixed reference cursor streams", see also examples 148, 149, 151.
Copyright © 1996-2024, 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.