Class Union - Program Example
Class Union corresponds to a SQL union statement. It is constructed from two or more objects of Select class. The same rules apply as for creating SQL union statement, namely number of columns and columns types in both select statements must be the identical. Let's take a look at the following SQL statement:
 
                select prod_id, prod_desc
                from products
                where prod_id <= 10004
                union all
                select prod_id, prod_desc
                from products
                where prod_id > 10004
                order by 1, 2;
 
where union all means that identical column set from both tables will be repeated. This is a little bit artificial example (shown only for demonstration purposes), as rows retrieved will be all rows stored in the table products. In reality, a several different tables can be used to make the union. Below is a listing of a corresponding C++ program using ORAC++:
 
// select_union.cc
#include <orac_lib.h>
int main()
{
        Connect c;
        try  {
                Database d = c.OpenDatabase("demo", "demo");
                Table tb = d.GetTable("products");
                Table tc = d.GetTable("products");

                Select sel(tb);                                
                sel << tb["prod_id"] << tb["prod_desc"];
                sel >> tb["prod_id"] <= 100004;
                Select sel1(tc);                                
                sel1 << tc["prod_id"] << tc["prod_desc"];
                sel1 >> tc["prod_id"] > 100004;

                Union u(sel, sel1);
                u >> "order by 1,2";
                u.AsLine(HEADER_OFF);                        

                while(u.Row())
                        cout << u;        
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
Please note, that class Union creates by default 'union all' SQL statement. If we dont want repeated lines
a function SetAll(0) must be called. If called with argument not equal to zero, 'union all' is set again.
Note also, that constructor of class Record and Line can take as argument Union class object and column names are always columns from the first Select in the Union.
 

 
Class Intersect -  Program Example
Class Intersect corresponds to a SQL intersect statement. It is constructed from two objects of Select class. The same rules apply as for creating SQL intersect statement, namely number of columns and columns types in both select statements must be the identical. Let's take a look at the following SQL statement:
 
                select prod_id
                from products
                intersect
                select prod_id
                from deleted_products

  Below is a full listing of corresponding C++ program using ORAC++:
 

#include <orac_lib.h>
int main()
{

        Connect c;
        try  {
                Database d = c.OpenDatabase("demo", "demo");
                Table tb = d.GetTable("products");
                Table tc = d.GetTable("deleted_products");

                Select sel(tb);                                
                sel << tb["prod_id"];
                Select sel1(tc);                                
                sel1 << tc["prod_id"];

                Intersect i(sel, sel1);
                i.AsLine(HEADER_OFF);

                while(i.Row())
                        cout << i;
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
Please note, that constructor of class Record and Line can take as an argument Intersect class object and column  names are always columns from the first Select in the Intersect.
 

 
Class Minus - Program Example
Class Minus corresponds to a SQL minus statement. It operates on two objects of Select class. The same rules apply as for creating SQL minus statement, namely number of columns and column types in both select statements must be the identical. Let's take a look at the following SQL statement:
 
                select prod_id
                from products
                minus
                select prod_id
                from deleted_products
 
Below is a complete listing of a corresponding C++ program using ORAC++ (program performs sub-query based on rows fetched by Minus class object).
 
#include <orac_lib.h>
int main()
{
        int prod_id;
        Connect c;
        try  {
                Database d = c.OpenDatabase("demo", "demo");
                Table tb = d.GetTable("products");
                Table tc = d.GetTable("deleted_products");

                Select sel(tb);                                
                sel << tb["prod_id"];
                Select sel1(tc);                                
                sel1 << tc["prod_id"];

                Select sel2(tb);
                sel2 << tb["prod_id"] << tb["prod_desc"];
                sel2 >> tb["prod_id"] == ":";             // insert placeholder to query criteria

                Minus m(sel, sel1);
                m >> "order by 1 desc";
                while(m.Row()) {
                        sel << prod_id;                        // load variable
                        sel2 >> prod_id;                      // substitute for placeholder
                        while (sel2.Row())
                                cout << m;                       // defaults to line
                }
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
Please note that column names are always from the first Select in the Minus. In Union, Intersect and Minus where clause can be applied to each involved Select objects.
 

 
Outer Join
Let's take a look at the following SQL statement:
 
        select a.prod_desc, a.prod_price,
                b.ord_id, b.ord_date,
                b.ord_qty, c.type_desc
        from products a,
             orders b,
             product_types c
        where a.prod_id = b.prod_id
        and c.type_id = a.prod_type;
 
The statement creates a join of three tables. Let's assume that we want to list all products, which are not on 'orders' table. To do that, we have to modify above SQL statement as follows:
 
        select a.prod_desc, a.prod_price,
                b.ord_id, b.ord_date,
                b.ord_qty, c.type_desc
        from products a,
             orders b,
             product_types c
        where a.prod_id = b.prod_id(+)                
        and c.type_id = a.prod_type
        and b.prod_id is null;
 
And a corresponding C++ program using ORAC++:
 
#include <orac_lib.h>
int main()
{
        Connect c;
        try  {
                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);                                
                sel += tc;
                sel += td;                                         // join of three tables

                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"];   //outer join
                sel >> and_ >> td["type_id"] == tb["prod_type"];
                sel >> and_ >> tc["prod_id"] == "null";
                while(sel.Row())
                        cout << sel;                         // defaults to Line
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
Program will create output as follows:
 
PROD_DESC PROD_PRICE     ORD_ID   ORD_DATE    ORD_QTY         TYPE_DESC
 
    PANTS                 19.99         NULL           NULL           NULL             CLOTHING
 
Please note, that in order to create outer-join using ORAC++ a += joining operator must be used. It can be only one += operator per line. Details of use outer join are out of scope of this document.  

 
Use of Hints
An Oracle optimizer goal can be controlled by use of hints. There are many Oracle hints, which can be used in SQL statements. A discussion of the hints is beyond scope of this document. Any valid Oracle hint can be easily used in a C++ program using ORAC++ library. A hint must always be entered as a first argument when building a SQL string. Let's consider a following example:
 
#include <orac_lib.h>
int main()
{
        Connect c;
        try  {
                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);                                
                sel += tc;
                sel += td;                                 // join of three tables

                sel << "/*+ USE_NL(products orders product_types) */"   // hint
                            << 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_ >> tc["prod_id"] == "null";
                while(sel.Row())
                        cout << sel;                 // defaults to Line
        }
        catch (OException& e) {
                cerr << e.Show() << endl;
        }
        catch (...) {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
In example above, hint /*+ USE_NL(products orders product_types) */ is a valid Oracle hint.
Hint is used in identical form as in SQL statement for simplicity. Please note that any table name if used within the hint must be a full table name as defined in Table class constructor.
 
As use of hints can significantly improve SQL statement execution and it will give a programmer a full control overriding optimizer defaults.
 

 
Accessing Table Embedded Objects

Following programs illustrate how objects embedded in Oracle table can be accessed by ORAC++. Table CUSTOMERS contains two embedded objects NAMES and . Assuming that a Table object is created by following statements:
 
        Database d = c.OpenDatabase("demo", "demo");
        Table t = d.GetTable("customers");
 
there are several types of Select objects, which can be used to query the table:
 
a)      query based solely on table columns (ignoring embedded objects)
 
          Select s(t);
 
b)      query based solely on first or second embedded object (NAMES or ADDRESS)

          Object o = t.GetObject("names"); or               Object o = t.GetObject("address");
          Select s(o);                                                 Select s(o);
 
c)      query based solely on first and second embedded object (NAMES and ADDRESS)

          Object o = t.GetObject("names");
          Object o1 = t.GetObject("address");
          Select s(o);
          s += o1;

        query based on table columns and first or second embedded object

          Object o = t.GetObject("names"); or               Object o = t.GetObject("address");
          Select s(t);                                                 Select s(t);
          s += o;                                                       s += o;
 
e)      query based on table columns and two embedded objects (all table data)

          Object o = t.GetObject("names");
          Object o1 = t.GetObject("address");
          Select s(t);
          s += o;
          s += o1;
 
Although in the examples above all objects have been created by specifying their name,
default method can also be used (without object name). A sequential call to GetObject() will create objects in order as they are stored in the table. To get object name a GetObjName() function must be called. To check if object is of type collection (table within table), an IsColl() function must be called. Following, is a complete C++ program example to illustrate this point:
 
#include <orac_lib.h>
int main()
{
        int i;
        Object *o;
        Connect c;
        try  {
                Database d = c.OpenDatabase("demo", "demo");
                Table t = d.GetTable("customers1");
                Select s(t);                                                         // query based on table
                for(i=0; i<t.GetNumObjects(); i++)  {                       // get number of objects
                        o = t.GetAObject();                                     // get object in sequence
                        cout << o->GetObjName() << endl;              // show object name
                        if (o->GetType() == EMBEDDED)            // check if embedded
                                s += o;                                               // add to query
                }
                while (s.Row())            
                        cout << s;                                                  // line display by default
                d.Close();
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error..." << endl;
        }
        return 0;
}
 
Let's now take a look at the following program:
 
#include <orac_lib.h>
int main()
{
        Connect c;
        try  {
                Database d = c.OpenDatabase("demo", "demo");
                Table t = d.GetTable("customers");
                Object o = t.GetObject("address");         // get object by name        
                Select sel(o);                                          // Select based on Object
                while (sel.Row())
                        cout << sel;                                    // defaults to Line
        }
        catch (OException& e) {
                cerr << e.Show() << endl;
        }
        catch (...) {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
In the example above, Select class object is constructed from Object class. In this case, default Line columns are only columns defined within the Object.  Columns which belong to a Table are not visible.
 
Finally, let's consider a program:
 
#include <orac_lib.h>
int main()
{
        Connect c;
        try {
                Database d = c.OpenDatabase("demo", "demo");
                Table t = d.GetTable("customers");
                Object o = t.GetObject();             // get first object (names)
                Select sel(t);                                // Select based on Table
                sel += o;                                     // add Object to Select (1)
                while (sel.Row())
                cout << sel;                         // defaults to line
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
In latest example, Select class object is constructed from Table object. In this case, default Line columns are only columns defined within the Table. Columns which belong to an Object are not visible. To make them visible, Object must be added to Select (1).
         
Please note, that objects can be retrieved from table either in sequence by calling function
GetObject() with no arguments, or retrieved by name by calling GetObject(table_column_object_name).
If table contains several objects, object can be retrieved be either of two methods.
 
In addition to query, insert and update operations can be performed on table with embedded objects. Method of inserting and updating objects is similar to those used in case of select.
First, object must be extracted from the table:
 
        Database d = c.OpenDatabase("demo", "demo");
        Table t = d.GetTable("customers");
        Object o = t.GetObject("cust_address");                // get embedded object by name
 
Next, Insert or Update object must be constructed based on Table:
 
        Insert i(t);                or                 Update u(t);
 
After that, object must be added to Insert or Update (so object columns are visible):
 
        i += o;                        or                u += o;
 
Finally insert or update data must be entered:
 
        i >> 100 >> "Lawsons" >> "George" >> "Taraga Street" >> "Culvers";
        or
        u << o["street"] << o["city"];                                // update cust_address(street,city)
        u >> "Ruther Street" >> "Beckarton";                   // enter update data
        u >> t["cust_id"] == 100;                                    // where cust_id = 100
 
Note, that object type must be checked by calling function IsColl() after each embedded object is created. Only objects which are not a collection can be used in insert in update using above method.
 

 
Accessing Table of Oracle Objects
Table CUST  is a table of objects (CUSTOMER_OBJECT) created as a type (there are no table columns), all object columns became table columns, and can be accessed in the regular way:
 
#include <orac_lib.h>
int main()
{
        Connect c;
        try  {
                Database d = c.OpenDatabase("demo", "demo");
                Table t = d.GetTable("cust");
                Select sel(t);
                sel.AsRecord();
                while (sel.Row())
                        cout << sel;
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
When Oracle table has one or more columns of named type, in order to access named type columns, Object (named type) must be first retrieved from the table. Table (CUST1) used in program below has only one column (of named type), therefore to access named type columns Object must be retrieved from the Table, and Select must be based on this Object.
 
#include <orac_lib.h>
int main()
{
        Connect c;
        try  {
                Database* d = c.OpenADatabase("demo", "demo");
                Table* t = d->GetATable("cust1");         
                Object* o = t->GetAObject();             // get next embedded object in sequence
                Select sel(o);                                    // select based on object
                sel.AsRecord();                               // all objects columns are visible
                while (sel.Row())
                        cout << sel;
        }
        catch (OException& e) {
                cerr << e.Show() << endl;
        }
        catch (...) {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
Another method of retrieving data from the object is to use object column names. Next program will illustrate this point. Select class is based on object.
 
#include <orac_lib.h>
int main()
{
        Connect c;
        try  {
                Database* d = c.OpenADatabase("demo", "demo");
                Table* t = d->GetATable("cust1");
                Object* o = t->GetAObject();                        // get first available object
                if (o->GetType() == EMBEDDED) {             // check if not nested table
                        Select sel(o);
                        sel << o["id"] << o["last_name"];            // get columns from the Object
                        sel.AsRecord();                                     // use Record format
                        while (sel.Row())
                                cout << sel;
                }
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 

 
Accessing Nested Table
Following programs demonstrate accessing of Oracle nested tables. Table SCHEDULES  contains a nested table PERFOMERS. The way program access nested table is similar to handling sub-queries. There are two Select class objects created: first for the main table, second for the object embedded in nested table. Nested table is queried within the loop, with unique primary key used in where clause ( Select s(o)).
 
#include <orac_lib.h>
int main()
{
        int evt, act;
        char name[50], desc[50];
        Connect c;
        try {
                Database d = c.OpenDatabase("demo", "demo");
                Table t = d.GetTable("schedules");
                Object o = t.GetObject("evt_lineup");        // use column name of embedded table

                Select sel(t);                                              // first Select based on Table
                sel << t["evt_id"];
                Select s(o);                                               // second Select based on Object                
                s << o["act_no"] << o["act_name"] << o["act_desc"];
                s >> o["evt_id"] == ":";                              // placeholder in query criteria
                while (sel.Row()) {
                        sel << evt;                                        // load first fetched column into variable
                        cout << "evt = " << evt << endl;
                        s >> evt;                                           // substitute data for placeholder
                        while (s.Row())
                                s << act << name << desc;
                                cout << " ------ " << act << ":"
                                             << name << ":" << desc << endl;
                        }
                }
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}
 
Following program is a variation of preceding one. Difference is that selected columns are not specified, so all columns defined in the main and nested tables are fetched accordingly. Only column which needs to be loaded separately, is key (evt) used to query nested table (EVT_ID) in where clause (op. >>).
 
#include <orac_lib.h>
int main()
{
        int evt;
        Connect c;
        try  { // use pointer wrapper class Ptr
                Ptr<Database> d (c.OpenADatabase("demo", "demo"));
                Ptr<Table> t (d.GetATable("schedules"));
                Ptr<Object> o (t.GetAObject());

                Select sel(t);                              // two Select objects
                Select s(o);
                s >> o["evt_id"] == ":";               // placeholder in query clause
                s.AsLine(HEADER_OFF);

                while (sel.Row())  {
                        cout << sel;                       // fetched data is displayed in Line format
                        sel << evt;                         // get value for placeholder
                        s >> evt;                           // and substitute
                        while (s.Row())
                                cout << s;                 // display data in Line format (no column names)
                        cout << endl;
                }
        }
        catch (OException& e) {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}

Now, let's take look at the program to fetch specific record from the nested table:

#include <orac_lib.h>
int main()
{
        int act;
        char name[30], desc[30];
        Connect c;
        try  {
                Database d = c.OpenDatabase("demo", "demo");
                Table t = d.GetTable("schedules");
                Object o = t.GetObject();

                Select s(o);
                s >> o["evt_id"] == 10;               // query table
                s >> nested_ >> o["act_no"] == 1; // query nested table
                while (s.Row())  {
                        sel << act << name << desc;                     
                         cout << act << name << desc << endl;
                }
        }
        catch (OException& e) {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}

Following, are programs to insert, update and delete records from nested table (only code within 'try' block is shown):

First, get object from the table (common code for insert, update and delete):

                Database d = c.OpenDatabase("demo", "demo");
                Table t = d.GetTable("schedules");
                Object o = t.GetObject("evt_lineup");

Program code to insert records into nested table:

                Insert ins(t);
                ins << t["evt_id"] << t["evt_name"] << t["evt_location"] << t["evt_lineup"];  
                ins >> 30 >> "screening" >> "Los Angeles"; // three data items
                cout << s.Row() << " rows inserted" << endl;

                Insert i(o);
                i << o["act_no"] << o["act_name"] << o["act_desc"];                     
                i >> 1 >> "John Resda" >> "actor";
                cout << i.Row() << " rows inserted - nested table" << endl;

                i >> 2 >> "Mary Hantan" >> "actor";
                cout << i.Row() << " rows inserted - nested table" << endl;

Program code to update records in nested table:

                Update u(o);
                u << o["act_name"] << o["act_desc"];                     
                u >> "Jerry Tantext" >> "support";
                u >> t["evt_id"] == 30; // specify query criteria
                u >> nested_ >> o["act_no"] == 1;

                cout << u.Row() << " rows updated - nested table" << endl;


 Program code to delete records from nested table:


                Delete del(o);
                del >> t["evt_id"] == 30; // specify query criteria
                del >> nested_ >> o["act_no"] == 1;
                cout << del.Row() << " rows deleted - nested table" << endl;
 

 
 Using In/NotIn Functions in Query

Use of operator 'in/not in' in Oracle select statement can be presented by following examples:

        select prod_id, prod_desc
        from products
        where prod_id in (100001,100003,100004);

        select prod_id, prod_desc
        from products,
        where prod_id in (
        select prod_id
        from orders
        where ord_id > 40);

In the first example a list of values is used, in the second a sub-query is created. Corresponding C++ programs are shown below:

#include <orac_lib.h>
int main()
{
        Connect c;
        try {
                Database d = c.OpenDatabase("demo", "demo");
                Table t = d.GetTable("products");

                Select sel(t);
                sel << t["prod_id"] << t["prod_desc"];
                sel >> t["prod_id"] >> In("100001,100003,100004");  
                while (sel.Row())
                        cout << sel;                            
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}

#include <orac_lib.h>
int main()
{
        Connect c;
        try {
                Database d = c.OpenDatabase("demo", "demo");
                Table t1 = d.GetTable("products");
                Table t2 = d.GetTable("orders");

                Select s1(t1); // create two Select objects
                Select s2(t2);
                               
                s2 << t2["prod_id"];
                s2 >> t2["ord_qty"] > 40;
                s1 >> t1["prod_id"] >> In(s2);  
                while (s1.Row()) // execute first Select
                        cout << s1;                            
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}

 
Using Exists/NotExists Functions in Query
                    
Use of operator 'exists/not exists' in Oracle select statement can be presented by following examples:

        select *
        from products a
        where exists (
         select *
        from orders b
        where  a.prod_id = b.prod_id );

Corresponding C++ program using ORAC++ is shown below:

#include <orac_lib.h>
int main()
{
        Connect c;
        try {
                Database d = c.OpenDatabase("demo", "demo");
                Table t1 = d.GetTable("products");
                Table t2= d.GetTable("orders");

                Select sel(t1);                                              
                sel >> sel.Exists(t2)
                >> t1["prod_id"] == t2["prod_id"];
                while (sel.Row())
                        cout << sel;                            
        }
        catch (OException& e)  {
                cerr << e.Show() << endl;
        }
        catch (...)  {
                cerr << "Error ..." << endl;
        }
        return 0;
}

Placeholders can be used with 'In/NotInd/Exists/NotExists' functions.
Limitation is, that all placeholders must be inserted either before or after function.