Sergei Kuchin, email: skuchin@ispwest.com, skuchin@gmail.comgmail
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 inlinr 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.
OTL introduces the concept of SQL streams. The idea here is to combine streams and SQL. Any SQL statement or PL/SQL block can be treated as a functional element with input/output parameters. There are functions to put objects into a stream, that is, to assign values to input variables of the SQL statement. Also, there are functions to get objects from the stream, that is, to get values from output variables of the SQL statement.
When values of all input variables of the functional element are filled out then the element is executed. Resulting values are assigned to the output variables right after the execution. Sets of input and output variables are allowed to overlap.
Logically, a SQL stream is a structured stream with input and output rows. The format of the input row is defined by a set of output variables of the stream. Similarly, the output row is defined by input variables of the stream. When objects are written into the stream, values are actually assigned to the input variables. Likewise, when objects are read from the stream, values are read from the output variables of the stream.
SQL streams are similar to buffered files. A SQL statement or PL/SQL block is opened as an ordinary buffered file. The logic of the SQL stream operations remains the same as the file operations with the only exception -- the SQL stream has separate input and output buffers which may overlap.
The SQL stream has a flush function for flushing its input buffer when the buffer gets full and a collection of >> and << operators for reading and writing object of different data types. The most important advantage of the SQL streams is their unified interface to SQL statements and PL/ SQL blocks of any kind. This means that the application developer needs to remember just a few syntactical constructs and function names which he already got familiar with when he started working with C++ streams.
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.
This class encapsulates the Oracle Call Interface connect functions: connect, commit, roll back, etc. In other words, otl_connect is the class for creating "connect" objects.
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:
This section explains in detail how to declare bind variables (or extended placeholders) in the SQL streams.
A SQL statement or PL/SQL block may have placeholders which are usually connected with the corresponding bind variables in the program. In Pro*C, the user needs to declare such variables directly in the program. OTL provides the same functionality in another way. There is a small parser which parses a SQL statament or PL/SQL block declaration and allocates corresponding bind variables dynamically inside the stream.
The following data types for extneded placeholder declarations are available:
For PL/SQL blocks, special qualifiers are introduced to distinguish between input and output variables:
Here is some examples:
begin
:rc<int,out> := my_func(:salary<float,in>,
:ID<int,inout>,
:name<char[32],out>
);
end;
Invoke the my_func function; return the function result into the :rc variable; the function has three parameters: salary (input), ID (iput/output), name (output)
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.
In the extended placeholder declaration, spaces in the data type section and in the access qualifier section ARE NOT allowed. The following code is invalid:
In case of Oracle failure or inconsistent use of SQL streams, exceptions of the otl_exception type are raised by the library functions. The main advantage of using this exception handling mechanism is that exceptions can be processed in one catch block, instead of checking return codes from every library function call.
enum{ disabled, enabled };
SQL statement or PL/SQL block is considered to be constant if it does not have any bind variables. OTL has a static (in class) function to execute constant statements or blocks, e.g.
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 skuchin@ispwest.com, skuchin@gmail.comgmail
Here is the list of the OTL header files:
Besides, you need to have standard OCI header files and object libraries. The location of the files and libraries is specific to the operating system. See the Oracle manuals for more details.
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: