SOCI - The C++ Database Access Library

## Oracle Backend Reference

### Prerequisites

#### Supported Versions

The SOCI Oracle backend is currently supported for use with Oracle 10 or later.
Older versions of Oracle may work as well, but they have not been tested by the SOCI team.

#### Tested Platforms

Oracle version | Operating System | Compiler
--- | --- | ---
10.2.0 (XE) | RedHat 5 | g++ 4.3

#### Required Client Libraries

The SOCI Oracle backend requires Oracle's `libclntsh` client library. Depending on the particular system, the `libnnz10` library might be needed as well.

Note that the SOCI library itself depends also on `libdl`, so the minimum set of libraries needed to compile a basic client program is:

    -lsoci_core -lsoci_oracle -ldl -lclntsh -lnnz10

#### Connecting to the Database

To establish a connection to an Oracle database, create a `session` object using the oracle backend factory together with a connection string:

    session sql(oracle, "service=orcl user=scott password=tiger");

    // or:
    session sql("oracle", "service=orcl user=scott password=tiger");

    // or:
    session sql("oracle://service=orcl user=scott password=tiger");

The set of parameters used in the connection string for Oracle is:

- `service`
- `user`
- `password`
- `mode` (optional)

The first 3 of these parameters have to be provided as part of the connection string.

The `mode` parameter allows to specify the connection mode and can be any of:

- `default` (which is assumed if omitted)
- `sysdba`
- `sysoper`

Once you have created a `session` object as shown above, you can use it to access the database, for example:

    int count;
    sql << "select count(*) from user_tables", into(count);

(See the SOCI basics and exchanging data documentation for general information on using the `session` class.)

### SOCI Feature Support

#### Dynamic Binding

The Oracle backend supports the use of the SOCI `row` class, which facilitates retrieval of data which type is not known at compile time.

When calling `row::get<T>()`, the type you should pass as `T` depends upon the underlying database type.
For the Oracle backend, this type mapping is:

Oracle Data Type | SOCI Data Type | `row::get<T>` specializations
--- | --- | ---
number (where scale > 0) | `dt_double` | `double`
number (where scale = 0 and precision ≤ std::numeric_limits<int>::digits10) | `dt_integer` | `int`
number | `dt_long_long` | `long long`
char, varchar, varchar2 | `dt_string` | `std::string`
date | `dt_date` | `std::tm```

(See the dynamic resultset binding documentation for general information on using the `row` class.)

#### Binding by Name

In addition to binding by position, the Oracle backend supports binding by name, via an overload of the `use()` function:

    int id = 7;
    sql << "select name from person where id = :id", use(id, "id")

SOCI's use of ':' to indicate a value to be bound within a SQL string is consistant with the underlying Oracle client library syntax.

#### Bulk Operations

The Oracle backend has full support for SOCI's bulk operations interface.

#### Transactions

Transactions are also fully supported by the Oracle backend, although transactions with non-default isolation levels have to be managed by explicit SQL statements.

#### blob Data Type

The Oracle backend supports working with data stored in columns of type Blob, via SOCI's `blob` class.

#### rowid Data Type

Oracle rowid's are accessible via SOCI's `rowid` class.

#### Nested Statements

The Oracle backend supports selecting into objects of type `statement`, so that you may work with nested sql statements and PL/SQL cursors:

    statement stInner(sql);
    statement stOuter = (sql.prepare <<
        "select cursor(select name from person order by id)"
        " from person where id = 1",
        into(stInner));
    stInner.exchange(into(name));
    stOuter.execute();
    stOuter.fetch();

    while (stInner.fetch())
    {
        std::cout << name << '\n';
    }

#### Stored Procedures

Oracle stored procedures can be executed by using SOCI's `procedure` class.

### Acessing the native database API

SOCI provides access to underlying datbabase APIs via several `get_backend()` functions, as described in the Beyond SOCI documentation.

The Oracle backend provides the following concrete classes for navite API access:

Accessor Function | Concrete Class
--- | ---
`session_backend * session::get_backend()` | `oracle_session_backend`
`statement_backend * statement::get_backend()` | `oracle_statement_backend`
`blob_backend * blob::get_backend()` | `oracle_blob_backend`
`rowid_backend * rowid::get_backend()` | `oracle_rowid_backend`

### Backend-specific extensions

#### eracle=soci_error

The Oracle backend can throw instances of class `oracle_soci_error`, which is publicly derived from `soci_error` and has an additional public `err_num_` member containing the Oracle error code:

    int main()
    {
        try
        {
            // regular code
        }
        catch (oracle_soci_error const & e)
        {
            cerr << "Oracle error: " << e.err_num_
                << " " << e.what() << endl;
        }
        catch (exception const &e)
        {
            cerr << "Some other error: " << e.what() << endl;
        }
    }

Copyright © 2004-2008 Maciej Sobczak, Stephen Hutton