# One-Page Oracle API, Oracle Call Interface Template Library (OTL), Version 2.0.0

Sergei Kuchin, email: skuchin@ispwest.com, skuchin@gmail.comgmail

    Copyright (C) Sergei Kuchin, 1996, 1997, 1998
    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.

# Table of Contents

- 1. Introduction
- 1.1. SQL streams
- 2. One-page Oracle API
  - 2.1. Connect object
  - 2.2. otl_stream class
    - 2.2.1. Stream bind variables declaration
  - 2.3. Exception handling
  - 2.4. Constant SQL statement or PL/SQL block
  - 2.5. Examples
- 3. STL-compliant iterators for otl_stream
  - 3.1. Example
- Appendix A. How to download the source code
- Appendix B. OTL exception list

# 1. Introduction

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:

- OTL for Oracle 7 uses native OCI7
- OTL for Oracle 8 uses native OCI8

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.

## 1.1. SQL stream classes

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.

# 2. One-page Oracle API

## 2.1. Connect object

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.

    class otl_connect{
    public:

- Constants to initialize OCI. oci_threaded is needed in case of multithreaded program. oci_default is the default mode.

       enum {oci_default,oci_threaded};

- Initialize OCI program. This function needs to be called only once before connecting to the database for the first time. The function is static in class.

      static void otl_initialize(const int mode=oci_default);

- Create "connect" object.

      otl_connect();

- Create "connect" object and connect to Oracle using the "connect_str" connect string.

       otl_connect(const char* connect_str);

- Destructor

      ~otl_connect();

- Concurrent logon; OCI application is allowed to have more than one concurrent logon. Returns 1 on success, 0 on failure.

      int rlogon(const char* connect_str);

- Disconnect from / log off Oracle. Returns 1 on success, 0 on failure.

      int logoff(void);

- Commit current transaction. Returns 1 on success, 0 on failure.

      int commit(void);

- Roll back current transaction. Returns 1 on success, 0 on failure.

      int rollback(void);

    };

## 2.2. otl_stream class

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.

    class otl_stream{
    public:

- General conctructor. SQL statement is parsed, all host input and output host variables are automatically allocated and bound. This constructor allows the user to use extended placeholder declarations.

      otl_stream(
      	   short arr_size, // host array size
      	   const char* sqlstm, // SQL statement
      	   otl_connect& db, // connect object
                 const char* ref_cur_placeholder=0
      		   // reference cursor placeholder, e.g. ":cur"
      	  );

- Default constructor

      otl_stream();

- Destructor

      ~otl_stream();

- Test if all data has been already read from the stream

      int eof(void);

- Flush stream's output buffer. It actually means to execute the SQL statement as many times as rows entered to the output buffer. The stream is automatically flushed when the buffer gets full.

      void flush(void);

- Clean up buffer without flushing it.

      void clean(void);

- Rewind stream

      void rewind(void);

- Test if NULL was fetched from the stream

      int is_null(void);

- Set "auto-commit" flag. When the buffer is flushed, current transaction is automatically commited, if the flag is set. By default, the flag is set. In order to prevent current transaction from "auto-committing", unset the flag using this function.
  This auto-commit flag has nothing to do with the Oracle auto-commit mode. This auto-commit is otl_stream specific. If it is more convenient to have "auto-commit off" by default then it makes sense to derive another stream class from otl_stream and unset the flag in constructors of the derived class.

      void set_commit(int auto_commit=0);

- Open stream

      void open(
      	  short arr_size, // host array size
      	  const char* sqlstm, // SQL statement
      	  otl_connect& db, // connect object
                const char* ref_cur_placeholder=0
      	     // reference cursor placeholder, e.g. ":cur"
      	 );

- Close stream

      void close(void);

- Test if the stream was opened okay

      int good(void);

- Read objects from stream

      otl_stream& operator>>(char& c);
      otl_stream& operator>>(unsigned char& c);
      otl_stream& operator>>(char* s);
      otl_stream& operator>>(unsigned char* s);
      otl_stream& operator>>(int& n);
      otl_stream& operator>>(unsigned& u);
      otl_stream& operator>>(short& sh);
      otl_stream& operator>>(long int& l);
      otl_stream& operator>>(float& f);
      otl_stream& operator>>(double& d);

- Write objects into stream

      otl_stream& operator<<(const char c);
      otl_stream& operator<<(const unsigned char c);
      otl_stream& operator<<(const char* s);
      otl_stream& operator<<(const unsigned char* s);
      otl_stream& operator<<(const int n);
      otl_stream& operator<<(const unsigned u);
      otl_stream& operator<<(const short sh);
      otl_stream& operator<<(const long int l);
      otl_stream& operator<<(const float f);
      otl_stream& operator<<(const double d);
      otl_stream& operator<<(const otl_null n);
        // write Oracle NULL into stream. otl_null is a dummy class
        // which has only a empty default constructor. The only purpose
        // of creating this class was providing a function to write
        // NULL into the database

- C-style printf/scanf functions

      void printf(const char* fmt,...);
      void scanf(const char* fmt,...);

  The following format specifiers are supported:

  - %d -- int
  - %u -- unsigned
  - %ld -- long int
  - %f -- float
  - %lf -- double
  - %c -- char
  - %s -- string
  - %N -- specifier for writing NULL into streams

    };

## 2.2.1. Stream bind variables declaration

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:

- int
- unsigned
- short
- long -- (long integer)
- float
- double
- char[length] (length >= 3 & length <= 32545)

For PL/SQL blocks, special qualifiers are introduced to distinguish between input and output variables:

- in -- input variable
- out -- output variable
- inout -- input/output variable

  #### Examples

  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:

        insert into tab1 values(:f1< double >, :f2< char [ 32 ] > , :f3< int>);
        :rc< int, out > := ...;

  ## 2.3. Exception handling

  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.

      class otl_exception{
      public:

  - This enum defines two constants which may be used in the direct_exec function

        enum{ disabled, enabled };

  - Create exception out of Logon Descriptor Area (Oracle 7) or out of OCI error handle (Oracle 8)

        otl_exception(Lda_Def& lda,const char* sqlstm=0);

                             or

        otl_exception(OCIError* errhp,const char* sqlstm=0)

  - Create exception from amsg, acode and sqlstm

        otl_exception(const char* amsg,const int acode,const char* sqlstm=0);

  - Copy constructor

        otl_exception(const otl_exception& p);

  - Default constructor

         otl_exception();

  - error message buffer

        unsigned char msg[1000];

  - error code

        int code;

  - SQL statement or PLSQL block that caused the error

         unsigned char* stm_text;

      };

  ### Example

       try{

        otl_stream s(50, // fetch 50 rows per one fetch operation
      	      "select state_name, state_code "
                    "from state "
                     "where state_name like :v1<char[33]>",
                     db // connect object
                   );
        char name[33];
        int  code;

        s<<"M%";
        while(!s.eof)){
         s>>name>>code;
         cout<<"State="<<name<<", Code="<<code<<endl;
        }

       }catch(otl_exception& p){ // intercept exception

        cerr<<p.code<<endl; // print out error code
        cerr<<p.msg<<endl; // print out error message
        cerr<<p.stm_text<<endl; // print out SQL that caused the error

       }

  ## 2.4. Constant SQL statement or PL/SQL block

  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::direct_exec
         (db, // connect object
          "create table test_tab(f1 number, f2 varchar2(30))"
          );  // create table

  otl_cursor is one of OTL internal classes. There is another format of the direct_exec function call:

        otl_cursor::direct_exec
         (db, // connect object
          "drop table test_tab", // SQL statement or PL/SQL block
          otl_exception::disabled // disable OTL exceptions,
                                  // in other words, ignore any
                                  // Oracle error
         ); // drop table

  ## 2.5. Examples

  ### Example 1 (with otl_stream class)

  #### Source code

  ####

  ####

  ####

      #include <iostream.h>
      #include <stdio.h>
      #include <otl.h>

      otl_connect db; // connect object

      void 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;

      }

  #### Output

      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

  ### Example 2 (with PL/SQL block)

  #### Source code

  ####

  ####

      #include <iostream.h>
      #include <stdio.h>
      #include <otl.h>

      otl_connect db; // connect object

      void 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;
      }

  #### Output

      A=2, B=Test String1
      A=3, B=Test String2
      A=4, B=Test String3

  ### Example 3 (with printf/scanf functions)

  #### Source code

  ####

  ####

  ####

      #include <iostream.h>
      #include <stdio.h>
      #include <otl.h>

      otl_connect db; // connect object

      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
      	     );
       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;

      }

  #### Output

      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

  ### Example 4 (with otl_stream class and referenced cursor)

  #### Source code

  ####

  ####

  ####

      #include <iostream.h>
      #include <stdio.h>
      #include <otl.h>

      otl_connect db; // connect object

      void 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;

      }

  #### Output

      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

  # 3. STL-compliant iterators for otl_stream

  OTL provides two otl_stream based STL-compliant iterator classes:

  - otl_output_iterator
  - otl_input_iterator

  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.

  ## 3.1. Example

  #### Source code

  ####

  ####

  ####

      #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;

      }

  #### Output

      vo.size=100
      inp_par.size=1
      Size=9
      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
      Size=5
      f1=4, f2=Name4
      f1=5, f2=Name5
      f1=6, f2=Name6
      f1=7, f2=Name7
      f1=8, f2=Name8

  # Appendix A. How to download the source code

  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:

  - otl7.h -- the OTL source code for Oracle 7.
  - otl8.h -- the OTL source code for Oracle 8. Save the file as otl.h.
  - otl_iter.h -- STL-compliant iterators for otl_stream

  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:

  - ociapr.h
  - ocidfn.h
  - ocikpr.h
  - oratypes.h

  The OCI8 standard header files are as follows:

  - nzerror.h
  - nzt.h
  - oci.h
  - ociap.h
  - ociapr.h
  - ocidef.h
  - ocidem.h
  - ocidfn.h
  - ociextp.h
  - ocikp.h
  - ocikpr.h
  - oratypes.h
  - ori.h
  - orid.h
  - orl.h
  - oro.h
  - ort.h
  - tnsapi.h

  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.

  # Appendix B. OTL exception list

  The following OTL exceptions can be raised by the OTL functions:

  Code=32000: Incompatible data types in stream operation
    Cause: The data type of a variable used in the current stream operation is not compatible with the declared stream format.
    Action: Check placeholders and their data types declaration.
  Code=32001: Row must be full for flushing output stream
    Cause: Stream is open for output and has a format of output rows. An output row is a tuple of all output variables put together. The current output row is not filled yet but the flush function is invoked. The stream buffer cannot be flushed until the current row of the output buffer is full.
    Action: Fill the row first, then flush the stream.
  Code=32004: Not all input variables have been initialized
    Cause: stream has input variables but not all the variables have been initialized. An attempt to read data from the stream was made.
    Action: Assign all the input variables first.
  Code=32004: No input variables have been defined in SQL statement
    Cause: Stream has no input variables. An attempt to write objects to the stream via one of the << operators was made.
    Action: Do not call the << operators for streams which have no input variables defined.