Oracle Backend Reference

SOCI backend for accessing Oracle database.

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 OS Compiler
10.2.0 (XE) RedHat 5 g++ 4.3
11.2.0 (XE) Ubuntu 12.04 g++ 4.6.3
12.2.0.1 macOS High Sierra 10.13.5 AppleClang 9.1.0.9020039

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");

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

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

If both user and password are provided, the session will authenticate using the database credentials, whereas if none of them is set, then external Oracle credentials will be used - this allows integration with so called Oracle wallet authentication.

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 connection and data binding 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 consistent 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.

Native API Access

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

oracle_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;
    }
}