Purpose

ORAC++ have been developed using:

• Oracle8i  with Oracle OCI package (Oracle 8i  - Release 2 for Linux)
  

• Linux (Red Hat 8.0) GNU C++ compiler and standard GNU C++ library.

ORAC++ is a shared library to be linked with Oracle client shared library (libclntsh.so - created at Oracle build time)
For installation details see 'readme.txt' file, included in 'orac8cpp_rhlib.tar.gz' library distribution file.
 
This document describes all features of ORAC++ and provides user with step by step method of writing C++ programs using ORAC++. For various examples of ORAC++ programs, see the documents (in Word format)ORAC++ Programming Examples. See also ORAC++ Additional Features for programming examples on accessing objects in tables, union, intersect, minus, outer-joins and Oracle hints.
 

ORAC++ Main Features
 

• Allows easy select, update, insert and delete on a table
  

• Support for equi-joins, non-equi joins and outer-joins
  
• Allows to select from a database view
  
• Allows to execute Stored Procedure/Function
  
• Support for sub-queries
  
• Allows to execute any user specified SQL statement
  
• Allows to dynamically create and drop database tables
  
• Permits repeat query by preserving either selected columns or query clause
  
• Permits repeat insert by preserving column names
  
• Permits repeat update by preserving column names
  
• Support for array fetch and array bind
  
• Allows dynamic rebinding
  
• Allows to set column format and size
  
• Allows a quick auto-display of retrieved rows by using class Record or Line
  
• Use of variabletype classes allows easy handling of data retrieval and update
  
• Supports CLOB and BLOB data types
  
• Supports SQL union, intersect and minus statements
  
• Supports Oracle hints
  
• Supports Oracle named types and collections
  
• Allows to store selected rows in memory for later retrieval
  
• Integrated error checking allows to minimize number of program lines

 
ORAC++ Classes at Glance

 
Generic pointer wrapper class Ptr<class T> is also provided.
This class can be used in place of all pointer to classes as suited. For example:

    Table* p;
    Ptr<Table> pt = p;
    p = &pt;

Later on in the program pt can be used instead of p.
Alternatively, Ptr class can be used in objects construction:

  Ptr<Table> pt (GetATable("products"));

 
C++ Program Using ORAC++
 
Each C++ program using ORAC++ must include following steps:

• Open Database  ( classes: Connect, Database )
  

• Define Table/View/Stored Procedure/Function ( classes: Table/View,Procedure/Function )
  
• Define Operation on the Table/View/Stored Procedure/Function ( classes: Select,  Update,  Insert,  Delete, Execute )
  
• Define Input/Output variables ( overloaded operator << - for output, >>  - for input )
  
• Perform Operation ( function: Execute - for class Procedure/Function, Row for remaining classes
  
• Commit/Rollback any changes made to database ( default is Rollback )
  
• Close Database ( optional )
   

Here is a brief description of each step ( as an example is used a simple one table query )
To Open Database a valid username and password has to be provided.
Create default object of Class Connect, then use OpenDatabase method from this class to create Database class object. Example:
 
        Connect c;
        Database d = c.OpenDatabase(user, password);
       
To Define Table a valid table name has to be provided. Use GetTable method from class Database to create Table object. This is a persistent object, which means that it corresponds to real object existing in the database. Example:
 
        Table tb = d.GetTable(customers);
 
To Define Operation 

-         in case of a Table : Select, Update, Insert, Delete must be specified
-         in case of a Stored Procedure only operation is Execute.
 
In this step operationalobject is created from class Select/Insert/Update/Dalete Class Select constructor has as argument persistent database object: tb.
Example:
 
        Select sel(tb);
 
To Define Input/Output variables overloaded operator <<for selected columns, and operator >>for wherecolumns are used. Example for class Select:
 
        sel << tb["cust_id"] << tb["name"] << tb["address"];         // (1)
        sel >> tb["cust_id"] ==  32181;                                     // (2)
 
Selected or 'where' columns are specified by entering column names within the bracket[]. At this step all columns are validated.

Please note, that in the example above Select object operates on only one table, but in fact, Select can be used on number of joined tables and columns can belong to any table from the join. Line (1) is optional, if omitted all columns are fetched from the table (in case when Select object operates on more than one table and there is no line of type (1) , an error message is displayed). If there is no line (2) , all rows from the table(s) are fetched (no where condition).
 
Operation is performed at this step. In case of a Table the only operation is a Row method. At this step actual fetch from the table is performed. Function Row() returns 0 when query is exhausted. After each fetch, data can be loaded into output variables using operator <<. (variable types must correspond to Select column types, otherwise error is reported).
Example for class Select:
 
        while (sel.Row()) {
                sel << cust_id << name << address;                        // load output variables        
                cout << cust_id << name << address << endl;        
        }
 
Types of output variables must correspond to types (and sizes) of Select columns, otherwise error message is displayed.
 
To commit changes made to database call function Commit():
 
        d.Commit();
 
To rollback changes made to database call function Rollback() (called by default):
 
        d.Rollback();
 
To Close Database a Close method is used (automatically called by Database destructor). Example:
 
        d.Close();
 
Program code from example above corresponds to a following SQL statement:
 
        select cust_id, name, address
        from customers
        where cust_id = 32181;
 
Below is a listing of complete C++ program using ORAC++:
 
        #include <orac_lib.h>
        int  main(void)
        {
                Connect c;
                try  {
                        Database d = c.OpenDatabase("user", "password");
                        Table tb = d.GetTable("customers");
                        Select sel(tb);
                        sel << tb["cust_id"] << tb["name"] << tb["address"];        
                        sel >> tb["cust_id"] == 32181;                        
                        while (sel.Row())   {
                                sel << cust_id << name << address;
                                cout << cust_id << name << address << endl;
                        }
                }
                catch (OException& e)  {
                        cerr  << e << endl;
                }
                catch (...)  {
                        cerr  <<  "Error ..." << endl;
                }
                return 0;
        }
 

 
Selecting Records 
Class Select is used to retrieve records from the one or more tables (or views).
To use this class, a database object must be created:
 
        Connect c;
        Database d = c.OpenDatabase("user", "password");
        Table t = d.GetTable("customers");
 
In this case, 't' is object from class Table. This is a persistent (database) object.
In order to perform select operation on this object a Select object must be created:
 
        Select s(t);
 
Once Select object has been successfully created, it can be used to query a database object. A simplest possible way is to query as follows:
 
        while (s.Row())        
                cout << s;        // display formatted lines (defaults to Line format)
 
Line format, used by default in example above, is identical to format in SQL*Plus query if statement is select * from table_name. Through this manual this format is called a horizontal.
In order to display all ( or selected ) column names along with the data a class Record has to be created ( based on Select ).
 
As the result of calling a AsRecord() function, display will have the following format:      column_name : column_data. This format is called a vertical through this manual.
 
        s.AsRecord();
        while (s.Row())        
                cout << s;                 // display formatted lines ( Record  format)
 
If AsLine() function (invoked by default) is called, format will change to line 'horizontal' (like in SQL*Plus).
 

        s.Array(10); // set array fetch size to 10 rows

        s.AsLine(HEADER_OFF);

        while (s.Row())        
                cout << s;                 // display formatted record - Line format (10 records at the time)
 
In examples above, all columns from the table were displayed (no selection has been made). In order to select columns to be fetched, an operator << has to be used:
 
        s << tb["cust_id"] << tb["name"] << tb["address"];        
 
Once columns are selected, any of the methods involving either implicit or explicit Line class (or explicit Record class ) can be used to display fetched data. Data also can be loaded to external variables which correspond to column type.
 
To use a where clause, following line must be inserted before Row() function:
 
        s >> t["prod_id"] == 100230;
 
This will create a where statement: where products.prod_id = 100230.
So, finally, a following program code ( code within try block ):
 
        Database = c.OpenDatabase(scott, tiger);
        Table t = d.GetTable("products");
        Select s(t);                                                // select * from products
        s >> t["prod_id"] == 100001;                   // where prod_id = 100001
        s.AsRecord();                                           // explicit class Record
        while (s.Row())        
                cout << s;                                         // output as pre-formatted Record
 
will produce output:
 
                          PROD_ID : 100001
                    PROD_DESC : BOOKSHELF
                    PROD_PRICE : 108.5
                     PROD_TYPE : 3
 
If we use Line class instead of a Record, all data will be displayed horizontally. So, the following program code (function used):
 
        s.AsLine();                                 // defaults to Line use only if previously set to Record
        while (s.Row())  
                cout << s;
 
will create output as follows:
 
PROD_ID    PROD_DESC         PROD_PRICE             PROD_TYPE
 
100001     BOOKSHELF                     108.5                               3

 If  we want to load fetched data into output variables, an operator <<must be used. Example below illustrates this point:
 
        int id;
        char *desc = new char[30];
        Select s(t);                                                     // select * from products
        s >> t["prod_id"] == 100001;                             // where prod_id = 100001
        while (s.Row())  {
                s << id << desc;
                cout << "ID = " << id << endl;
                cout << "DESC = " << desc << endl;
        }
 
In this case program output will be:
 
      ID = 100001
      DESC = BOOKSHELF
 
Order of loading data into variables is always important and must follow table description (types and sizes must correspond). In the example above, even though all columns from the table were fetched, we loaded only first two of them into output variables. In most situations, we need to select only specified columns from the table(s) or view. In this case, we must use operator << along with column names in the following way:
 
        int id;
        char desc[30];
        Select s(t);
        s << t["prod_desc"] << t["prod_id"];
        s >> t["prod_id"] == 10001;
        while (s.Row())  {
                s << desc << id;
                cout << "ID = " << id << endl;
                cout << "DESC = " << desc << endl;
        }
 
Now, we are fetching only two columns and output variables will be loaded using the same order as used in the operator <<(types and sizes of columns and variables must match).
 
In examples above, Select operated on one Table object. In fact, we can have any number of joined tables used by Select object.  Lets take look at the example below. In this case Select object operates on three joined tables. Operator << specifies selected columns, and operator >> specifies query columns and variables (all join conditions are also specified at this line).  
 
Program A (only code within try block is shown)
 
        Connect c;
        Database d = c.OpenDatabase("demo", "demo");
        Table tb = d.GetTable("products");
        Table tc = d.GetTable("orders");
        Table td = d.GetTable("product_types");

        Select sel(tb);                        // define join of 3 tables
        sel +=tc;
        sel += td;
        sel << tb["prod_desc"] << tb["prod_price"]
                        << tc["ord_id"] << tc["ord_date"]
                        << tc["ord_qty"] << td["type_desc"];
        sel >> tb["prod_id"] == tc["prod_id"];
        sel >> and_ >> td["type_id"] == tb["prod_type"];
        sel >> and_ >> tb["prod_desc"] == "BREAD";
        sel >> and_ >> tc["ord_qty"] == 20;
        sel.AsRecord();
        while(sel.Row())
                cout << sel;

A SQL statement to create database view, which corresponds to a join used by the program will be:
 
        create view view_orders as
        select products.prod_desc,
               products.prod_price,
                       orders.ord_id,
               orders.ord_date,
               orders.ord_qty,
               product_types.type_desc
        from products, orders, product_types
        where products.prod_id = orders.prod_id
        and product_types.type_id = products.prod_type;
 
After creating above view in the database, we can use a following C++ program to fetch the data:
Program B (only code within try block is shown)
 
        Connect c;
        Database d = c.OpenDatabase("demo", "demo");
        View v = d.GetView("view_orders");        // use view
        Select sel(v);                                         // select based on view
        sel >> v["prod_desc"] == "BREAD";
        sel >> and_ >> v["ord_qty"] == 20;
        sel.AsRecord();
        while(sel.Row())
                cout << sel;
 
Outputs from programs (A) and (B) are identical :
 
                     PROD_DESC : BREAD
                    PROD_PRICE : 1.35
                            ORD_ID : 20003214
                      ORD_DATE : 13-MAR-00
                       ORD_QTY : 20
                     TYPE_DESC : D
 
It is possible to use formats on selected and query columns. The format must be entered after column name using convention : column_name(format):
 
        tb["prod_id('000999')"]                                                // column type number(n)
        tb["prod_price('99999.99')"]                                        // column type number(n,p)
        tc["ord_date('DD/MM/YY HH24:MI:SS')"]                     // column type date
       
Only use of the format  in setting a query condition  (operator >>) is for the date column of type date. Please note, that all format strings correspond to formats as defined in Oracle
SQL *Plus. In case when formats are used on numeric columns, retrieved column type is set to be of  VARCHAR2 type, so output variable must be of type char[], otherwise error is reported.
For detailed explanation of using column of type date see:  Accessing Columns of Type Date.
 
In case of a table column of type VARCHAR2, size of a column can be set using convention: column_name(size). For VARCHAR2 columns size will override actual size of the column (can be only less or equal to actual size), and is used to limit size of fetched data:
 
        td["varchar2_column('30')"];        // set maximum size of fetched data
 
There are cases, when two fetches (using two Select class objects) must be done within the same loop. This is called a sub-query in SQL terms. To perform a sub-query in C++ program using ORAC++, placeholders must be specified.
 

 
Example below shows a program to retrieve all products for a specified product type. Select class object 'sel' operates on table product_types(outer loop), while Select class object 'sel1' operates on table products(inner loop). At the time when query columns are defined for object sel1 'product type' is unknown, so a placeholder ':' is used instead (1). After each fetch of outer (sel) loop, retrieved prod_type (type_id) must be substituted for a placeholder (2). It can be any number of placeholders specified in the outer loop, and both outer and inner loop can be used by Select class objects operated on joined tables or views. Example below is showing a C++ program to perform a sub-query as described above (only code within try block shown):
 
        Connect c;
        int id, proc_type = 0, type_id, cnt = 0;
        char name[30], desc[30], buff[10];
        float price;
        Database d = c.OpenDatabase("demo", "demo");
        Table tb = d.GetTable("product_types");
        Select sel(tb);
        sel << tb["type_id"] << tb["type_desc"];
        Table tc = d.GetTable("products");
        Select sel1(tc);
        sel1 << tc["prod_id"] << tc["prod_desc"] << tc["prod_type"];
        sel1 >> tc["prod_type"] == ":";                         // (1) placeholder here
        while(sel.Row())        {
                sel << type_id << desc;
                cout << " (outer)--- " << type_id << ":" << desc << endl;
                sel1 >> type_id;                                   // (2) substitute it by type_id
 
                while (sel1.Row())  {
                        sel1 << id << name << proc_type;
                        cout << "         (inner)--- " << id << ":"
                                 << name << ":" << proc_type << endl;
                }
        }
       
Data can be fetched using arrays instead of scalar variables. To simplify input/output also special variable classes can be used. There are following classes to store and manipulate column data:
 
        TInt                 for integer columns ( database number(n) type )
        TFloat             for float columns ( database number(n,d) type )
        TStr                for string columns ( database varchar2(n) type )
        TDate             for date columns ( database date type )
        TClob             for CLOB columns ( database CLOB type )
        TBlob             for BLOB columns ( database BLOB type )
        TRowid           for retrieving 'rowid' from the table ( database rowid type )
 
For examples of C++ program using various cases of array fetch and TInt, TFloat, TStr classes using class Select, please read ORAC++ Programs Examples.
 

 
TRowid class is designated to hold an Oracle 'rowid' value for each row. Objects of this class can be used as a pseudo-column in selected or query columns. Following, is an example of C++ program to fetch rowidfrom the table. TRowid is then used to fetch the data. Note, that class TRowid object 'r' is used as a column name (without quotes) when selecting columns (only code within try block is presented):
 
int main()
{
        int id, msg_size;
        TRowid r;
        Connect c;
        Database d = c.OpenDatabase("demo", "demo");
        Table tc = d.GetTable("table_test");
        Select sel1(tc);
        sel1 << tc[r] << tc["id"] << tc["msg_size"];
        if (sel1.Row())   {         // (1) fetch first row only
                sel1 << r << id << msg_size;         // fetch rowid into r
                cout << r << ":" << id << ":" << msg_size << endl;

                  sel1 >> tc[r] == r;                            // (2) set query using rowid
                  while(sel1.Row())   {
                          sel1 << r << id << msg_size;     // selected columns the same
                          cout << r << ":" << id << ":" << msg_size << endl;
                  }
        }
        return 0;
}
 
In the program shown, TRowid retrieved from (1) is used in setting query for loop (2).
 
 
 
 

 
 
 
 
 
 
 
Inserting Records
To insert rows into the table Insert class object must be created. Insert class constructor has as an argument a Table object. So, to insert rows into a table, we must start with the following statements:
 
        Connect c;
        Database d = c.OpenDatabase("user", "password");
        Table t = d.GetTable("products");
 
then create Insert object:
 
        Insert ins(t);
 
after that, specify columns to be inserted, using operator <<:
 
        ins << t["prod_id"] << t["prod_desc"] << t["prod_type"];
 
and, finally values to be inserted, using operator >>;
 
        ins >> 100010 >> "TV STAND" >> 2;  // (1)
 
At this point, a row can be inserted using a Row method:
 
        num_inserted = ins.Row();
 
where variable 'num_inserted' holds number of rows which were inserted (in this case 1).
To insert more than one row, specify size of array to be inserted, using function Array:
 
        ins.Array(10);                // to insert array of 10 records
 
In this case, values will be inserted from the arrays:
 
        int prod_id[10];
        char *prod_desc[10];
        int prod_type[10];
 
After populating arrays, data from the arrays must be  loaded using following lines:
 
        ins << t["prod_id"] << t["prod_desc"] << t["prod_type"];
 
        ins >> prod_id >> prod_desc >> prod_type;        // arrays are used
        ins.Array(10);
        num_inserted = ins.Row();
 
num_inserted will have a value 10 (upon successful insertion).
 
For examples of C++ programs to insert records see document ORAC++ - Programming Examples.
 
 
 

 
 
 
 
 
Updating Records
To update a table Update class object must be created. Update class constructor has as an argument a Table object ( like in case of class Insert ). To update a table, we enter:

        Connect c;
        Database d = c.OpenDatabase("user", "password");
        Table t = d.GetTable("products");
 
then create an Update object:
 
        Update upd(t);
 
and specify columns to be updated, using operator <<:
 
        upd << t["prod_desc"] << t["prod_type"];
 
then, update values have to be inserted using operator >>:
 
        upd >> "PRINTER STAND" >> 2;
 
and query condition (if necessary) also using operator >>:
 
        upd >> t["prod_id"] == 100010;
 
To update a table, a function Row must be used:
 
        num_updated = upd.Row();
 
where variable 'num_updated' will store number of rows updated (in this case 1). A complete C++ program ( only code within try block is shown ) to update a table, discussed above, will be:
 
        int num_updated;
        Database d = c.OpenDatabase("user", "password");
        Table t = d.GetTable("products");
        Update upd(t);
        upd << t["prod_desc"] << t["prod_type"];        
        upd >> "PRINTER STAND" >> 2;
        upd >> t["prod_id"] == 100010;
        num_updated = upd.Row();
        cout << num_updated << " rows updated" << endl;
 
 
 
 

 
 
 
 
Deleting Records
To delete rows from the table Delete class object must be created. Delete class constructor has as an argument a Table object. To delete rows from a table, we must start with the following statements:
 
        Connect c;
        Database d = c.OpenDatabase("user", "password");
        Table t = d.GetTable("products");
 
then create a Delete object:
 
        Delete del(t);
 
and specify a where clause (if necessary) using operator >>:
 
        del  >> t["prod_id"] == 100010;
 
To delete rows from a table, function Row must be used:
 
        num_deleted = upd.Row();
 
where  variable num_deleted will store number of rows deleted (in this case 1). A complete C++ program ( code within try block only ) to delete rows from a table, discussed above, will be:
 
        Database d = c.OpenDatabase("user", "password");
        Table t = d.GetTable("products");
 
        Delete del(t);
        del  >> t["prod_id"] == 100010;
        cout << del.Row()  << " rows_deleted" << endl;
 
 

 
Accessing Stored Procedure
To access a database stored procedure Procedure class object must be created. Procedure class object is both persistentand operationalobject, since stored procedure exists in the database. Procedure class object is created, like in case of a Table directly from Database class. Following lines of code are needed to create a Procedure object:
 
        Connect c;
        Database d = c.OpenDatabase("user", "password");
        Procedure p = d.GetProcedure("p_get_prod_name");
 
Where 'p_get_prod_name' is a name of stored procedure. At object creation step, validation is performed (like in case of a Table) and necessary object attributes are retrieved. When Table object was created, attributes were Table columns, in this case attributes are Procedure arguments.
 
After a valid Procedure object is created, all input/output arguments (if any) have to be filled in:
 
        p >> id >> name;
 
A listing of the stored procedure p_get_prod_namecan be found after C++ program listing. In this case, first argument of the procedure (type - input only) is a prod_idstored in program variable id. A second argument (type - output only) is a product name prod_descto be loaded  into a program variable name.
 
Final step is to execute a stored procedure, by using Execute function from class Procedure:
 
        p.Execute();
        p << id << name;                // load output variables
        cout << " prod  id  :" << id << endl;
        cout << " prod  desc:" << name << endl;
 
After execute step, all data retrieved by the procedure have to be loaded into a C++ program variables (even if variable is of a type input only it has to be entered in output operator <<). Please note, that variable names and types must follow the same order as was used in input operator >>(which corresponds to stored procedure argument types/names). Below is a full listing of a program we just discussed:
 
int main()
{
        int id = 100001;
        char *name = new char[30];
        Connect c;
        try  {
                Database d = c.OpenDatabase("demo", "demo");
                Procedure p = d.GetProcedure("p_get_prod_name");
                p >> id >> name;                // load input/output arguments
                p.Execute();
                p << id << name;                // load output variables
                cout << "product id   :" << id << endl;
                cout << "product desc :" << name << endl;
        }
        catch (OException& e)  {
                cerr << e << endl;
        }
        catch ( ... )  {
                cerr  <<  "Error ..." << endl;
        }
}
 
And Oracle stored procedure 'p_get_prod_name' listing is as follows:
 
create or replace procedure p_get_prod_name (
   p_id in number,
   p_desc out varchar2)
is
  cursor c(pid in number) is
  select prod_desc
  from products
  where prod_id = pid;
begin
  open c(p_id);
  fetch c into p_desc;
  if c%notfound then
        p_name := not found;
  end if;
  close c;
end;
 
 
 

 
 
 
Accessing Stored Function
To access a database stored function Function class object must be created. Function class is similar to a Procedure class and it corresponds to an object existing in the database. Following lines of code are needed to create a Function class object:
 
        Connect c;
        Database d = c.OpenDatabase("user", "password");
        Function f = d.GetFunction("f_get_prod_name");
 
Where f_get_prod_nameis a name of stored function. Difference between Function and a Procedure class is that function always returns a value (int, char*, float). Passing arguments to Function is identical to passing arguments to a Procedure, but should be remembered that first argument in a Function is always a value which function returns:
 
        f >> rtn >> id >> name;
 
Above line has an additional variable rtn( function return value ) at first position. Below is a full listing of a C++ program using stored function:
 
        int main()
        {
                int rtn, id = 100001;
                char *name = new char[30];
                Connect c;
                try  {
                        Database d = c.OpenDatabase("demo", "demo");
                        Function f = d.GetFunction("f_get_prod_name");
                        f >> rtn >> id >> name;         //load input/output arguments
                        f.Execute();
                        f << rtn << id << name;        // reload input/output arguments
                        cout << "product type   :" << rtn << endl;
                        cout << "product id     :" << id << endl;
                        cout << "product desc   :" << name << endl;
                }
                catch (OException& e)  {
                        cerr << e << endl;
                }
                catch (...)  {
                        cerr << "Error ..." << endl;
                }
                return 0;
        }
 
And Oracle stored function 'f_get_prod_name':
 
create or replace function f_get_prod_name (
   p_id in number,
   p_name out varchar2)
return number
is
  cursor c(pid in number) is
  select prod_type, prod_desc
  from products
  where prod_id = pid;
  v_rtn number;
begin
  open c(p_id);
  fetch c into v_rtn, p_name;
  if c%notfound then
        v_rtn := 0;
  end if;
  close c;
  return v_rtn;
end;
 
In this case, a return value can be used as indicator of a successful fetch operation (even if fetch fails because no records found, function or procedure is successfully executed). For examples of programs accessing stored procedures and functions see ORAC++ Programming Examples.
 

 
Classes Representing Various Column Types
A several classes to manipulate data stored in various column types were created. Classes TRowid, TDate were already described. Remaining classes are:
 
        TInt for integer columns ( for Oracle number(n) type)
        TFloat for float columns ( for Oracle number(n,p) type)
        TStr for character columns ( for Oracle varchar2(n) type)
 
To create objects from each class one may use, for example:
 
        TInt iv;
        TFloat fv;
        TStr cv;
        TStr cv1(30);        // set size to 30
 
Each object from classes above holds data from the column and nullindicator. Classes can be used to select, insert and update records. Output operator <<for each class will create by default <NULL>string in case when nullindicator is set. There are two basic i/o operators used by these classes:

  int i; double d; char c[30];
        iv << i; fv << d; cv << c;                              // return value (int, float, char*)
        iv = i; fv = d; cv = c;           // set value (int, float, char *) (cna be used in insert/update/query)
       iv = "null" fv = "null"; cv = "null";     // set null value (can be used in insert/update/query)

Usually, above functions are not directly used, as they are called by overloaded operators << and >>.  These classes can also be used for array fetch by specifying array of objects:
 
        TInt iarr[10];
        TFloat farr[10];
        TStr charr[10];
 
In case, when there is a need to set size of variable TStr used in array, a function SetSize(size) can be called:
 
        TStr charr[10];                        // no size specified
        for (int i = 1; i < 10; i++) charr[i].SetSize(30);
 
 

 
Accessing Columns of Type Date
There are two ways to retrieve, insert or update date column from an Oracle table:
 
-        access date column using a string ( char* ) variable
-        use a special class TDate to access date column
 
In first case, an optional format can be specified with column name. Oracle column of type date, by default returns string of a format: DD-MON-YY where DD is a day, MON stands for the first three characters of a month and YY year within a century. Lets take a look at the following C++ program example (only code within try block is shown):
 
        int id;
        char date[30];
        Connect c;
        Database d = c.OpenDatabase("demo", "demo");
        Table t = d.GetTable("orders");
        Select sel(t);
        sel << t["ord_id"] << t["ord_date"];   // date fetched as string
        sel >> t["ord_id"] == 12130; // set query condition
        while(sel.Row())  {
                memset(date,'\0' , sizeof(date));
                sel << id << date;
        }
 
when run against test database, retrieved variable datewill have a value : 10-FEB-00. Any valid Oracle SQL*Plus format can be specified, following column name. For example, format:
 
        t["ord_date('DD-MM-YYYY HH24:MI:SS')"]
 
will result in output variable datehaving value : 10-02-2000 15:35:58(where 15 is an hour, 35 minutes and 58 seconds)
 
Query criteria for a date columns can also be set in whereclause. If  format is not entered, Oracle default date format will apply DD-MON-YY. In this case, string is set to 12-JUL-00.
 
        sel >> t["ord_date"] == "12-JUL-00";   // (*)
 
A fetch will return all rows, with column ord_date having day, month and year as specified.
Each date column always holds time data: hours,  minutes and seconds, but when query criteria are set as shown in line (*), time information is ignored, and fetch will retrieve all records matching day, month and the year (records can have different timedata). By providing a format, it is possible to include a time data for exact match:
 
        sel >> t["ord_date('DD-MM-YYYY HH24:MI:SS')"] == "12-JUL-2000 11:30:21"
 
In real situations, date will not be entered this way, but objects loaded from one fetch can be used as condition in second fetch:
 
        int id;
        char date[30];
        Connect c;
        Database d = c.OpenDatabase("demo", "demo");
        Table t = d.GetTable("orders");
        Select sel(t);
        sel << t["ord_id"] << t["ord_date('DD-MM-YYYY HH24:MI:SS')"]
        sel >> t["ord_id"] == 12130;
        while(sel.Row())  {                        // (1)
                memset(date,'\0' , sizeof(date));
                sel << id << date;
        }
 
        sel >> t["ord_date('DD-MM-YYYY HH24:MI:SS')"] == date;
        while(sel.Row())  {                        // (2)
                memset(date,'\0' , sizeof(date));
                sel << id << date;
        }
 
Assuming that we fetched only one row in loop (1), using fetched date/time as query condition in  loop (2) will result in the same output from both loops. In similar way, date strings can be used in insert and update classes (see ORAC++ Programming Examples).
 
To simplify and enhance date handing process a TDate class was created. TDate class can be used to query, update or insert column of type date. There are two constructors for this class:
 
        TDate dt1;
        TDate dt2("ord_date");
 
First constructor, is a default constructor, and it creates an object of datetype for later use. Object created in this way can be used in a following statement:
 
        sel << t["ord_id"] << t[dt1] << t["ord_date"];
 
Object dt1has a default output format already created and this format is passed to next column to be selected, in this case 'ord_date'. Default output format for class TDate is
DD-MON-YY.For example 10-APR-00. It is possible to set additional formats after TDate object was  created:
 
        - function ShowTime()  will set format to DD-MON-YYYY HH24:MI:SS

        - function ShowDefault()  will set format to DD-MON-YY (Oracle default)

  - function SetFormat(char *fmt) will set TDate format to fmt
 
Second type of TDate class constructor allows to specify column name at the time of object creation. This object is permanently bound to a table column and can be used in statements like this:
 
        sel << t["ord_id"] << t["dt2"];
 
In this case, there is no need to enter a column name, because it is already stored in object dt2.
To compare, object dt1can be placed several times in the operator <<line, always preceding a column of datetype, while object dt2will always refer to ord_date column. (it can be re-usedin a different table, having the same column name). To illustrate this point, a following C++ program can be presented (only code within try block is shown):
 
int main()
{
        Connect c;
        int  cnt = 0, id;
        char fmt_dt[30], std_dt[30];
        TDate dt1;                                         // use default output format DD-MON-YY
        TDate dt2("ord_date");
        dt1.SetFormat("DD/MM/YYYY");                                   // set output format to DD/MM/YYYYY
        Database d = c.OpenDatabase("demo", "demo");
        Table tc = d.GetTable("orders");
        Select sel1(tc);
        sel1 << tc["ord_id"]
                 << tc["ord_date"]                                                     // default string date
                 << tc["ord_date('YYYY/MM/DD::HH24/MI')"]       // formatted string date
                 << tc[dt1] << tc["ord_date"]                                   // format from dt1
                 << tc[dt2];                                                               // name/format from dt2
        sel1 >> tc[ord_id] == 20003210;                                     // query criteria
 
        while(sel1.Row()) {
                memset(std_dt,'\0 ',sizeof(std_dt));
                memset(fmt_dt, '\0',sizeof(fmt_dt));
                sel1 << id << fmt_dt << std_dt << dt1 << dt2;
                cout << id << " # " << fmt_dt << " # " << std_dt << " # "
                        << dt1 << " # " << dt2 << endl;
        }
        return 0;
}
 
The program will produce a following output:
 
20003210 # 10-MAR-00 # 2000/03/10::14/48 # 10/03/2000 # 10-MAR-00
 
TDate objects can be used also in setting query condition. Let's take a look at the following program:
 
int main()
{
        Connect c;
        int   id;
        TDate dt2("ord_date");                         // default output format is DD-MON-YY
        Database d = c.OpenDatabase("demo", "demo");
        Table tc = d.GetTable("orders");
        Select sel1(tc);
        sel1 << tc["ord_id"]
              << tc[dt2];                                       // name format from dt2
        sel1 >> tc[ord_id] == 20003210;          // query condition
        while(sel1.Row()) {
                sel1 << id << dt2;
                cout << id <<  # " << dt2 << endl;
        }

        sel >> tc[dt2] == dt2;                               // query condition: get column name and format from dt2
        while(sel1.Row())  
                sel1 << id << dt2;
                cout << id <<  # " << dt2 << endl;
        }
        return 0;
}
 
Program will produce following lines (default TDate format is used)

20003210 # 10/03/2000
20003210 # 10/03/2000
 
In this case records fetched have date/time value of ord_date column matching exactly data retrieved from first loop. Using this method, we can specify any relational operator >, <, >=, <=, <> to compare two dates in a where clause. For example:
 
        sel >> tc[dt2] >= dt2;
 
In preceding examples, we used a TDate object bound to a column. Not bound object also can be used in a where statement:

        sel >> tc["ord_date"] >> tc[dt1] >= dt2;
 
Please note, that in this case, tc[dt1] must be preceded by tc["ord_date"] (reversing order used in selecting columns - op <<).
 
In similar way TDate class can be used to insert and update database tables. Following example illustrates this point:
 
        TDate dt("id_date");                 // use named constructor
        dt.Current();                           // get current date/time
        Insert ins(tc);
        ins << tc[dt];                           // insert column name from dt
        ins >> dt;                                // insert value date/time from dt
 
In case of update the same rules apply, and column name and value can be retrieved from TDate object.
 Please note that TDate object can be set to null value (to be used in insert/update/query) by specifying:

  dt = "null";

in query clause null values can be used in the following way: sel >> t[dt] == dt; (is null), or sel >> t[dt] != dt; (is not null)

 
Accessing Columns of Type CLOB and BLOB
To access columns of type CLOB and BLOB two classes have been created: TClob and TBlob.
Constructors for these classes are:
 
        TClob tc_1;                                         TBlob tb_1;
        TClob tc_2(clob_column);                     TBlob tb_2(blob_column);
 
Like in case of  a  TDate class, first constructor, is a default constructor, and it creates an object of Tclob/TBlobtype for later use. Object created in this way can be used in a following statement:
 
        sel << t["id"] << t[tc_1] << t["clob_column"];
        sel << t["id"] << t[tb_1] << t["blob_column"];
 
Second type of constructors use column name therefore objects created this way can be used in statements like:
 
        sel << t["id"] << t[tc_2];
        sel << t["id"] << t[tb_2];
 
Object 'sel' in program lines above belongs to class Select, so above statements are used to load columns which have to be selected. After columns are selected, data is fetched using function:
 
        num_rows_fetched  = sel.Row();
 
Selected data has to be loaded to appropriate variables: first one of type integer and second TClob or TClob type. Assuming, that integer variable is 'int_var', and TClob variable is 'tc_1' program line will look like that:
 
        sel << int_var << tc_1;
 
Variable int_varis loaded by operator <<from the statement above, but only CLOB locator is loaded. To get data stored in CLOB variable  'tc_1' (from class TClob) has to be queried as follows:
 
        while (tc_1.Next())                // Next() returns 0 if no more data in LOB
                tc_1 << buff;
 
External variable 'buff' in this case of type char[size], is loaded from CLOB column in the table. Size of fetched chunkof data must be specified, by calling function:
 
        tc_1.SetFetchSize(size);
 
A C++ program below shows example of fetching data from column of type CLOB:
 
int main()
{
        int  int_var;
        char buff[30];
        TClob tc;                                         // not named TClob
        Connect c;

        Database d = c.OpenDatabase("demo", "demo");

        Table t = d.GetTable("test_table_lob");
        Select s(t);
        s << t["id"] << t[tc] << t["clob_column"];   // set CLOB name
        s >> tc["id"] == 1;                               // query : where id = 1
        tc.SetFetchSize(30);                           // set chunksize to be fetched
        while(sel1.Row())  {                             // loop for all fetched rows
                sel << int_var << tc;                   // load non-LOB fetched data
                while (tc.Next())                         // check if data left in LOB
                        tc << buff;                           // load output  variable buff
                        cout << buff << endl;           // and display
        }
        d.Close();
}
 
Following C++ program explains how to insert data into column of type CLOB or BLOB:
 
int main()
{
        int  int_var;
        char buff[] = "this is a testing insert of clob datatype";
        TClob tc("clob_column"); // named TClob object
        tc.SetFetchSize(strlen(buff));                           // set chunksize to be inserted

      Connect c;
        Database d = c.OpenDatabase("demo", "demo");
        Table t = d.GetTable("test_table_lob");
        Insert i(t);
        i << t["id"] << t[tc];                       // columns to be populated
        i  >> 1 >> tc_2;                           // LOB object is set to empty                
        i.Row();                                       // insert integer variable, LOB is empty
        for (n = 0; n<100; n++)
                tc >> buff;                           // load CLOB column from variable buff
        d.Commit();
        d.Close();
}
 
And finally, C++ program to update data in CLOB column:
 
int main()
{
        int  int_var;
        char buff[] = this is a testing update of clob datatype ;
        TClob tc("clob_column");                         // use TClob with column name
        tc.SetFetchSize(strlen(buff));                    // set chunksize to be inserted

  Connect c;
        Database d = c.OpenDatabase("demo", "demo");
        Table t = d.GetTable("test_table_lob");
        Update  u(t);
        u << t[tc];                                              // columns to be updated
        u  >> tc;                                                // updated  CLOB (empty value)
        u >> tc[id] == 1;                                    // query: where id = 1
        while(u.Row())          {                           // update non-LOBs, set LOBs to empty
                do  {
                        for (n = 0; n<100; n++)
                        tc >> buff;                           // load data into CLOB column
                }  while (upd.GetLobs());               // get LOBs for all updated records
        }
        d.Commit();
        d.Close();
}
 
When updating more than one row with LOB data using one Update object, after call to a Row() function, a doloop must be executed to update LOB values for all updated rows. Please  note that Row() function will update all non-LOB columns and set LOB columns to EMPTY_CLOB (BLOB).
 
Up to two CLOB columns and BLOB columns (maximum 4 LOB types) can be selected or inserted or updated in one operation (using one operational object: Select, Insert, Update).
If table contains more LOB type columns, they can be accessed with additional Select, Update, Insert objects:
 
        TClob1 = tc1("clob1");
        TClob2 = tc1("clob2");
        TBlob1 = tc1("blob1");
        TBlob2 = tc1("blob2");
        Table t = d.GetTable("table_2clob_2blob");
        Select sel(t);
        Insert ins(t);
        Update upd(t);
        sel << t[clob1] << t[clob2] << t[blob1] << t[blob2];
        ins << t[clob1] << t[clob2] << t[blob1] << t[blob2];
        upd << t[clob1] << t[clob2] << t[blob1] << t[blob2];
 
TClob (TBlob) objects must be separately fetched/populated by using TClob operators <</>>accordingly for each LOB object specified. For more examples of programs using LOBs see ORAC++ Programming Examples.
 

 
Connecting to Database
Connect and Database classes are used to establish connection to database. There are two basic types of connections : server connection and server-less connection. To use server connection, a listener service must be running on client machine. Server and database name is stored in TNSNAMES.ORA file, so for example, to connect to a local server with database 'data1' a following program line should be entered:
 
        Connect c("data1.localdomain");
 
To use default server and default database, specify:
 
        Connect c("");
 
To connect to database in case of server connection, enter one from the two lines:
 
        Database d = c.GetDatabase("demo", "demo");                // default database
        Database d = c.GetDatabase("demo", "demo","" );           // default database
 
Following line will cause error, since database name is defined at server connection
 
        Database d = c.GetDatabase("demo", "demo", "data1"); // database data1- error
 
To connect to local database, server connection is not needed and listener does not have to be running.

Again, there are two possibilities here : specify database name (data1) or use default connection ( ). Here are examples:
 
        Connect c;                        // no server connection
        Database d = c.GetDatabase("demo", "demo", "data1");  // database SID 'data1'
        Database d = c.GetDatabase("demo", "demo","" );          // default database
        Database d = c.GetDatabase("demo", "demo");              // default database
 
 

 
 
 
Resetting Objects for Reuse
To reuse operational objects (from class: Select, Insert, Update, Delete) a reset function is used. There are various types of reset command, which are applicable to specific classes.
Reset functions are:

All resetting functions with corresponding classes are listed below:

Some resetting functions call others. Following table summarizes function's call.

 
Instead of resetting objects, a placeholders can be used in certain situations. In ORAC++ placeholders can be employed only for "where" statements ( in operators >>):
 
        sel >> t["id"] >> ":" >> t["prod_name"] >> ":";                // select 
        upd >> t["id"] >> ":" >> t["prod_name"] >> ":";               // update
        del >> t["id"] >> ":" >> t["prod_name"] >> ":";                // delete
 
Data, can be loaded using statements:
 
        sel >> 1 >> "computer";
        upd >> 1 >> "computer";
        del >> 1 >> "computer";
 
Use of placeholders is limited to substituting data for whereclause in Select/Update/Delete class. In class Insert placeholders are implicitly specified for all inserted columns, so data can be reloaded after each insert.Row() call. ( ins >> 1 >> data1; ins.Row(); ins >> 2 >> data2; ).
 
Main difference between use of placeholders and resetting objects is that resetting allows to redefine totally new selected/where columns and where criteria, placeholders refill existing query column data/criteria with new data.

Please note, that 'where' clause is auto-reset if new query criteria are entered (after select, update, delete is performed). For example:

  sel >> t["prod_id"] >> 100001; // query condition set
  sel.Row();
  sel >> t["prod_type"] >> 3 >> "and" >> t["prod_price"] >> ">" >> 1000; // query clause is reset to new one
 
 In case of Procedure or Function, all input parameters are auto-reset after each call to function Execute():

  p >> id >> name; // load input parameters
  p.Execute();
  p << id << name; // reload parameters with output data
  p >> id1 >> name1; // load new input parameters