Oracle Call Interface Template Library 1.0.5 (OTL), Pro*OTL / Pre-Pro*C preprocessor 1.0.0 (PPC)

Sergei Kuchin, email: skuchin@aceweb.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

 protected: };

Class otl_variable

This is the OTL template variable class. It is the base class for constructing specialized host variable classes.

template <class T, int atype> class otl_variable: public otl_generic_variable{ public: };

Class otl_array

This is the OTL template host array class. It is the base class for constructing specialized template array classes.

template <class T, int atype, short size> class otl_array: public otl_generic_variable{ public: };

3.1.1. Specialized host variable classes

3.1.2. Specialized host array classes

3.2. Oracle Call Interface "wrapper"

Class otl_exception

This is the OTL exception class. Exceptions of this type are raised by the library functions (default mode), unless it is prohibited explicitly in the otl_connect or otl_cursor class constructors. In case of disabled exceptions OTL functions return codes and it is the user's responsibility to check out the codes and handle errors. 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: };

Class otl_object

This class is a parent of the otl_cursor and otl_connect classes. Its children inherit the following two properties:

class otl_object{ public: protected: };

Class otl_connect

This class encapsulates the Oracle Call Interface functions which have Logon Descriptor Area as their first parameter. In other words, otl_connect is the class for creating "connect" objects.

class otl_connect: public otl_object{ public: };

Class otl_column_desc

This class is data structure which contains a select item (column) descriptive information. The information may be obtained by the otl_cursor::describe_column function call (see the otl_cursor class). class otl_column_desc{ public: };

Class otl_cursor

This class is a general-purpose cursor class.

class otl_cursor: public otl_object{ public: };

Class otl_select_cursor

This class is a cursor class, specialized for SELECT statements. class otl_select_cursor : public otl_cursor{ public: };

Class otl_dynamic_variable

This class is used in the otl_select_stream class to dynamically allocate a list of output columns of SELECT statement and any other automatically created bind variables (e.g. in a SQL stream).

class otl_dynamic_variable: public otl_generic_variable{ public: };

Class otl_err_info

This is the OTL error info class. It is intended for using in case of manual error handling. The class allows to get more detailed error information about the current Oracle error. class otl_err_info: public otl_exception{ public: };

3.3. OTL stream interface

In OTL, SQL streams are introduced. The idea here is to combine streams and SQL. Such a combination provides new quality and simplicity in programming interface to SQL. The Oracle Array Interface naturally transforms into buffered stream operations.

The SQL streams are intended for SQL or PL/SQL statements which have input and/or output bind variables. Any statement 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. Also, there are functions to get objects from the stream, that is, to get values from output variables.

+--> I1 I2 ... In | | | | | V V V | +------------------+ +--| SQL statement or | | PL/SQL block | +-+-----+------+---+ | | | V V V O1 O2 ... Ok

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 (see the picture).

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 in C++ has a flush function for flushing its input buffer when the buffer gets full and a collection of >> and << operators for reading and writing objects 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 mean that application developers need to remember just a few syntactical constructs and function names which they already got familiar with when they 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 intercept the exception and prevent the program from aborting, wrap up the OTL stream code with the corresponding try & catch block.

For more detail on the stream class hierarchy, see Appendix A.

Class otl_select_stream

This is the OTL select stream class. The user does not need to manually attach output columns to SELECT statement because the statement is automatically parsed and the output columns are allocated in the class constructor.

This class may issue the following otl_exceptions:

class otl_select_stream: public otl_select_cursor{ public: };

Class otl_out_stream

This is the OTL output class. This class is used for the following SQL statements:

This class may issue the following otl_exceptions:

class otl_out_stream: public otl_cursor{ public: };

Class otl_inout_stream

This is the OTL input/output stream class. It is used primarily for PL/SQL blocks with input and output parameters. Though, this stream class can be used for SQL statements and PL/SQL blocks with input or output parameters only.

This class may issue the following otl_exceptions:

class otl_inout_stream: public otl_out_stream{ public: };

Class otl_stream

This is the OTL stream class. It is a general-purpose and most advanced stream class, unified for streams of all types. This class may issue the following otl_exceptions:

class otl_stream{ public: };

Stream bind variable declarations

This section explains in detail how to declare bind variables (or extended place-holders) 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 place-holder declarations are available:

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 a number of 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.

    3.4. Prosto*C

    The name Prosto*C is originated in the author's native language -- "prosto" means "simple". Prosto*C is supposed to provide a simplified set of procedures for interfacing with SQL or PL/SQL. In Prosto*C, the mechanism of handling errors is slightly different from the otl_exception mechanism. Each connect object is supplied with the error handler -- a procedure, which is invoked each time when an error occurs (see also 2.1.11.).

    Prosto*C provides a set of functions which is very similar to the C "stdio" interface: scanf(), printf(), etc.

    Here is the list of Prosto*C functions:


    4. Pro*OTL / Pre-Pro*C preprocessor (PPC)

    PPC is a preprocessor which reads a directive file on input and can generate both Pro*C and OTL code on output. When the preprocessor starts up, it connects to the database, parses directives and then generates the output code. The output code consists of a Pro*C file, a C++ file with OTL function calls and a header file with the prototypes of the functions, generated by PPC.

    The directives fall into three main categories:


    4.1. Getting started with PPC

    Let's consider similar examples as described in Chapter 2: examples 8 and 9. In the "scott/tiger" user, create the following table, using SQLPlus:

    create table test_tab(f1 number, f2 varchar2(30));

    Let's assume that the example comprises of two modules: main and auxiliary. The main module has the main function which connects to the database and call functions from the auxiliary module. Source code of the auxiliary module is generated by the PPC preprocessor from the directive file. The directive file is unified for both Pro*C and C++, but the output for Pro*C and C++ is different (see examples below). The interface functions are the same and can be used both in Pro*C and C++.

    Here is the source code of the directive file (ppc_test.ppc):

    /* ppc_test.ppc - directive file; ppc_test.h - generated header file with interface functions and data structures; ppc_test.C - generated C++ module with OTL function calls; ppc_test.pc - generated Pro*C module; */ #include <ppc_test.h> /* generated header file */ /* PPC standard prolog for an auxiliary (not main) module */ #sql-init-module #sql-str-type <CSTR,1> /* type equivalence directive */ /* SELECT statement directive. "Sel" is the directive label. 50 is the internal host arrays size. */ #sql-select <Sel,50> SELECT * FROM TEST_TAB WHERE F1>=:F<int> AND F1<=:F*2 ORDER BY F1 ## /* ## is a directive terminator */ /* "Output" statement directive. "Ins" is the directive label. 50 is the internal host arrays size. */ #sql-out-stm <Ins,50> INSERT INTO TEST_TAB ( F1, F2 ) VALUES ( :F1<float>, :F2<char[31]> ) ## /* "Arbitrary PL/SQL block" directive. "PL" is the directive label. 1 is a dummy parameter which does not matter in the current release of PPC. Put 1 for compatibility with the future versions. :A is IN/OUT parameter; :B is OUT parameter; :C is IN parameter; */ #sql-plsql <PL,1> BEGIN :A<int,inout> := :A+1; :B<char[31],out> := :C<char[31],in>; END; ## #ifdef __cplusplus /* Function for C++. In the main C++ module, the user needs to call this function, in order to pass over a pointer to the actual database connect object into the C++ module, generated by PPC. This function can be eliminated if only Pro*C is used. */ void assign_db(otl_connect* adb) { db=adb; // db is a static (in the module) pointer // to the database connect object } /* Function for C++. In the main C++ module, the user needs to call this function just before disconnecting from the database, in order to close the static "hot" cursor in this file. This function can be eliminated if only Pro*C is used. */ void close_hotcur(void) { hotcur.close(); // close static hot cursor } #endif Here is the header file (ppc_test.h), generated from the directive file by PPC: #ifndef __PPC_TEST_H #define __PPC_TEST_H #ifdef __cplusplus extern "C"{ #endif /* C-structure, corresponding to the "Sel" statement. The SELECT list has been automatically extracted from the database dictionary and the structure generated. The structure is a container for one output row of the "Sel" statement. */ struct struct_Sel{ double F1; /* F1 number */ short F1_IND; /* F1's indicator */ char F2[31]; /* F2 varchar2(30) */ short F2_IND;/* F2's indicator */ }; typedef struct struct_Sel Sel; /* typedef declaration */ void Sel_open( int F /* F is the integer input host variable :F */ ); /* Open the "Sel" statement */ void Sel_close(void); /* Close the "Sel" statement */ int Sel_get(Sel* out); /* Get one row and put it into the "Sel" structure */ void Ins_open(int auto_commit); /* Open the "Ins" statement. "auto_commit" is the auto-commit flag. If the flag is set then: - commit transaction right after the internal host arrays get full and the "Ins" statement is executed; - commit transaction right after the "Ins" statement is closed; */ void Ins_put( float F1, /* input float host variable :F1 */ char* F2 /* input char[31] host variable :f2 */ ); /* Put one row into the "Ins" statement's internal buffer. The statement is automatically executed when the buffer gets full. */ void Ins_flush(void); /* "Flush" internal buffer, no matter how full it is. This means that the "Ins" statement executes as many times as rows the buffer contains. If the "auto_commit" flag was set, then the current transaction commits. */ void Ins_close(void); /* Close the "Ins" statement: call the Ins_flush() function, then deallocate all internal resources and quit. */ void PL_exec( int* A, /* IN/OUT integer parameter :A */ char* B,/* OUT char[31] parameter :B */ char* C /* IN char[31] parameter :C ); /* Execute the "PL" PL/SQL block */ #ifdef __cplusplus } #endif #endif

    For more information on this example, see Appendix F.

    Example in Pro*C

    In this section, source code of the Pro*C main module (ppc_main.pc) is given. It needs to be preprocessed by Pro*C, compiled by C and linked with the ppc_test.pc module (see the above example).

    Source code
    #include <ppc_test.h> #include <stdio.h> EXEC SQL INCLUDE SQLCA; typedef char CSTR[80]; EXEC SQL BEGIN DECLARE SECTION; EXEC SQL TYPE CSTR IS STRING(80); CSTR UserId; EXEC SQL END DECLARE SECTION; /* Define error handler */ void sqlerror(void) { EXEC SQL WHENEVER SQLERROR CONTINUE; fprintf(stderr,"\n%s\n",sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK RELEASE; exit(1); } EXEC SQL WHENEVER SQLERROR DO sqlerror(); void Insert() /* insert rows into table */ {int i; Ins_open(1); /* open "Ins" statement with "auto_commit" flag set */ for(i=0;i<100;++i){ char F2[32]; sprintf(F2,"Name%d",i); Ins_put(i,F2); /* write one row into the database */ } Ins_close(); /* close statement */ } /* insert */ void Select() /* select rows from table */ { Sel p; /* one row containter */ Sel_open(8); /* open "Sel" statement with the :F input parameter = 8 */ /* fetch rows from the table */ while(!Sel_get(&p)){ /* not end-of-data */ printf("f1=%g, f2=%s\n",p.F1,p.F2); } Sel_close(); /* close the statement */ /* re-open "Sel" statement with :F = 4 */ Sel_open(4); /* fetch rows from the table */ while(!Sel_get(&p)){ /* not end-of-data */ printf("f1=%g, f2=%s\n",p.F1,p.F2); } Sel_close(); /* close the statement */ } /* select */ void plsql() /* execute the "PL" PL/SQL block */ { int a; char b[31]; char c[31]; /* assigning :A = 1, :C = "Test String1" */ a=1; strcpy(c,"Test String1"); PL_exec(&a,b,c); /* execute the block */ printf("A=%d, B=%s\n",a,b); /* print out results */ /* assigning :A = 2, :C = "Test String2" */ a=2; strcpy(c,"Test String2"); PL_exec(&a,b,c); /* execute the block */ printf("A=%d, B=%s\n",a,b); /* print out results */ /* assigning :A = 3, :C = "Test String3" */ a=3; strcpy(c,"Test String3"); PL_exec(&a,b,c); /* execute the block */ printf("A=%d, B=%s\n",a,b); /* print out results */ } /* plsql */ int main() { strcpy(UserId,"scott/tiger"); EXEC SQL CONNECT :UserId; /* connect to Oracle as scott/tiger */ EXEC SQL TRUNCATE TABLE TEST_TAB; /* truncate table */ Insert(); /* insert rows */ Select(); /* select rows */ plsql(); /* execute PL/SQL block */ EXEC SQL COMMIT WORK; /* disconnect from Oracle */ } /* main */
    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 A=1, B=Test String1 A=2, B=Test String2 A=3, B=Test String3

    Example in C++

    In this section, source code of the C++ main module (ppc_main.C) is given. It needs to be compiled by C++ and linked with the ppc_test.C module (see the above example).

    Source code
    #include <ppc_test.h> #include <stdio.h> #include <iostream.h> #include <otl.h> otl_connect db; // connect object void Insert() // insert rows into table { Ins_open(1); // open "Ins" statement with "auto_commit" flag set for(int i=0;i<100;++i){ char F2[32]; sprintf(F2,"Name%d",i); Ins_put(i,F2); // write one row into the database } Ins_close(); // close statement } /* insert */ void Select() // select rows from table { Sel p; // one row container Sel_open(8); // open "Sel" statement with the :F input parameter = 8 // fetch rows from the table while(!Sel_get(&p)){ // not end-of-data printf("f1=%g, f2=%s\n",p.F1,p.F2); } Sel_close(); // close the statement Sel_open(4); // re-open the statement with :F = 4 // fetch rows from the table while(!Sel_get(&p)){ // not end-of-data printf("f1=%g, f2=%s\n",p.F1,p.F2); } Sel_close(); // close the statement } /* select */ void plsql() // execute the "PL" PL/SQL block { int a; char b[31]; char c[31]; // assigning :A = 1, :C = "Test String1" a=1; strcpy(c,"Test String1"); PL_exec(&a,b,c); // execute the block printf("A=%d, B=%s\n",a,b); // print out results // assigning :A = 2, :C = "Test String2" a=2; strcpy(c,"Test String2"); PL_exec(&a,b,c); // execute the block printf("A=%d, B=%s\n",a,b); // print out results // assigning :A = 3, :C = "Test String3" a=3; strcpy(c,"Test String3"); PL_exec(&a,b,c); // execute the block printf("A=%d, B=%s\n",a,b); // print out results } /* plsql */ // define prototypes of the assign_db and close_hotcur functions // from the ppc_test.C module. extern "C" void assign_db(otl_connect* db); extern "C" void close_hotcur(void); int main() { try{ db.rlogon("scott/tiger"); // connect to Oracle as scott/tiger assign_db(&db); // initialize internal pointer to the actual connect // object in the ppc_test.C module otl_cursor::direct_exec(db,"truncate table test_tab"); // truncate table Insert(); // insert rows Select(); // select rows plsql(); // execute PL/SQL block }catch(otl_exception& p){ // intercept OTL exceptions cout<<p.msg<<endl; // print out error message } close_hotcur(); // close the hot cursor from ppc_test.C db.logoff(); // disconnect from Oracle return 0; } /* main */
    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 A=1, B=Test String1 A=2, B=Test String2 A=3, B=Test String3
    Notes

    It is possible to encapsulate all database functionality in separate Pro*C and PPC modules and use the modules without using OTL. The user can define Pro*C connect and disconnect functions separately in a Pro*C file, together with the other "handmade" Pro*C procedures. Additionally, a few directive files can be defined to automatically generate Pro*C code. Then, all this stuff can be "objectified" (encapsulated) in C++ classes and the classes can be used in the C++ main module.

    The user needs to keep in mind the technique of invoking plain C functions from C++.

    4.2. Directives

    PPC source code files consist of directives which may be mixed with real code in plain C, C++ or Pro*C (non-directive code is not processed and remains intact). The directive starts with # at the beginning of line. Some directives have names, arguments and special terminators, the other -- do not. By format, the directives are similar to the C preprocessor commands. Inside the directive body, extended place-holder declarations are allowed.

    4.2.1. #sql-select

    This is the "SELECT" directive. On output, it generates a set of functions to select rows according to the given SELECT statement. The directive format is as follows:

    #sql-select <Label,BufSize> end-of-line ... <SELECT Statement> ... ## end-of-line

    <Label>

    Statement label

    <BufSize>

    Defines the size of internal host arrays, attached to the SELECT statment

    <SELECT Statement>

    SELECT statement body. Can be multi-line.

    ##

    SELECT statement terminator. Starts at the beginning of line


    Example

    #sql-select <Sel,50> SELECT * FROM TEST_TAB WHERE F1>=:F<int> AND F1<=:F*2 ORDER BY F1 ##

    Generated code

    struct struct_<Label>{ ... <SELECT list items> ... }; /* One output row container */ typedef struct struct_<Label> <Label>; void <Label>_open( /* open statement */ ... <Input variable list> .. ); void <Label>_close(void); /* close statement */ int <Label>_get(<Label>* out); /* get one rows from the fetch sequence */

    For more detail, see Appendix F and "Getting Started with PPC".

    4.2.2. #sql-out-stm

    This is the "output" directive. It is called "output" similar to the OTL streams. A stream is called output, when the user can write objects into the stream. This directive is used for:

    On the output, the directive generates a set of functions to write rows to the database, according to the given SQL statement or PL/SQL block. The directive format is as follows:

    #sql-out-stm <Label,BufSize> end-of-line ... <SQL Statement or PL/SQL block with input parameters only> ... ##

    <Label>

    Statement label

    <BufSize>

    Defines the size of internal host arrays, attached to the statment

    <SQL Statement or PL/SQL block with input parameters only>

    Statement body. Can be multi-line.

    ##

    Statement terminator. Starts at the beginning of line


    Example

    #sql-out-stm <Ins,50> INSERT INTO TEST_TAB ( F1, F2 ) VALUES ( :F1<float>, :F2<char[31]> ) ##

    Generated code

    void <Label>_open(int auto_commit); /* open statement */ void <Label>_put( /* write one row */ ... <Parameter list> ... ); void <Label>_flush(void); /* "flush" internal buffer */ void <Label>_close(void); /* close statement */

    For more detail, see Appendix F and "Getting Started with PPC".

    4.2.3. #sql-plsql

    This is the "arbitrary PL/SQL block" directive. On the output, the directive generates the "exec" function to execute the PL/SQL block, given in the directive. The directive format is as follows:

    #sql-plsql <Label,BufSize> end-of-line ... <PL/SQL block> ... ## end-of-line

    <Label>

    Statement label

    <BufSize>

    Defines the size of internal host arrays, attached to the statment. In the current release of PPC should be always 1.

    <PL/SQL block>

    Statement body. Can be multi-line.

    ##

    Statement terminator. Starts at the beginning of line


    Example

    #sql-plsql <PL,1> BEGIN :A<int,inout> := :A+1; :B<char[31],out> := :C<char[31],in>; END; ##

    Generated code

    void <Label>_exec( /* execute PL/SQL block */ ... <Parameter list> ... );

    For more detail, see Appendix F and "Getting Started with PPC".

    4.2.4. #sql-init-module

    This is the "module's standard prolog" directive. On output, the directive generates a piece of code, typical for non-main modules.

    #sql-init-module end-of-line

    Example

    #sql-init-module

    Generated code

    For more detail, see Appendix F and "Getting Started with PPC".

    4.2.5. #sql-init-main

    This is the "main module's standard prolog" directive. On output, the directive generates a piece of code, typical for main modules.

    #sql-init-main end-of-line

    Example

    #sql-init-main

    Generated code

    For more detail, see Appendix F and "Getting Started with PPC".

    4.2.6. #sql-str-type

    This is the "string type equivalence" directive. It has effect only in Pro*C. The directive format is as follows:

    #sql-str-type <StringType,Flag> end-of-line

    <StringType>

    C-string type, defined as a typedef. PPC uses the "StringType" identifier in generating Pro*C internal string variables.

    <Flag>

    if <flag>==1 then the string type equivalence is enforced. if <flag>==0 then the string type equivalence is off.


    Example

    typedef char C_STR[256]; ... #sql-str-type <C_STR,1> ...

    Generated code

    None.

    4.3. Command line parameters

    All command line parameter are positional. Parameters inside [] are optional. The format of PPC command line is as follows:

    ppc <connect_string> <input_file> <proc-file> <h-file> <#define> [<macro-def-file> [<OTL-module>]]

    1. connect_string

    Database connect sting

    2. input_file

    Input PPC directive file

    3. proc-file

    Output Pro*C file

    4. h-file

    Output interface header file (contains PPC-generated external function prototypes and data structures

    5. #define

    #define for the interface header file

    6. macro-def-file

    File, containing macro definitions to be used with the OTL streams (see the file for more detail)

    7. OTL-module

    Output C++ module which contains OTL function calls


    Examples

    ppc scott/tiger sample.ppc sample.pc sample.h __SAMPLE_H ppc scott/tiger sample.ppc sample.pc sample.h __SAMPLE_H dummy.h sample.C

    5. Acknowledgements

    Vladimir Shipunov and Igor Galichin (Siberian Trade Bank, Novosibirsk, Russia) have discussed with me some ideas how to implement basic classes.

    Peter Muth, Hannelore Eisner, Achim Kraiss and other members of the VODAK team in GMD IPSI (Darmstadt, Germany) have given me good knowledge on Object Oriented Databases and I do not regret about the time I spent with them. The knowledge was very useful in the development of the OTL.

    Especially, I would like to thank Prof.Dr. Erich Neuhold who granted me a visiting researcher position in GMD IPSI.

    Sergei Trapeznikov's and my hard work on numerous Oracle projects at Siemens / Empros inspired me to develop PPC. I wish Sergei Trapeznikov all the best in his career at SDT.

    Many thanks to my wife Irina for her patience and understanding that this work is important to me.

    6. Bibliography

    Appendix A. OTL class hierarchy

    otl_object | +------>otl_connect | +------>otl_cursor | +------>otl_select_cursor | | | +------>otl_select_stream | | | otl_stream +------>otl_out_stream | +------>otl_inout_stream otl_generic_variable | +-->otl_dynamic_variable | +-->otl_variable | | | +--->otl_cstring | otl_varchar2 | otl_long | otl_varchar | otl_varraw | otl_raw | otl_long_raw | otl_char | otl_charz | otl_long_varchar | otl_long_varraw | +->otl_array | +---->otl_date_array otl_rowid_array otl_varnum_array otl_number_array otl_double_array otl_float_array otl_signed_char_array otl_short_int_array otl_int_array otl_long_int_array otl_unsigned_array otl_cstring_array otl_varchar2_array otl_long_array otl_varchar_array otl_varraw_array otl_raw_array otl_long_raw_array otl_char_array otl_charz_array otl_exception | +---->otl_err_info

    Appendix B. Error message list

    A few error codes are defined by OTL. It is necessary because a runtime error can occur during debugging of a program. All the error codes defined can be issued only from member functions of the stream classes. Error reporting and handling is implemented via the normal mechanism of the OTL exceptions.

    The user can catch an exception raised not by an Oracle error but by one of the << or >> operators of the stream classes.

    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.

    Appendix C. OTL source code (otl.h)

    // // The OCI Template Library 1.0.6.5 // Copyright (C) Sergei Kuchin, 1996, 1997, 1998 // Author: Sergei Kuchin // This library is free software. Permission to use, copy, // modify and redistribute it for any purpose is hereby granted // without fee, provided that the above copyright notice appear // in all copies. // #ifndef __OTL_H #define __OTL_H // Functions are defined as INLINE. Those, who don't like inline // functions and prefer to have a separate C++ module with the // functions, can define INLINE as empty string and split up otl.h // into two files: otl.h and otl.C (otl.cpp). #define INLINE inline #define OTL_DEBUG_ // OTL uses the ociapr.h file (OCI standard header file, recommended // for ANSI C compilers). In Unix, OCI header files reside in the // $ORACLE_HOME/rdbms/demo directory. extern "C"{ #include <ociapr.h> } #ifdef OTL_DEBUG #include <iostream.h> #endif #include <string.h> #include <ctype.h> #include <stdarg.h> // Oracle internal data types (see Programmer's Guide to the // Oracle Call Interface, chapter 3) // enum otl_internal_type{ const int inVarChar2=1; const int inNumber=2; const int inLong=8; const int inRowId=11; const int inDate=12; const int inRaw=23; const int inLongRaw=24; const int inChar=96; const int inMslabel=106; //}; // Oracle external data types (see Programmer's Guide to the // Oracle Call Interface, chapter 3) //enum otl_external_type{ const int extVarChar2=inVarChar2; const int extNumber=inNumber; const int extInt=3; const int extFloat=4; const int extCChar=5; const int extVarNum=6; const int extLong=inLong; const int extVarChar=9; const int extRowId=inRowId; const int extDate=inDate; const int extVarRaw=15; const int extRaw=inRaw; const int extLongRaw=inLongRaw; const int extUInt=68; const int extLongVarChar=94; const int extLongVarRaw=95; const int extChar=inChar; const int extCharZ=97; const int extMslabel=inMslabel; //}; // Container data types for some of Oracle external data types defined // in Programmer's Guide to the Oracle Call Interface, chapter 3: typedef ub1 otl_date_intern[7]; // date Oracle internal format typedef ub1 otl_rowid_intern[14]; // rowid in Oracle internal format typedef char otl_cchar_rowid[19]; // rowid in text format typedef ub1 otl_varnum_intern[22]; // varnum in internal format typedef ub1 otl_number_intern[21]; // number in Oracle 21-byte binary // format // Error codes and messages, used in an exception raised in the // otl_select_stream class and otl_out_stream_class const int otl_error_code_0=32000; #define otl_error_msg_0 "Incompatible data types in stream operation" const int otl_error_code_1=32004; #define otl_error_msg_1 "No input variables have been defined in SQL statement" const int otl_error_code_2=32004; #define otl_error_msg_2 "Not all input variables have been initialized" const int otl_error_code_3=32001; #define otl_error_msg_3 "Row must be full for flushing output stream" // OTL "Blocked Call" class. It is used for "non-blocking" Oracle // connections. The otl_blocked_call exception is raised when the // current call of an OCI function is blocked. class otl_blocked_call{ public: otl_blocked_call(){}; ~otl_blocked_call(){}; }; const int otl_blocked=3123; // Return code, indicating that the most // recent Oracle call has been blocked const int otl_blocking=3128; // Return code, indicating that the // current Oracle connection is still blocking const int otl_nonblocking=1; // Return code, indicating that the // current connection is nonblocking // OTL exception class. Exceptions of this type are raised by the // library functions (default mode), unless it is prohibited // explicitly in the otl_connect or otl_cursor class constructors // (see below). In case of disabled exceptions OTL functions // return codes and it the user's responsibility to check out the // codes and handle errors. 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 are used in // constructors of the otl_connect, otl_cursor and // otl_select_cursor classes. enum{ disabled, enabled }; unsigned char msg[1000]; // error message buffer unsigned char* stm_text;// sql that caused the error int code; // error code // Create exception from LDA otl_exception(Lda_Def& lda,const char* sqlstm=0) {if(lda.rc==otl_blocked)throw otl_blocked_call(); init(lda,sqlstm); } // Create exception from amsg and acode otl_exception(const char* amsg,const int acode,const char* sqlstm=0) {if(acode==otl_blocked)throw otl_blocked_call(); init(amsg,acode,sqlstm); } // Copy constructor otl_exception(const otl_exception& p) {init((char*)p.msg,p.code,(const char*)p.stm_text);} // Default constructor otl_exception(){stm_text=0;} // Destructor ~otl_exception(){delete[] stm_text;stm_text=0;} // Init-function void init(const char* amsg,const int acode,const char* sqlstm) {stm_text=0; code=acode; if(sqlstm) { stm_text=new unsigned char[strlen(sqlstm)+1]; strcpy((char*)stm_text,sqlstm); } } // Init-function void init(const otl_exception& p) {init((const char*)p.msg,p.code,(const char*)p.stm_text);} protected: INLINE void init(Lda_Def& lda,const char* sqlstm=0); // get error code and message }; // Parent for otl_cursor and otl_connect classes class otl_object{ public: int connected; // "connected" flag // Default constructor INLINE otl_object(); // Destructor virtual ~otl_object(){}; protected: int ex_enabled; // "exception enabled" flag }; class otl_out_stream; // Connect class. Object of this class are used for connecting to // Oracle. class otl_connect: public otl_object{ public: Lda_Def lda; // Logon Descriptor Area (see OCI doc. for more // details) ub2& rc; // reference to "V7 return code" // Create "connect" object INLINE otl_connect(int exception_enabled=otl_exception::enabled); // exceptions are allowed to raise by default // Create "connect" object and connect to Oracle using the // "connect_str" connect string; by default, exceptions are // allowed to raise INLINE otl_connect(const char* connect_str, int exception_enabled=otl_exception::enabled ); // Destructor INLINE ~otl_connect(); // Concurrent logon; OCI application is allowed to have more than one