Oracle Call Interface Classes

Environment: VC5 SP3, NT4 SP3, Oracle 7.3, Oracle 8.0, Oracle Lite 3.6

I wrote this library some time ago when I began to use Oracle database 7.3. First I tried to use ODBC (with and without MFC classes) as client interface but soon I discovered some problems such as: no multi-thread support, no MFC support for stored procedure returning results, no possibility to use PL/SQL directly and to finish low...low...performance with large databases. Starting from a simple example found in the OCI samples I designed and developed my personal library to interface Oracle database.

Classes

COciObject

COciObject is a base class which is inherited by both COciDatabase class and COciCursor class. COciObject tells if a database connection exists and if exceptions are enabled.

COciDatabase

COciDatabase is the class used to access the database. Its main features are:
  • Database connection/disconnection;
  • Transaction handling (COMMIT/ROLLBACK);
  • OCI thread safe initialization.

    COciCursor

    COciCursor is the class for cursor handling. Its main features are:
  • Cursor open/close;
  • Query cancellation;
  • Rollback options settings;
  • Column fetch;
  • Parsing of a SQL statement;
  • Variable binding;
  • Immadiate execution of constant SQL statements.

    COciException

    COciException is the exception class. Exceptions of this type are raised by the library functions (default mode), unless it is prohibited explicitly in the COciDatabase or COciCursor class constructors. In case of disabled exceptions COciClasses 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.

    COciColumnDesc

    COciColumnDesc is the select list item (column) descriptor

    COciGenericVariable

    COciGenericVariable represents the generic variable and is parent of COciVariable. This class is needed in order to pass over internal info about actual host variables and arrays, instantiated from the templates.

    COciVariable

    COciVariable is the OCI template variable class. It allows to construct specialized template variable classes. COciVariable is parent of the classes:
  • COciSimpleVariable
  • COciString
  • COciVarchar2
  • COciDate
  • COciLongRow

    COciSimpleVariable

    COciSimpleVariable is a template with two arguments T = Host variable data type and Type = External data type's code of the host variable/array. Some instances of the COciSimpleVariable template are defined (with typedefs) for the various data types:
  • COciChar
  • COciInt
  • COciLongInt
  • COciFloat
  • COciDouble

    COciString

    COciString is the string scalar class. Implements null terminated string most useful in C++ programs

    COciVarchar2

    COciVarchar2 is the OCI VARCHAR2 class

    COciDate

    COciDate is the OCI DATE class

    COciLongRow

    COciLongRow is the Oci LONG ROW class

    Examples

    Some examples of how to use this library.They work with the default database that Oracle (7.3 and 8.0) creates.
    
    extern "C"{
    #include <ociapr.h>
    }
    #include <iostream.h>
    #include <string.h>
    #include <ctype.h>
    #include <stdarg.h>
    #include "OciClassDef.h"
    #include "OciException.h"
    #include "OciVariable.h"
    #include "OciDatabase.h"
    #include "OciCursor.h"
    
    void SelectMethod1( COciDatabase& db )
    {
       COciCursor     cur( db );
       int            empno;
       char           empname[32];
    
       // parse SQL statement
       cur.Parse("SELECT empno, ename FROM EMP");
       // bind 1st column in select list
       cur.BindInt( 1, &empno );             
       // bind 2nd column in select list
       cur.BindCString( 2, empname, 32 );    
       // execute SQL statement
       cur.Exec();
       // fetch until there are no more records
       while ( cur.Fetch() )
       {
          cout << empno << endl;
          cout << empname << endl;
       }
    }
    
    void SelectMethod2( COciDatabase& db )
    {
       COciCursor           cur( db );
       COciInt              empno( 1 );    // 1st column in select list 
       COciString<32>       empname( 2 );  // 2nd column in select list
    
       cur.Parse("SELECT empno, ename FROM EMP");
    
       cur.Bind( empno );
       cur.Bind( empname );
    
       cur.Exec();
    
       while ( cur.Fetch() )
       {
          cout << empno << endl;
          cout << empname << endl;
       }
    }
    
    void SelectMethod3( COciDatabase& db )
    {
       COciCursor           cur( db );
       COciString<100>      empname;
       COciDate             empdate;
    
       cur.Eexec("SELECT ename, hiredate FROM EMP", &empname, &empdate, NULL );
    
       while ( cur.Fetch() )
       {
          cout << empname << endl;
          cout << empdate.GetYear() << empdate.GetMonth() << empdate.GetDay() << endl;
       }
    }
    
    void UpdateMethod1( COciDatabase& db )
    {
       COciCursor           cur( db );
       // if you use in the constructor a string the variable will be marked as
       // placeholder rather then bind variable
       COciString<7>        empsalary(":salary");
       COciString<9>        empjob(":ejob");
    
       empsalary = "21000";
       empjob = "SwEng";
    
       cur.Eexec("UPDATE EMP SET sal = :salary, job = :ejob WHERE  ename='SCOTT'", &empsalary, &empjob, NULL );
    
       db.Commit();
    }
    
    
    void DescrMethod1( COciDatabase& db )
    {
       COciCursor        cur( db );
       COciColumnDesc    coldesc;
    
       cur.Parse("SELECT * FROM EMP");
    
       int Index = 1;
       do
       {
          cur.DescribeColumn( Index, coldesc );
          Index++;
       } while ( !cur.EndOfDesc() );
    }
    
    void main( void )
    {   
       COciDatabase   db;   
       try   
       {
          db.Connect("scott/tiger@mauro.world");
          cout << "\\nSelectMethod1:\\n";      
          SelectMethod1( db );
          cout << "\\nSelectMethod2:\\n";      
          SelectMethod2( db );
          cout << "\\nSelectMethod3:\\n";      
          SelectMethod3( db );
          cout << "\\nUpdateMethod1:\\n";      
          UpdateMethod1( db );
          cout << "\\nDescrMethod1:\\n";      
          DescrMethod1( db );     
       }
       catch( COciException& pErr )   
       {
          // print out error message
          cerr < pErr.m_Message < endl;    
       }
    }
    
    
    I used Autoduck to create a simple documentation that you can find as Word, Help and HTML files. I hope this library will help you to reduce your coding time and remember that your suggestions are welcomed.

    Thanks to my friend Abbondio Vago for is usefull work to create this library.

    Downloads

    Download source code and demo project - 136 Kb


  • Comments

    Leave a Comment
    • Your email address will not be published. All fields are required.

    Top White Papers and Webcasts

    • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

    • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

    Most Popular Programming Stories

    More for Developers

    RSS Feeds