Sergei Kuchin, email: skuchin@gmail.com, skuchin@gmail.com
This document describes the Oracle Call Interface Template Library, Version 2.0.0 (OTL). The library introduces a one-page Oracle API in the form of SQL streams.
OTL is a new kind of C++ libraries, similar to the Standard Template Library. This kind of libraries is easy to use, since the user needs only to include C++ header files which contain template classes and inline functions. There is no need to link additional object modules into C++ applications. The code instantiated from template classes and inline functions is efficient and reliable in terms of runtime performance and C++ strict type checking.
OTL automatically uses Oracle Array Interface (SQL streams are buffered by definition) and has a higher runtime performance than Pro*C because there is no overhead on caching cursors and filling out the SQLCA global structure before every single SQL statement embedded into the program. Runtime performance can be improved even further by increasing the stream buffer size.
OTL provides an exception handling mechanism in the format of the otl_exception class. This mechanism takes advantage of C++ exception handling compared to coding database applications in plain C. The user does not need to check out return codes after each function call. The code, instantiated from the OTL templates and inline functions is much nicer and cleaner in comparison with the code generated by the Pro*C precompiler.
Also, OTL integrates with the Standard Template Library by means of STL-compliant iterator classes. This feature is very powerful because it allows to combine the power of generic programming and Oracle database backend into compact, reliable, top performance and easy-to-maintain C++ database applications.
This version of OTL is available for both Oracle 7.x and Oracle 8.x. Both implemetations use native OCIs:
It provides smooth transition from Oracle 7 to Oracle 8 with no changes to the custom source code. Besides, OTL compiles by many C++ compilers on major brands of Unix as well as Windows NT and Windows 95/98.
The fact that OTL is available in source code adds more value to this material -- folks new to OCI can use the OTL source code to learn both OCI7 and OCI8 . Moreover, OTL can be used a guidance for migrating programs from native OCI7 to native OCI8.
Inside the SQL stream there is a small parser for parsing declarations of bind variables and their data types. There is no need to declare C/C++ host variables and bind them with placeholders by special bind function calls. All necessary buffers are created dynamically inside the stream. The stream just needs to be opened for reading input values and writing output values.
The OTL stream interface requires use of the OTL exceptions. This means that potentially any OTL stream operation can throw an exception of the otl_exception type. In order to intercept the exception and prevent the program from aborting, wrap up the OTL stream code with the corresponding try & catch block.
OTL introduces the otl_stream class which is the actual implementation of the SQL Stream concept. It is unified for any SQL statement or PL/SQL block which potentially may have input/output bind variables (placeholders). otl_stream provides the same interface for both straight SELECT statements and SELECT statements to be returned via a referenced cursor. Though, implementations are quite different.
An OTL stream needs to have at least one bind variable. See 2.4. for more information on how to execute a constant SQL statement or PL/SQL block.
The following format specifiers are supported:
The following data types for extneded placeholder declarations are available:
begin
:rc<int,out> := my_func(:salary<float,in>,
:ID<int,inout>,
:name<char[32],out>
);
end;
select * from tab1 where f1 > :f1<double>
Select all columns from the tab1 table where f1 is greater than :f1
insert into tab1 values( :f1<double>, :f2;<char[32]>, :f3<int> )
Insert row { :f1(double), :f2(string), :f3(integer) } into the tab1 table.
enum{ disabled, enabled };
otl_cursor is one of OTL internal classes. There is another format of the direct_exec function call:
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:f1<float>,:f2<char[31]>)",
// SQL statement db // connect object ); char tmp[32];
for(int i=1;i<=100;++i){
sprintf(tmp,"Name%d",i);
o<<(float)i<<tmp;
}
}void select()
{ otl_stream i(50, // buffer size "select * from test_tab where f1>=:f<int> and f1<=:f*2",
// SELECT statement db // connect object ); // create select stream int f1;
char f2[31];
i<<8; // assigning :f = 8 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i>>f1>>f2;
cout<<"f1="<<f1<<", f2="<<f2<<endl;
}
i<<4; // assigning :f = 4 // SELECT automatically re-executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i>>f1>>f2;
cout<<"f1="<<f1<<", f2="<<f2<<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 insert(); // insert records into table select(); // select records from table } catch(otl_exception& p){ // intercept OTL exceptions cerr<<p.msg<<endl; // print out error message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from Oracle return 0;
}
f1=8, f2=Name8 f1=9, f2=Name9 f1=10, f2=Name10 f1=11, f2=Name11 f1=12, f2=Name12 f1=13, f2=Name13 f1=14, f2=Name14 f1=15, f2=Name15 f1=16, f2=Name16 f1=4, f2=Name4 f1=5, f2=Name5 f1=6, f2=Name6 f1=7, f2=Name7 f1=8, f2=Name8
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid plsql()
// invoking PL/SQL block { otl_stream o(5, // buffer size "begin " " :A<int,inout> := :A+1; " " :B<char[31],out> := :C<char[31],in>; " "end;",
// PL/SQL block db // connect object ); o<<1<<"Test String1"; // assigning :A = 1, :C = "Test String1" o<<2<<"Test String2"; // assigning :A = 2, :C = "Test String2" o<<3<<"Test String3"; // assigning :A = 3, :C = "Test String3" o.flush(); // executing PL/SQL block 3 times int a;
char b[32];
while(!o.eof()){ // not end-of-data o>>a>>b;
cout<<"A="<<a<<", B="<<b<<endl;
}
}int main()
{ otl_connect::otl_initialize(); // initialize OCI environment try{
db.rlogon("scott/tiger"); // connect to Oracle plsql(); // invoking PL/SQL block } catch(otl_exception& p){ // intercept OTL exceptions cerr<<p.msg<<endl; // print out error message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from Oracle return 0;
}
A=2, B=Test String1 A=3, B=Test String2 A=4, B=Test String3
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:f1<int>,:f2<char[31]>)",
// SQL statement db // connect object ); char tmp[32];
for(int i=1;i<=100;++i){
sprintf(tmp,"Name%d",i);
o.printf("%d %s",i,tmp); // write one row into stream } }void select()
{ otl_stream i(50, // buffer size "select * from test_tab where f1>=:f<int> and f1<=:f*2",
// SELECT statement db // connect object ); // create select stream int f1;
char f2[31];
i<<8; // assigning :f = 8 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i.scanf("%d %s",&f1,f2); // read one row from stream cout<<"f1="<<f1<<", f2="<<f2<<endl;
}
i<<4; // assigning :f = 4 // SELECT automatically re-executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i.scanf("%d %s",&f1,f2); // read one row from stream cout<<"f1="<<f1<<", f2="<<f2<<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 insert(); // insert records into table select(); // select records from table } catch(otl_exception& p){ // intercept OTL exceptions cerr<<p.msg<<endl; // print out error message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from Oracle return 0;
}
f1=8, f2=Name8 f1=9, f2=Name9 f1=10, f2=Name10 f1=11, f2=Name11 f1=12, f2=Name12 f1=13, f2=Name13 f1=14, f2=Name14 f1=15, f2=Name15 f1=16, f2=Name16 f1=4, f2=Name4 f1=5, f2=Name5 f1=6, f2=Name6 f1=7, f2=Name7 f1=8, f2=Name8
#include <iostream.h> #include <stdio.h> #include <otl.h> otl_connect db; // connect objectvoid insert()
// insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:f1<float>,:f2<char[31]>)",
// SQL statement db // connect object ); char tmp[32];
for(int i=1;i<=100;++i){
sprintf(tmp,"Name%d",i);
o<<(float)i<<tmp;
}
}void select()
{ otl_stream i(50, // buffer size "begin " " open :cur for " " select * " " from test_tab " " where f1>=:f<int> and f1<=:f*2; " "end;",
// PL/SQL block returns a referenced cursor db, // connect object ":cur" // referenced cursor placeholder name ); // create select stream int f1;
char f2[31];
i<<8; // assigning :f = 8 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i>>f1>>f2;
cout<<"f1="<<f1<<", f2="<<f2<<endl;
}
i<<4; // assigning :f = 4 // SELECT automatically re-executes when all input variables are // assigned. First portion of out rows is fetched to the buffer while(!i.eof()){ // while not end-of-data i>>f1>>f2;
cout<<"f1="<<f1<<", f2="<<f2<<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 insert(); // insert records into table select(); // select records from table } catch(otl_exception& p){ // intercept OTL exceptions cerr<<p.msg<<endl; // print out error message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error } db.logoff(); // disconnect from Oracle return 0;
}
f1=8, f2=Name8 f1=9, f2=Name9 f1=10, f2=Name10 f1=11, f2=Name11 f1=12, f2=Name12 f1=13, f2=Name13 f1=14, f2=Name14 f1=15, f2=Name15 f1=16, f2=Name16 f1=4, f2=Name4 f1=5, f2=Name5 f1=6, f2=Name6 f1=7, f2=Name7 f1=8, f2=Name8
OTL provides two otl_stream based STL-compliant iterator classes:
These two iterator classes make it possible to combine the power of generic programming and Oracle into compact, reliable, top performance and easy-to-maintain C++ database applications.
It would be cool to develop a PL/SQL-to-C++ converter which would use STL container classes (to efficiently implement PL/SQL tables) and OTL to take advantage of straight OCI in order to efficiently implement Oracle cursor handling. Also, converting interpreted code into code compiled into native machine instructions is not such a bad idea.
#include <iostream.h> #include <stdio.h> #include <otl_iter.h> #include <vector.h> #include <hash_map.h> #include <iterator.h> otl_connect db; // connect object // row container class class row{
public:
int f1;
char f2[32];
// default constructor row(){f1=0; f2[0]=0;} // destructor ~row(){} // copy constructor row(const row& row)
{
f1=row.f1;
strcpy(f2,row.f2);
}
// assignment operator row& operator=(const row& row)
{
f1=row.f1;
strcpy(f2,row.f2);
return *this;
}
};
// redefined operator>> for reading row& from otl_stream otl_stream& operator>>(otl_stream& s, row& row)
{
s>>row.f1>>row.f2;
return s;
}
// redefined operator<< for writing row& into otl_stream otl_stream& operator<<(otl_stream& s, const row& row)
{
s<<row.f1<<row.f2;
return s;
}
// redefined operator<< writing row& into ostream ostream& operator<<(ostream& s, const row& row)
{
s<<"f1="<<row.f1<<", f2="<<row.f2;
return s;
}void insert()
// insert rows into table { otl_stream o(50, // buffer size "insert into test_tab values(:f1<int>,:f2<char[31]>)",
// SQL statement db // connect object ); row r; // single row buffer vector<row> vo; // vector of rows // populate the vector for(int i=1;i<=100;++i){
r.f1=i;
sprintf(r.f2,"Name%d",i);
vo.push_back(r);
}
cout<<"vo.size="<<vo.size()<<endl;
// insert vector into table copy(vo.begin(), vo.end(), otl_output_iterator<row>(o)
);
}void select()
{ otl_stream i(50, // buffer size "select * from test_tab where f1>=:f<int> and f1<=:f*2",
// SELECT statement db // connect object ); // create select stream vector<int> inp_par; // vector of 1 element to demonstrate OTL iterators vector<row> v; // vector of rows // assigning :f = 8 // this example demonstrates how both input // and output iterators may be attached to the // same otl_stream inp_par.push_back(8); // populate the vector with one element cout<<"inp_par.size="<<inp_par.size()<<endl;
// copy the vector into the input variable of the select statement copy(inp_par.begin(),inp_par.end(),otl_output_iterator<int>(i));
// SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer // copy all rows to be fetched into the vector copy(otl_input_iterator<row,ptrdiff_t>(i),
otl_input_iterator<row,ptrdiff_t>(),
back_inserter(v));
cout<<"Size="<<v.size()<<endl;
// send the vector to cout copy(v.begin(), v.end(), ostream_iterator<row>(cout, "\n"));
// clean up the vector v.erase(v.begin(),v.end()); // OTL traditional technique i<<4; // assigning :f = 4 // SELECT automatically executes when all input variables are // assigned. First portion of out rows is fetched to the buffer // copy all rows to be fetched to the vector copy(otl_input_iterator<row,ptrdiff_t>(i), otl_input_iterator<row,ptrdiff_t>(), back_inserter(v)); cout<<"Size="<<v.size()<<endl;
// send the vector to cout copy(v.begin(), v.end(), ostream_iterator<row>(cout, "\n"));
}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 insert(); // insert records into table select(); // select records from table } catch(otl_exception& p){ // intercept OTL exceptions cerr<<p.msg<<endl; // print out error message if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL statement associated with the error } db.logoff(); // disconnect from Oracle return 0;
}
In order to obtain a copy of the OTL header files,
send email to
Here is the list of the OTL header files:
The OCI7 standard header files are as follows:
The OCI8 standard header files are as follows:
If you want to use the STL-compliant iterators then you are going to need the Standard Template Library (adaption by Boris Fomitchev) or the original STL SGI, version 3.11.
The following OTL exceptions can be raised by the OTL functions: