Introduction

Sibyl is an OCI-based interface (a.k.a. a driver) between Rust applications and Oracle databases. It supports both sync (blocking) and async (nonblocking) API.

Example

Assuming an HR sample schema is installed, the following example program would report median salaries for each country in the specified region.

Blocking Mode Version

fn main() -> sibyl::Result<()> {
    let oracle = sibyl::env()?;

    let dbname = std::env::var("DBNAME").expect("database name");
    let dbuser = std::env::var("DBUSER").expect("user name");
    let dbpass = std::env::var("DBPASS").expect("password");

    let session = oracle.connect(&dbname, &dbuser, &dbpass)?;

    let stmt = session.prepare("
        SELECT c.country_name, Median(e.salary)
          FROM hr.employees e
          JOIN hr.departments d ON d.department_id = e.department_id
          JOIN hr.locations l   ON l.location_id = d.location_id
          JOIN hr.countries c   ON c.country_id = l.country_id
          JOIN hr.regions r     ON r.region_id = c.region_id
         WHERE r.region_name = :REGION_NAME
      GROUP BY c.country_name
    ")?;

    let rows = stmt.query("Europe")?;

    while let Some(row) = rows.next()? {
        let country_name : &str = row.get(0)?;
        let median_salary : u16 = row.get(1)?;
        println!("{:25}: {:>5}", country_name, median_salary);
    }
    Ok(())
}

When executed it prints:

Germany                  : 10000
United Kingdom           :  8800

Nonblocking (async) Mode Version

fn main() -> sibyl::Result<()> {
  sibyl::block_on(async {
    let oracle = sibyl::env()?;

    let dbname = std::env::var("DBNAME").expect("database name");
    let dbuser = std::env::var("DBUSER").expect("user name");
    let dbpass = std::env::var("DBPASS").expect("password");

    let session = oracle.connect(&dbname, &dbuser, &dbpass).await?;

    let stmt = session.prepare("
        SELECT c.country_name, Median(e.salary)
          FROM hr.employees e
          JOIN hr.departments d ON d.department_id = e.department_id
          JOIN hr.locations l   ON l.location_id = d.location_id
          JOIN hr.countries c   ON c.country_id = l.country_id
          JOIN hr.regions r     ON r.region_id = c.region_id
         WHERE r.region_name = :REGION_NAME
      GROUP BY c.country_name
    ").await?;

    let rows = stmt.query("Europe").await?;

    while let Some(row) = rows.next().await? {
        let country_name : &str = row.get(0)?;
        let median_salary : u16 = row.get(1)?;
        println!("{:25}: {:>5}", country_name, median_salary);
    }
    Ok(())
  })
}

Note the only difference between this and the blocking mode program is that async method calls need to be awaited. Otherwise the async version of the program is a verbatim copy of the non-async one.

Prerequisites

Sibyl needs an installed Oracle client in order to link either OCI.DLL on Windows or libclntsh.so on Linux. The minimal supported client is 12.2 as Sibyl uses some API functions that are not available in earlier clients.

Note that while supporting 12.1 and older clients is definitely feasible, it was not a priority.

Using Sibyl In A Project

Sibyl has 2 features - blocking and nonblocking. They are exclusive and one must be explicitly selected as neither is the default. Thus, when Sibyl is used as a dependency, it might be included as:

[dependencies]
sibyl = { version = "0.6", features = ["blocking"] }

A nonblocking mode also needs to know which async runtime/executor it is allowed to use to spawn async tasks. The async runtime selection is also controlled by a set of exclusive features. For now, Sibyl supports tokio, actix, async-std, and async-global. One of these must be specified with the nonblocking feature. For example:

[dependencies]
sibyl = { version = "0.6", features = ["nonblocking", "tokio"] }

Building

The cargo build needs to know where the OCI client library is. You can provide that information via environment variable OCI_LIB_DIR on Windows or LIBRARY_PATH on Linux. On Linux, depending on which Oracle client is installed and how it was installed, the LD_LIBRARY_PATH might also be needed. LIBRARY_PATH (and LD_LIBRARY_PATH) would include the path to the directory with libclntsh.so. For example, you might build Sibyl's examples as:

export LIBRARY_PATH=/opt/instantclient_19_24
export LD_LIBRARY_PATH=/opt/instantclient_19_24
cargo build --examples --features=blocking

On Windows the process is similar if the target environment is gnu. The OCI_LIB_DIR would point to the directory with oci.dll:

set OCI_LIB_DIR=%ORACLE_HOME%\bin
cargo build --examples --features=blocking

Note that for gnu targets the build script will try to locate OCI.DLL by searching it in the current PATH if the OCI_LIB_DIR is not specified.

However, for msvc environment the OCI_LIB_DIR must exist and point to the directory with oci.lib. The build will fail if OCI_LIB_DIR is not set. For example, you might build those examples as:

set OCI_LIB_DIR=%ORACLE_HOME%\oci\lib\msvc
cargo build --examples --features=blocking

Because of the above requirement, that the OCI_LIB_DIR must be set for msvc targets, it also must be specified for the rust-analyzer. For example, in VS Code this can be done in .vscode\settings.json:

"rust-analyzer.server.extraEnv": { "OCI_LIB_DIR": "C:\\Path\\To\\Oracle\\instantclient\\sdk\\lib\\msvc" }

Optional Features

Sibyl provides one optional opt-in feature - unsafe-direct-binds.

By default Sibyl creates shadow buffers for arguments that are bound to IN parameter placeholders. With unsafe-direct-binds Sibyl instead binds arguments directly. This, of course, is somewhat more performant and conserves memory. However, unsafe-direct-binds makes it possible to violate Rust's immutability of references when a reference is mistakenly bound to the OUT or INOUT placeholder.

Example

    let stmt = session.prepare("
        SELECT country_name
          FROM hr.countries
         WHERE country_id = :COUNTRY_ID
    ")?;
    let row = stmt.query_single("UK")?.unwrap();
    let name : String = row.get(0)?;
    // note that the `name` is not declared mutable

    let stmt = session.prepare("
    BEGIN
        SELECT country_name
          INTO :COUNTRY_NAME
          FROM hr.countries
         WHERE country_id = :COUNTRY_ID
           AND country_name != :COUNTRY_NAME;
    END;
    ")?;
    stmt.execute((
        ("COUNTRY_ID", "NL"),
        ("COUNTRY_NAME", &name),
        // `:COUNTRY_NAME` is INOUT but `name` is bound only for reading
    ))?;
    println!("country_name={name}");

Default (Safe) Binding

    #[cfg(not(feature="unsafe-direct-binds"))]
    // `name` has not changed despite the binding mistake
    assert_eq!(name, "United Kingdom");

The OCI actually did change the value as values bound to OUT placeholders are always changed. However, that has happened in the shadow buffer that Sibyl created to bind the value, thus actual value in Rust was not affected.

Unsafe Direct Binding

The binding mistake allows unacceptable mutation of the bound value:

    #[cfg(feature="unsafe-direct-binds")]
    assert_eq!(name, "Netherlandsdom");

Note also that because the string was bound via a (read-only) reference Sibyl used read-only binding for it and thus the code that sets the String length to match the loaded value was not executed. As the result the new name still has the last 3 characters from the original name.

Example Project

Let's say we need to write a program to generate a report for the demo HR schema. This report shall list median salaries for each country of the specified region. The name of the region and the connection parameters shall be specified as environment variables (maybe because our program will be executed in a container).

Project Setup

cargo new median-salary

Then edit Cargo.toml and include Sibyl as a dependency:

[dependencies]
sibyl = { version="0.6", features=["blocking"] }

We will be writing a single threaded program, thus we will use Sibyl's blocking API.

Implement the Solution

fn main() -> sibyl::Result<()> {
    let oracle = sibyl::env()?;

    let dbname = std::env::var("DBNAME").expect("database name");
    let dbuser = std::env::var("DBUSER").expect("user name");
    let dbpass = std::env::var("DBPASS").expect("password");
    let region = std::env::var("REGION").expect("HR region");

    let session = oracle.connect(&dbname, &dbuser, &dbpass)?;

    let stmt = session.prepare("
        SELECT c.country_name, Median(e.salary)
          FROM hr.employees e
          JOIN hr.departments d ON d.department_id = e.department_id
          JOIN hr.locations l   ON l.location_id = d.location_id
          JOIN hr.countries c   ON c.country_id = l.country_id
          JOIN hr.regions r     ON r.region_id = c.region_id
         WHERE r.region_name = :REGION_NAME
      GROUP BY c.country_name
    ")?;

    let rows = stmt.query(&region)?;

    while let Some(row) = rows.next()? {
        let country_name : &str = row.get(0)?;
        let median_salary : u16 = row.get(1)?;
        println!("{:25}: {:>5}", country_name, median_salary);
    }
    Ok(())
}

Build and Run

cargo build

And then run it:

DBNAME=localhost/orcl DBUSER=sibyl DBPASS=Or4cl3 REGION=Americas cargo run

⚠ Note that depending on the Oracle client installed you might also need to adjust PATH on Windows or specify LD_LIBRARY_PATH on Linux:

LD_LIBRARY_PATH=/opt/instantclient_19_24 DBNAME=localhost/orcl DBUSER=sibyl DBPASS=Or4cl3 REGION=Americas cargo run

Expected Output

Canada                   :  9500
United States of America :  3250

Environment

An OCI environment handle must be created before any other OCI function can be called. While there can be many environments - for example, they might be configured to have different languages and territories - usually one is sufficient. Sibyl initializes it to be the most compatible with Rust requirements - thread-safe using UTF8 (AL32UTF8) character encoding. That single environment handle can be created in main and then passed around:

fn main() {
    let oracle = sibyl::env().expect("Oracle OCI environment");
    // ...
}

Note however that some functions will need a direct reference to this handle, so instead of passing it around some applications might prefer to create it statically:

use sibyl::Environment;
use lazy_static::lazy_static;

lazy_static!{
    pub static ref ORACLE : Environment = sibyl::env().expect("Oracle OCI environment");
}

Then later one would be able to create, for example, a current timestamp as:

use sibyl::TimestampTZ;

let current_timestamp = TimestampTZ::from_systimestamp(&ORACLE)?;

Connections

Depending on the application and its needs there are several ways to obtain a database sesssion:

An application might use Environment::connect method to connect to a database and start a new user session. This is the most relevant way to get session for a single threaded application. Though, multithreaded applications might, in some cases, do the same.
A multithreaded or a multitasking (async) application might create a session pool and then make each thread (or task) "borrow" a session from that pool for limited time. The caveat here is that those sessions are indistinguishable and thus must be "stateless".
A blocking mode multithreaded application might create a connection pool and make each thread establish their own sessions that would use pooled connections when they need to communicate with the database. As these sessions are not shared, they can be "stateful".

Connection Per Thread

fn main() -> sibyl::Result<()> {
    let oracle = sibyl::env()?;

    let dbname = std::env::var("DBNAME").expect("database name");
    let dbuser = std::env::var("DBUSER").expect("user name");
    let dbpass = std::env::var("DBPASS").expect("password");

    let session = oracle.connect(&dbname, &dbuser, &dbpass)?;
    // ...
    Ok(())
}

Where dbname can be any name that is acceptable to Oracle clients - from local TNS name to EZConnect identifier to a connect descriptor.

Or with multiple threads:

use std::{env, thread, sync::Arc};
use sibyl::*;

fn main() -> Result<()> {
    let oracle = sibyl::env()?;
    let oracle = Arc::new(oracle);

    let mut workers = Vec::new();
    for _i in 0..10 {
        let oracle = oracle.clone();
        let handle = thread::spawn(move || -> Result<()> {
            let dbname = env::var("DBNAME").expect("database name");
            let dbuser = env::var("DBUSER").expect("user name");
            let dbpass = env::var("DBPASS").expect("password");

            let session = oracle.connect(&dbname, &dbuser, &dbpass)?;            
            // ...
            Ok(())
        }
        workers.push(handle);
    }
    for handle in workers {
        let _ = handle.join();
    }
    Ok(())
}

Session Pool

Session pool can be used in both blocking and nonblocking mode applications.

Blocking Mode Pool

use std::{env, thread, sync::Arc};
use once_cell::sync::OnceCell;
use sibyl::*;

fn main() -> sibyl::Result<()> {
    static ORACLE : OnceCell<Environment> = OnceCell::new();
    let oracle = ORACLE.get_or_try_init(|| {
        Environment::new()
    })?;

    let dbname = env::var("DBNAME").expect("database name");
    let dbuser = env::var("DBUSER").expect("user name");
    let dbpass = env::var("DBPASS").expect("password");

    // All sessions will be authenticated with the provided user name and password.
    let pool = oracle.create_session_pool(&dbname, &dbuser, &dbpass, 0, 1, 10)?;
    // This pool has 0 available session at this time, will create 1 session at a
    // time when they are needed (session is requested and there are no available
    // sessions in the pool), up to the maximum of 10 sessions.
    let pool = Arc::new(pool);

    let mut workers = Vec::new();
    for _i in 0..100 {
        let pool = pool.clone();
        let handle = thread::spawn(move || -> Result<()> {
            let session = pool.get_session()?;
            // ...
            Ok(())
        }
        workers.push(handle);
    }
    for handle in workers {
        let _ = handle.join();
    }
    Ok(())
}

Nonblocking Mode Pool

use std::{env, thread, sync::Arc};
use once_cell::sync::OnceCell;
use sibyl::*;

#[tokio::main]
async fn main() -> sibyl::Result<()> {
    static ORACLE : OnceCell<Environment> = OnceCell::new();
    let oracle = ORACLE.get_or_try_init(|| {
        Environment::new()
    })?;

    let dbname = env::var("DBNAME").expect("database name");
    let dbuser = env::var("DBUSER").expect("user name");
    let dbpass = env::var("DBPASS").expect("password");

    let pool = oracle.create_session_pool(&dbname, &dbuser, &dbpass, 0, 1, 10).await?;
    let pool = Arc::new(pool);

    let mut workers = Vec::new();
    for _i in 0..100 {
        let pool = pool.clone();
        let handle = tokio::task::spawn(async move {
            let session = pool.get_session().await?;
            // ...
            Ok::<_,Error>(())
        }
        workers.push(handle);
    }
    for handle in workers {
        let _ = handle.await;
    }
    Ok(())
}

Connection Pool

Connection pool can only be used in blocking mode applications.

use std::{env, thread, sync::Arc};
use once_cell::sync::OnceCell;
use sibyl::*;

fn main() -> sibyl::Result<()> {
    static ORACLE : OnceCell<Environment> = OnceCell::new();
    let oracle = ORACLE.get_or_try_init(|| {
        Environment::new()
    })?;

    let dbname = env::var("DBNAME").expect("database name");
    let dbuser = env::var("DBUSER").expect("user name");
    let dbpass = env::var("DBPASS").expect("password");

    // Connection pool needs to establish an internal session with the database.
    // `dbuser` and `dbpass` here are for that session.
    let pool = oracle.create_connection_pool(&dbname, &dbuser, &dbpass, 0, 1, 4)?;
    // This pool has 0 available connections at this time, will create 1 connection
    // at a time when they are needed (session needs a connection to run and there
    // are no available connections in the pool), up to the maximum of 4 connections.
    let pool = Arc::new(pool);

    let mut workers = Vec::new();
    for i in 0..10 {
        let pool = pool.clone();
        let handle = thread::spawn(move || -> Result<()> {
            let dbuser = env::var(format!("DBUSER{}",i)).expect("user name");
            let dbpass = env::var(format!("DBPASS{}",i)).expect("password");
            // Here `dbuser` and `dbpass` are used to create a new database session.
            // While these sessions share pooled connections, they are entirely
            // "owned" by the threads that created them and as such might use
            // different users for authentication.
            let session = pool.get_session(&dbuser, &dbpass)?;
            // ...
            Ok(())
        }
        workers.push(handle);
    }
    for handle in workers {
        let _ = handle.join();
    }
    Ok(())
}

SQL Statement Execution

All SQL or PL/SQL statements must be prepared before they can be executed:

let stmt = session.prepare("
    SELECT employee_id, last_name, first_name
      FROM hr.employees
     WHERE manager_id = :id
  ORDER BY employee_id
")?;

A prepared statement can be executed either with the query, query_single or execute methods:

query is used for SELECT statements. In fact, Sibyl will complain if you try to query any other statement.
query_single is a variant of query that returns a single row. It's a convenience method that allows skipping boilerplate of extracting only one row from a result set when it is known upfront that only one row (or none) is expected.
execute is used for all other, non-SELECT, DML and DDL.

execute returns the number of rows affected by the statement.

query returns what is colloquially called a "streaming iterator" which is typically iterated using while. For example (continuing the SELECT example from above):

let mut employees = HashMap::new();
let stmt = session.prepare("
    SELECT employee_id, last_name, first_name
      FROM hr.employees
     WHERE manager_id = :id
  ORDER BY employee_id
")?;
let rows = stmt.query(103)?;
while let Some( row ) = rows.next()? {
    let employee_id : u32 = row.get(0)?;
    let last_name : &str  = row.get(1)?;
    let first_name : Option<&str> = row.get(2)?;
    let name = first_name.map_or(last_name.to_string(),
        |first_name| format!("{}, {}", last_name, first_name)
    );
    employees.insert(employee_id, name);
}

There are a few notable points of interest in the last example:

Sibyl uses 0-based column indexing in a projection.
LAST_NAME and FIRST_NAME are retrieved as &str. This is fast as they borrow directly from the respective column buffers. However, those values will only be valid during the lifetime of the row. If the value needs to continue to exist beyond the lifetime of a row, it should be retrieved as a String.

Note that while Sibyl expects 0-based indexes to reference projection columns, it also accepts column names. Thus, the row processing loop of the previous example can be written as:

while let Some( row ) = rows.next()? {
    let employee_id : u32 = row.get("EMPLOYEE_ID")?;
    let last_name : &str  = row.get("LAST_NAME")?;
    let first_name : Option<&str> = row.get("FIRST_NAME")?;
    let name = first_name.map_or(last_name.to_string(),
        |first_name| format!("{}, {}", last_name, first_name)
    );
    employees.insert(employee_id, name);
}

Note that all examples use all upper case column and parameter names. This is not really necessary as Sibyl treat them as case-insensitive. However, using all upper case gives Sibyl a chance to locate a column (or a parameter placeholder) without converting the name to upper case first (to match the Oracle reported names), thus avoiding temporary string allocation and upper case conversion. Of course, you can always maintain an enum for a select list, thus using indexes, which are the speediest way to get to the data anyway.

enum Col { EmployeeId, LastName, FirstName }

while let Some( row ) = rows.next()? {
    let employee_id : u32 = row.get(Col::EmployeeId as usize)?;
    let last_name : &str  = row.get(Col::LastName as usize)?;
    let first_name : Option<&str> = row.get(Col::FirstName as usize)?;
    // ...
}

Or to be extra fancy:

#[derive(Clone,Copy)]
enum Col { EmployeeId, LastName, FirstName }

impl sibyl::Position for Col {
    fn index(&self) -> Option<usize> { Some(*self as _) }
}

impl std::fmt::Display for Col {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        static COLS : [&str;3] = ["EMPLOYEE_ID", "LAST_NAME", "FIRST_NAME"];
        let i = *self as usize;
        f.write_str(COLS[i])
    }
}

while let Some( row ) = rows.next()? {
    let employee_id : u32 = row.get(Col::EmployeeId)?;
    let last_name : &str  = row.get(Col::LastName)?;
    let first_name : Option<&str> = row.get(Col::FirstName)?;
    // ...
}

Of course, that's a lot of boilerplate, which would benefit from a derive macro. Maybe we'll get to that eventually :-)

Statement Arguments

query, query_single and execute take a tuple of arguments (or a single argument). The latter can be specified as positional arguments or as name-value tuples. For example, to execute the above SELECT we can call query using a positional argument as:

let row = stmt.query_single(103)?;

or bind a value to :id by name as:

let row = stmt.query_single((":ID", 103))?;

The leading colon in name part of the name-value argument tuple is optional. Depending on your preferences and/or tooling you might specify parameter placeholder name to bind argument value to without a colon:

let row = stmt.query_single(("ID", 103))?;

Note one caveat - until [min_specialization][1] is stabilized Sibyl has no way of distinguishing whether a 2-item tuple is used to pass a single named argument or 2 positional arguments. For the moment you must use a 3-item tuple with a unit type as the last item when you are passing 2 positional arguments. The unit type is skipped, so effectively only first 2 arguments are used. For example:

let stmt = session.prepare("
    SELECT department_id, manager_id
      FROM hr.departments
     WHERE department_name = :DEPARTMENT_NAME
       AND location_id = :LOCATION_ID
")?;
let rows = stmt.query(( "Administration", 1700, () ))?;

In most cases which binding style to use is a matter of convenience and/or personal preferences. However, in some cases named arguments would be preferable and less ambiguous. For example, statement might change during development and thus force the change in argument positions. Also SQL and PL/SQL statements have different interpretation of a parameter position. SQL statements create positions for every parameter but allow a single argument to be used for the primary parameter and all its duplicates. PL/SQL on the other hand creates positions for unique parameter names and this might make positioning arguments correctly a bit awkward when there is more than one "duplicate" name in a statement.

For example, the following (contrived) INSERT would need its arguments to be bound differently depending on whether it is defined as a standalone SQL or as a (part of a) PL/SQL:

let stmt = session.prepare("
    INSERT INTO hr.locations
        (location_id, state_province, city, postal_code, street_address)
    VALUES
        (:id, :na, :na, :code, :na)
")?;
stmt.execute( (3333, "N/A", (), "00000", ()) )?;
// :NA'a first pos __^---^
// while ___________________^^____and____^^
// are its duplicate positions

The duplicate position can be skipped using () as in the example. However, when it is a part of PL/SQL:

let stmt = session.prepare("
  BEGIN
    INSERT INTO hr.locations
        (location_id, state_province, city, postal_code, street_address)
    VALUES
        (:id, :na, :na, :code, :na);
  END;
")?;
stmt.execute( ( 3333, "N/A", "00000" ) )?;

Only 3 position are possible as there are only 3 unique names.

execute also allows execution of statements with OUT (or INOUT) parameters. For example:

let stmt = session.prepare("
    INSERT INTO hr.departments
           ( department_id, department_name, manager_id, location_id )
    VALUES ( hr.departments_seq.nextval, :department_name, :manager_id, :location_id )
 RETURNING department_id
      INTO :department_id
")?;
let mut department_id: u32 = 0;
let num_inserted = stmt.execute(
    (
        (":DEPARTMENT_NAME", "Security"         ),
        (":MANAGER_ID",      ""                 ),
        (":LOCATION_ID",     1700               ),
        (":DEPARTMENT_ID",   &mut department_id ),
    )
)?;

Using Slices as Statement Arguments

Sibyl accepts a collection of values (as a slice) as a single positional or a named argument. The primary target of this feature is to make passing arguments for IN-list parameters easier. However, as Sibyl simply unrolls a slice into consecutive arguments, this feature can also be "abused" :-) to pass multiple consecutive arguments of the same type when it is convenient.

Example

    let stmt = session.prepare("
        SELECT first_name, last_name, department_name, hire_date
          FROM hr.employees e
          JOIN hr.departments d
            ON d.department_id = e.department_id
         WHERE d.department_name IN (:departments, :2, :3, :4, :5)
           AND d.department_id IN (
                    SELECT department_id
                      FROM hr.employees
                  GROUP BY department_id
                    HAVING Count(*) >= :min_employees )
           AND hire_date BETWEEN :hire_range AND :8
      ORDER BY hire_date, department_name, last_name, first_name
    ")?;
    let date_from = Date::from_string("July      1, 2006", "MONTH DD, YYYY", &session)?;
    let date_thru = Date::from_string("December 31, 2006", "MONTH DD, YYYY", &session)?;

    let rows = stmt.query(
        (
            (":DEPARTMENTS",   ["Marketing", "Purchasing", "Human Resources", "Shipping", "IT"].as_slice()),
            (":MIN_EMPLOYEES", 5),
            (":HIRE_RANGE",    [date_from, date_thru].as_slice()),
        )
    )?;

NULLs as Statement Arguments

There are several, sometimes complementary, ways to pass nulls into and receive them from statements.

Empty Strings Are Nulls

This is idiosyncratic Oracle's way to treat empty strings as nulls.

Example

    let stmt = session.prepare("
        SELECT Nvl(:VAL,'nil') FROM dual
    ")?;
    if let Some(row) = stmt.query_single("")? {
        let val: Option<&str> = row.get(0)?;

        assert!(val.is_some());
        assert_eq!(val.unwrap(), "nil");
    }

When used as an OUT or an INOUT arguments a String or a Varchar does not even need to be wrapped into Option as its length by itself indicates whether Oracle sees it as null or returned its value as null.

Example

    let stmt = session.prepare("
    BEGIN
        IF :VAL IS NULL THEN
            :VAL := 'nil';
        ELSE
            :VAL := NULL;
        END IF;
    END;
    ")?;
    // allocate space for future output
    let mut val = String::with_capacity(4);

    stmt.execute(&mut val)?;
    assert!(!stmt.is_null("VAL")?);
    assert_eq!(val, "nil");

    stmt.execute(&mut val)?;
    assert!(stmt.is_null("VAL")?);
    assert_eq!(val, "");

Using `Option` to Represent Null

Option allows the maximum flexibility in representing input and output null values.

Example

    let stmt = session.prepare("
    BEGIN
        IF :VAL IS NULL THEN
            :VAL := 42;
        ELSE
            :VAL := NULL;
        END IF;
    END;
    ")?;
    let mut val : Option<i32> = None;

    stmt.execute(&mut val)?;
    assert_eq!(val, Some(42));

    stmt.execute(&mut val)?;
    assert!(val.is_none());

Unfortunately, there are a few cases, specifically with INOUT parameters, when an Option cannot represent arguments that are null values as inputs and concrete values on output. To support these Sibyl offers Nvl type.

Using `Nvl` to Represent Null

Nvl wraps a value that would provide a storage for an output value, but it binds it to the parameter placeholder as null.

Example

    let stmt = session.prepare("
    BEGIN
        IF :VAL IS NULL THEN
            :VAL := Utl_Raw.Cast_To_Raw('Hello, World!');
        END IF;
    END;
    ")?;
    let mut buf = [0; 16];
    let mut val = Nvl::new(buf.as_mut_slice());

    stmt.execute(&mut val)?;
    assert!(!stmt.is_null("VAL")?);
    assert!(val.as_ref().is_some());
    assert_eq!(
        val.as_ref().unwrap(),
        &[0x48u8, 0x65, 0x6C, 0x6C, 0x6F, 0x2C, 0x20, 0x57, 0x6F, 0x72, 0x6C, 0x64, 0x21, 0x00, 0x00, 0x00]
        // note the "trailing" initial zeroes are not overwritten by output --------------^^^^--^^^^--^^^^
    );
    let output_len = stmt.len_of("VAL")?;
    let output = &val.as_ref().unwrap()[0..output_len];
    assert_eq!(output, &[0x48u8, 0x65, 0x6C, 0x6C, 0x6F, 0x2C, 0x20, 0x57, 0x6F, 0x72, 0x6C, 0x64, 0x21]);

Similarly INOUT Interval must be wrapped in Nvl if the input value is null while output is expected to be an actual interval.

Dynamic SQL

In cases when the code for an SQL statement is being constructed at runtime as a string, it might be difficult to express the SQL arguments as a tuple. The following approaches allow construction of SQL arguments dynamically with the SQL code and passing them to Sibyl for SQL execution.

Slice of Same Type Arguments

Quite often, when the SQL is built dynamically, argument values are provided out of the process. This way they most likely will arrive as strings. These strings can be collected into a vector and then passed to Sibyl as a slice of strings.

For the sake of clarity the example below does not show the actual process of building the SQL and the vector of arguments. One would have to feel in the gaps.

    // Assume that this was assembled dynamically from bits and pieces
    let sql = String::from("
        SELECT first_name, last_name, department_name, hire_date
          FROM hr.employees e
          JOIN hr.departments d
            ON d.department_id = e.department_id
         WHERE d.department_name IN (:department_name, :dn2, :dn3, :dn4, :dn5)
           AND d.department_id IN (
                    SELECT department_id
                      FROM hr.employees
                  GROUP BY department_id
                    HAVING Count(*) >= :min_employees )
           AND hire_date BETWEEN To_Date(:from_date,'MONTH DD, YYYY')
                             AND To_Date(:thru_date,'MONTH DD, YYYY')
      ORDER BY hire_date
    ");
    // Assume that values for them arrived from elsewhere, and were collected
    // while the above SQL was being constructed
    let mut args = Vec::new();
    args.push("Marketing".to_string());
    args.push("Purchasing".to_string());
    args.push("Human Resources".to_string());
    args.push("Shipping".to_string());
    args.push("IT".to_string());
    args.push("5".to_string());
    args.push("October 1, 2006".to_string());
    args.push("December 31, 2006".to_string());

    let stmt = session.prepare(&sql)?;

    let row = stmt.query_single(args.as_slice())?.expect("single row result");
    let first_name: &str = row.get(0)?;
    let last_name : &str = row.get(1)?;
    let dept_name : &str = row.get(2)?;
    let hire_date : Date = row.get(3)?;

Note that the example above uses only IN parameters. When some of them are OUT or INOUT the slice passed to Sibyl must be mutable, i.e. extracted via as_mut_slice(), and the strings at appropriate positions sized, i.e. created via with_capacity(), to accommodate the maximum length of the expected output.

While this woks, it has a certain, albeit minor, limitation - the SQL code has to expect only arguments of the same type and apply appropriate explicit conversion where appropriate. Sometimes this makes SQL a bit cluttered and somewhat more difficult to read.

`Vec` of Any Type

More strictly - a vector of any type that implements Sibyl's ToSql trait.

    let sql = String::from("
        SELECT first_name, last_name, department_name, hire_date
          FROM hr.employees e
          JOIN hr.departments d
            ON d.department_id = e.department_id
         WHERE d.department_name IN (:department_name, :dn2, :dn3, :dn4, :dn5)
           AND d.department_id IN (
                    SELECT department_id
                      FROM hr.employees
                  GROUP BY department_id
                    HAVING Count(*) >= :min_employees )
           AND hire_date BETWEEN :from_date AND :thru_date
      ORDER BY hire_date
    ");
    // Assume that the arguments where preparsed into the appropriate types
    let mut dept1 = "Marketing";
    let mut dept2 = "Purchasing";
    let mut dept3 = "Human Resources";
    let mut dept4 = "Shipping";
    let mut dept5 = "IT";
    let mut num_emp = 5;
    let mut date_from = Date::from_string("October   1, 2006", "MONTH DD, YYYY", &session)?;
    let mut date_thru = Date::from_string("December 31, 2006", "MONTH DD, YYYY", &session)?;

    let stmt = session.prepare(&sql)?;

    let mut args = Vec::<&mut dyn ToSql>::new();
    args.push(&mut dept1); // :department_name
    args.push(&mut dept2); // :dn2
    args.push(&mut dept3); // :dn3
    args.push(&mut dept4); // :dn4
    args.push(&mut dept5); // :dn5
    args.push(&mut num_emp as &mut dyn ToSql); // :min_employees
    args.push(&mut date_from); // :from_date
    args.push(&mut date_thru); // :thru_date

    let row = stmt.query_single(args)?.expect("single row result");
    let first_name: &str = row.get(0)?;
    let last_name : &str = row.get(1)?;
    let dept_name : &str = row.get(2)?;
    let hire_date : Date = row.get(3)?;

While this allows working with arguments that have appropriate types, it has a limitation on its own - in order to be able to accept arguments for OUT or INOUT parameters this method requires all arguments to be mutable. In a way it treats all of them as INOUT even when that is not actually needed.

Character Sets

When a database character set is anything other than AL32UTF8, UTF8, or US7ASCII, the reported by Oracle character column data size most likely will be smaller than what is required to store the retrieved text encoded as UTF-8 in Rust. To address this issue Sibyl applies a database character set to UTF-8 worst case conversion factor when it allocates memory for column buffers. By default this factor is 1, which works with AL32UTF8, UTF8, and US7ASCII.

In cases when conversion of the database character set to UTF-8 requires more bytes for each character, in the worst case, than is used by the original encoding this conversion factor should be provided to the application via environment variable ORACLE_UTF8_CONV_FACTOR. The factor is an unsigned integer. However, the most likely values for it would be 2, 3, or 4.

Example

A Thai Ko Kai "ก" character in TH8TISASCII encoding is stored as 0xA1. However, it is encoded as 0xE0 0xB8 0x81 in UTF-8. Thus, an application that is connected to the database that uses TH8TISASCII character set needs to use a conversion factor 3. To run such an application one would need to set ORACLE_UTF8_CONV_FACTOR before executing it:

export ORACLE_UTF8_CONV_FACTOR=3

Oracle Data Types

Sibyl provides API to access several Oracle native data types.

VARCHAR

fn main() -> sibyl::Result<()> {
use sibyl::Varchar;

let env = sibyl::env()?;

let txt = Varchar::from("Hello, World!", &env)?;

assert_eq!(txt.as_str(), "Hello, World!");
}

Number

use sibyl::Number;

let oracle = sibyl::env()?;

let pi = Number::pi(&oracle);
let two = Number::from_int(2, &oracle);
let two_pi = pi.mul(&two)?;
let h = Number::from_string("6.62607004E-34", "9D999999999EEEE", &oracle)?;
let hbar = h.div(&two_pi)?;

assert_eq!(
    hbar.to_string("TME")?,
    "1.05457180013911265115394106872506677375E-34"
);

RAW

fn main() -> sibyl::Result<()> {
use sibyl::Raw;

let env = sibyl::env()?;

let raw = Raw::from_bytes(&[1u8,2,3,4,5], &env)?;

assert_eq!(raw.as_bytes(), &[1u8,2,3,4,5]);
}

Date

use sibyl::Date;

let mar28_1996 = Date::from_string("28-MAR-1996", "DD-MON-YYYY", &oracle)?;
let next_monday = mar28_1996.next_week_day("MONDAY")?;

assert_eq!(next_monday.to_string("DL")?, "Monday, April 01, 1996");

Timestamp

There are 3 types of timestamps:

Timestamp which is equivalent to Oracle TIMESTAMP data type,
TimestampTZ - TIMESTAMP WITH TIME ZONE, and
TimestampLTZ - TIMESTAMP WITH LOCAL TIME ZONE

use sibyl::TimestampTZ;

let ts = oracle::TimestampTZ::from_string(
    "July 20, 1969 8:18:04.16 pm UTC",
    "MONTH DD, YYYY HH:MI:SS.FF PM TZR",
    &oracle
)?;
assert_eq!(
    ts.to_string("YYYY-MM-DD HH24:MI:SS.FF TZR", 3)?,
    "1969-07-20 20:18:04.160 UTC"
);

Note that if you are getting ORA-01805 when timestamp with time zone is used, then most likely your local client and the server it is connected to are using different versions of the time zone file. This stackoverflow answer should help you in setting up your local client with the correct time zone file.

Interval

There are 2 types of intervals:

IntervalYM which is eqivalent to Oracle's INTERVAL YEAR TO MONTH,
IntervalDS - INTERVAL DAY TO SECOND

use sibyl::{ TimestampTZ, IntervalDS };

let launch  = TimestampTZ::with_date_and_time(1969, 7, 16, 13, 32,  0, 0, "UTC", &oracle)?;
let landing = TimestampTZ::with_date_and_time(1969, 7, 24, 16, 50, 35, 0, "UTC", &oracle)?;
let duration : IntervalDS = landing.subtract(&launch)?;

assert_eq!(duration.to_string(1,3)?, "+8 03:18:35.000");

Row ID

Oracle ROWID can be selected and retrieved explicitly into an instance of the RowID. However, one interesting case is SELECT FOR UPDATE queries where Oracle returns ROWIDs implicitly. Those can be retrieved using Row::rowid method.

let stmt = session.prepare("
    SELECT manager_id
      FROM hr.employees
     WHERE employee_id = :id
       FOR UPDATE
")?;
let row = stmt.query_single(107)?;
let rowid = row.rowid()?;

let manager_id: u32 = row.get(0)?;
assert_eq!(manager_id, 103);

let stmt = session.prepare("
    UPDATE hr.employees
       SET manager_id = :manager_id
     WHERE rowid = :row_id
")?;
let num_updated = stmt.execute((
    ( ":MANAGER_ID", 102 ),
    ( ":ROW_ID",  &rowid ),
))?;
assert_eq!(num_updated, 1);

Cursor

Cursors can be returned explicitly:

let stmt = session.prepare("
    BEGIN
        OPEN :emp FOR
            SELECT department_name, first_name, last_name, salary
              FROM hr.employees e
              JOIN hr.departments d
                ON d.department_id = e.department_id;
    END;
")?;
let mut cursor = Cursor::new(&stmt)?;
stmt.execute(&mut cursor)?;
let rows = cursor.rows()?;
// ...

Or, beginning with Oracle 12.1, implicitly:

let stmt = session.prepare("
    DECLARE
        emp SYS_REFCURSOR;
    BEGIN
        OPEN emp FOR
            SELECT department_name, first_name, last_name, salary
              FROM hr.employees e
              JOIN hr.departments d
                ON d.department_id = e.department_id;
        ;
        DBMS_SQL.RETURN_RESULT(emp);
    END;
")?;
stmt.execute(())?;
if let Some( cursor ) = stmt.next_result()? {
    let rows = cursor.rows()?;
    // ...
}

CLOBs, BLOBs, BFILEs

Let's assume a table was created:

CREATE TABLE lob_example (
    id  NUMBER GENERATED ALWAYS AS IDENTITY,
    bin BLOB
);

We can then create and write data into that LOB as:

// ... create OCI environment, connect to the database, etc.

let file = BFile::new(&session)?;
file.set_file_name("MEDIA_DIR", "mousepad_comp_ad.pdf")?;
let file_len = file.len()?;

file.open_file()?;
let mut data = Vec::new();
let num_read = file.read(0, file_len, &mut data)?;
file.close_file()?;
// ... or do not close now as it will be closed
// automatically when `file` goes out of scope

// Insert new BLOB and lock its row
let stmt = session.prepare("
    DECLARE
        row_id ROWID;
    BEGIN
        INSERT INTO lob_example (bin) VALUES (Empty_Blob()) RETURNING rowid INTO row_id;
        SELECT bin INTO :NEW_BLOB FROM lob_example WHERE rowid = row_id FOR UPDATE;
    END;
")?;
let mut lob = BLOB::new(&session)?;
stmt.execute(&mut lob)?;

lob.open()?;
let num_bytes_written = lob.write(0, &data)?;
lob.close()?;

session.commit()?;

And then later it could be read as:

let id: usize = 1234; // assume it was retrieved from somewhere...
let stmt = session.prepare("SELECT bin FROM lob_example WHERE id = :ID")?;
let row = stmt.query_single(&id)?;
if let Some(row) = row {
    if let Some(lob) = row.get(0)? {
        let data = read_blob(lob)?;
        // ...
    }
}

// Where `read_blob` could be this:
fn read_blob(lob: BLOB<'_>) -> Result<Vec<u8>> {
    let mut data = Vec::new();
    let lob_len = lob.len()?;
    let offset = 0;
    lob.read(offset, lob_len, &mut data)?;
    Ok(data)
}

Known Issues with Some Clients

12.2 client does not support OCI_ATTR_SPOOL_MAX_USE_SESSION and thus SessionPool's session_max_use_count and set_session_max_use_count will fail on it with ORA-24315: illegal attribute type.

19.15 and all later clients do not return current schema (via Session::current_schema) until it is explicitly set via Session::set_current_schema. Session::current_schema works as expected in 19.13 client.

21c clients (at least with 19.3 database) is strangely picky about names of parameter placeholders for LOB columns. For example, if a table was created with the following LOB column:

CREATE TABLE table_with_lob (
    id   NUMBER GENERATED ALWAYS AS IDENTITY,
    txt  CLOB
);

and if an SQL parameter name is the same as the LOB column name (as in this example):

let stmt = session.prepare("
    INSERT INTO table_with_lob (txt) VALUES (:TXT) RETURNING id INTO :ID
")?;

Then 21c clients will fail executing this SQL with ORA-03120: two-task conversion routine: integer overflow. Renaming the parameter placeholder resolves this:

let stmt = session.prepare("
    INSERT INTO table_with_lob (txt) VALUES (:NEW_TXT) RETURNING id INTO :ID
")?;

21c clients also do not "like" some specific parameter names like :NAME which makes it fail with the same ORA-03120.

Note that 12.2 through 19.18 clients (as far as Sibyl's tests showed) do not exhibit this issue.

21c clients (at least when connected to the 19.3 database) cannot read LOBs piece-wize - something bad happens while it reads the very last byte of the last piece and the execution is aborted with SIGSEGV. Notably, they have no problem reading the second to last piece even if it has all the bytes but the very last one. Subsequently, an attempt to read the last one-byte piece gets aborted with memory violation.

All tested clients behave erratically in nonblocking mode when they execute piece-wize LOB operations. Therefore, in nonblocking mode Sibyl does not support LOBs piece-wise reading and writing.

Limitations

At this time Sibyl provides only the most commonly needed means to interface with the Oracle database. Some of the missing features are:

Array interface for multi-row operations
User defined data types
PL/SQL collections and tables
Objects
JSON data
LDAP and proxy authentications
Global transactions
High Availability
Continuous query and publish-subscribe notifications
Advanced queuing
Shards
Direct path load

Some of these features might be added in the upcoming releases if the need arises or if they are explicitly requested. Some, however, will never be implemented. The latter category includes those that are incompatible with nonblocking execution.

Testing

Some of Sibyl's tests connect to the database and expect certain objects to exist in it and certain privileges granted:

At least the HR demo schema should be installed.
While there is no need to install other demo schemas at least MEDIA_DIR should be created (see $ORACLE_HOME/demo/schema/mk_dir.sql) and point to the directory with demo files. The latter can be found in product_media in the db-sample-schemas.zip.
Some of the LOB tests need text files with the the expected content. Those can be found in etc/media and copied into MEDIA_DIR.
A test user should be created. That user needs access to the HR schema and to the MEDIA_DIR directory. See etc/create_sandbox.sql for an example of how it can be accomplished.
The test user needs SELECT access to V$SESSION as some tests use it for validation.

GRANT SELECT ON V_$SESSION TO sibyl;

Tests that connect to the database use environment variables - DBNAME, DBUSER and DBPASS - to identify the database, user and password respectively. These variables should be set before executing cargo test.

Sibyl User Manual