Oracle #

Oracle Database is one of the most widely used database systems in large enterprise environments — banking, telecommunications, and government. Accessing it from Rust has its own challenges: unlike MySQL or PostgreSQL which have pure Rust drivers, Oracle requires installing Oracle Instant Client (Oracle’s C library) as a system dependency. This makes setup more complex but doesn’t prevent production use. The main crate used is oracle — Rust bindings to the ODPI-C library. This article covers the complete setup from scratch, connections, queries, PL/SQL syntax differences, and common patterns for enterprise environments.

Prerequisite: Oracle Instant Client #

Before compiling Rust code that uses oracle, you must install Oracle Instant Client on your system:

# Linux (Ubuntu/Debian) — download from https://www.oracle.com/database/technologies/instant-client/downloads.html
# Extract to /opt/oracle/instantclient_21_x

# Set environment variables
export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_x:$LD_LIBRARY_PATH
export ORACLE_HOME=/opt/oracle/instantclient_21_x

# To connect to a database without tnsnames.ora
# you can use Easy Connect syntax directly in the connection string

# macOS — download the DMG from Oracle, or use brew
brew tap InstantClientTap/instantclient
brew install instantclient-basic

export DYLD_LIBRARY_PATH=$(brew --prefix)/lib:$DYLD_LIBRARY_PATH

# Windows — download the ZIP from Oracle, extract to C:\oracle\instantclient_21_x
# Add it to PATH
set PATH=C:\oracle\instantclient_21_x;%PATH%

Oracle Instant Client is Oracle’s proprietary library and requires an Oracle account to download. This is the only way to connect to Oracle Database from any programming language (including Python, Java, Node.js). Make sure the Instant Client version is compatible with the Oracle Database version you’re using.

Installation #

[dependencies]
oracle = "0.5"
tokio = { version = "1", features = ["full"] }
serde = { version = "1", features = ["derive"] }
chrono = "0.4"

The oracle crate is currently synchronous — there’s no native async support. To use it in an async application, run database operations in tokio::task::spawn_blocking or use a separate thread pool.

Oracle Connection Strings #

Oracle uses several connection formats:

// Easy Connect (without tnsnames.ora) — the simplest
"//hostname:port/service_name"
"//localhost:1521/XEPDB1"
"//prod-db.company.com:1521/ORCL"

// Easy Connect with extra options
"//hostname:port/service_name?connect_timeout=10"

// With a TNS alias (requires tnsnames.ora configured)
"TNS_ALIAS"

// SID (for older databases)
"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=orcl)))"

// Oracle Cloud (ATP/ADW) — using a wallet
"(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.region.oraclecloud.com))(connect_data=(service_name=xxxxx_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))"

Basic Connections #

use oracle::{Connection, Error};

fn buat_koneksi(
    username: &str,
    password: &str,
    connect_string: &str,
) -> Result<Connection, Error> {
    Connection::connect(username, password, connect_string)
}

fn main() -> Result<(), Error> {
    // Connect to local Oracle XE
    let conn = buat_koneksi(
        "system",
        "oracle",
        "//localhost:1521/XEPDB1",
    )?;

    println!("Connected to Oracle Database");

    // Verify the connection
    let mut stmt = conn.statement("SELECT 'OK' FROM DUAL").build()?;
    let rows = stmt.query(&[])?;
    for row_result in rows {
        let row = row_result?;
        let status: String = row.get(0)?;
        println!("Status: {}", status);
    }

    Ok(())
}

Connection Pools #

The oracle crate doesn’t provide a built-in connection pool. Use the r2d2 or deadpool crate for pooling:

[dependencies]
oracle = "0.5"
r2d2 = "0.8"
r2d2-oracle = "0.2"
tokio = { version = "1", features = ["full"] }

use oracle::Connection;
use r2d2::Pool;
use r2d2_oracle::OracleConnectionManager;
use std::sync::Arc;

type OraclePool = Pool<OracleConnectionManager>;

fn buat_pool(
    username: &str,
    password: &str,
    connect_string: &str,
    maks_koneksi: u32,
) -> Result<OraclePool, Box<dyn std::error::Error>> {
    let manager = OracleConnectionManager::new(username, password, connect_string);
    let pool = r2d2::Pool::builder()
        .max_size(maks_koneksi)
        .min_idle(Some(2))
        .connection_timeout(std::time::Duration::from_secs(10))
        .build(manager)?;
    Ok(pool)
}

// Since oracle is sync, wrap it with spawn_blocking for async applications
async fn dengan_pool_async(
    pool: Arc<OraclePool>,
) -> Result<Vec<String>, Box<dyn std::error::Error + Send + Sync>> {
    tokio::task::spawn_blocking(move || {
        let conn = pool.get()?;
        let mut stmt = conn.statement("SELECT nama FROM pengguna WHERE ROWNUM <= 10")
            .build()?;
        let rows = stmt.query(&[])?;
        let mut hasil = Vec::new();
        for row_result in rows {
            let row = row_result?;
            let nama: String = row.get(0)?;
            hasil.push(nama);
        }
        Ok(hasil)
    })
    .await?
}

Creating Tables — Oracle Syntax #

-- Sequence for auto-increment (Oracle < 12c)
CREATE SEQUENCE seq_pengguna
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Oracle 12c+ supports IDENTITY directly
CREATE TABLE pengguna (
    id          NUMBER(19)      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama        NVARCHAR2(100)  NOT NULL,
    email       NVARCHAR2(255)  NOT NULL,
    password    VARCHAR2(255)   NOT NULL,
    peran       VARCHAR2(20)    DEFAULT 'user' NOT NULL
                                CHECK (peran IN ('user', 'admin', 'moderator')),
    aktif       NUMBER(1)       DEFAULT 1 NOT NULL
                                CHECK (aktif IN (0, 1)),
    dibuat_pada TIMESTAMP       DEFAULT SYSTIMESTAMP NOT NULL,
    diperbarui  TIMESTAMP       DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT uq_pengguna_email UNIQUE (email)
);

CREATE TABLE artikel (
    id              NUMBER(19)      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pengguna_id     NUMBER(19)      NOT NULL,
    judul           NVARCHAR2(255)  NOT NULL,
    konten          CLOB            NOT NULL,
    diterbitkan     NUMBER(1)       DEFAULT 0 NOT NULL,
    dibuat_pada     TIMESTAMP       DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT fk_artikel_pengguna FOREIGN KEY (pengguna_id)
        REFERENCES pengguna(id) ON DELETE CASCADE
);

-- Indexes
CREATE INDEX idx_artikel_pengguna ON artikel(pengguna_id);
CREATE INDEX idx_artikel_diterbitkan ON artikel(diterbitkan, dibuat_pada DESC);

Structs and Oracle Data Types #

use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};

#[derive(Debug, Serialize, Deserialize, Clone)]
struct Pengguna {
    pub id: i64,                    // NUMBER(19) IDENTITY → i64
    pub nama: String,               // NVARCHAR2 → String
    pub email: String,
    pub password: String,
    pub peran: String,              // VARCHAR2 → String
    pub aktif: bool,                // NUMBER(1) → bool (0/1 to false/true)
    pub dibuat_pada: NaiveDateTime, // TIMESTAMP → NaiveDateTime
    pub diperbarui: NaiveDateTime,
}

#[derive(Debug, Serialize)]
struct Artikel {
    pub id: i64,
    pub pengguna_id: i64,
    pub judul: String,
    pub konten: String,             // CLOB → String
    pub diterbitkan: bool,
    pub dibuat_pada: NaiveDateTime,
}

Oracle Type	Rust Type
`NUMBER(n)`	`i32`, `i64`, `f64` (depending on precision)
`NUMBER(p,s)`	`f64` or `rust_decimal::Decimal`
`VARCHAR2(n)`	`String`
`NVARCHAR2(n)`	`String`
`CHAR(n)`	`String`
`CLOB`	`String`
`BLOB`	`Vec<u8>`
`DATE`	`chrono::NaiveDateTime`
`TIMESTAMP`	`chrono::NaiveDateTime`
`TIMESTAMP WITH TIME ZONE`	`chrono::DateTime<Utc>`
`NUMBER(1)` as bool	`i32` (manual conversion to `bool`)

Queries and Fetching #

use oracle::{Connection, Error, Row};

fn ambil_semua_pengguna(conn: &Connection) -> Result<Vec<Pengguna>, Error> {
    let mut stmt = conn.statement(
        "SELECT id, nama, email, password, peran, aktif, dibuat_pada, diperbarui
         FROM pengguna
         ORDER BY dibuat_pada DESC"
    ).build()?;

    let rows = stmt.query(&[])?;
    let mut pengguna_list = Vec::new();

    for row_result in rows {
        let row = row_result?;
        let aktif_num: i32 = row.get("AKTIF")?;  // Oracle returns NUMBER, not bool
        pengguna_list.push(Pengguna {
            id: row.get("ID")?,
            nama: row.get("NAMA")?,
            email: row.get("EMAIL")?,
            password: row.get("PASSWORD")?,
            peran: row.get("PERAN")?,
            aktif: aktif_num != 0,  // convert NUMBER(1) → bool
            dibuat_pada: row.get("DIBUAT_PADA")?,
            diperbarui: row.get("DIPERBARUI")?,
        });
    }

    Ok(pengguna_list)
}

fn cari_pengguna_by_id(conn: &Connection, id: i64) -> Result<Option<Pengguna>, Error> {
    let mut stmt = conn.statement(
        "SELECT id, nama, email, password, peran, aktif, dibuat_pada, diperbarui
         FROM pengguna
         WHERE id = :id"  // Oracle uses :nama for named parameters
    ).build()?;

    let rows = stmt.query_named(&[("id", &id)])?;

    for row_result in rows {
        let row = row_result?;
        let aktif_num: i32 = row.get("AKTIF")?;
        return Ok(Some(Pengguna {
            id: row.get("ID")?,
            nama: row.get("NAMA")?,
            email: row.get("EMAIL")?,
            password: row.get("PASSWORD")?,
            peran: row.get("PERAN")?,
            aktif: aktif_num != 0,
            dibuat_pada: row.get("DIBUAT_PADA")?,
            diperbarui: row.get("DIPERBARUI")?,
        }));
    }

    Ok(None)
}

INSERT with `RETURNING INTO` #

Oracle uses RETURNING INTO to get values after an INSERT — similar to OUTPUT INSERTED in MSSQL:

fn buat_pengguna(
    conn: &Connection,
    nama: &str,
    email: &str,
    password_hash: &str,
) -> Result<i64, Error> {
    // RETURNING INTO to get the newly created ID
    let mut stmt = conn.statement(
        "INSERT INTO pengguna (nama, email, password)
         VALUES (:nama, :email, :password)
         RETURNING id INTO :id_baru"
    ).build()?;

    stmt.execute_named(&[
        ("nama", &nama),
        ("email", &email),
        ("password", &password_hash),
        ("id_baru", &None::<i64>),  // output parameter
    ])?;

    // Get the output parameter value
    let id_baru: i64 = stmt.returned_value(0, 0)?;
    conn.commit()?;

    Ok(id_baru)
}

// Alternative with a sequence (for Oracle < 12c without IDENTITY)
fn buat_pengguna_sequence(
    conn: &Connection,
    nama: &str,
    email: &str,
    password_hash: &str,
) -> Result<i64, Error> {
    // Get the next sequence value
    let mut stmt = conn.statement("SELECT seq_pengguna.NEXTVAL FROM DUAL").build()?;
    let rows = stmt.query(&[])?;
    let mut id_baru: i64 = 0;
    for row in rows {
        id_baru = row?.get(0)?;
    }

    // Insert with the already known ID
    conn.execute(
        "INSERT INTO pengguna (id, nama, email, password) VALUES (:1, :2, :3, :4)",
        &[&id_baru, &nama, &email, &password_hash],
    )?;
    conn.commit()?;

    Ok(id_baru)
}

UPDATE and DELETE #

fn perbarui_pengguna(
    conn: &Connection,
    id: i64,
    nama: &str,
    email: &str,
) -> Result<bool, Error> {
    // Oracle has no SYSDATETIME, use SYSTIMESTAMP
    let baris_terpengaruh = conn.execute_named(
        "UPDATE pengguna
         SET nama = :nama, email = :email, diperbarui = SYSTIMESTAMP
         WHERE id = :id",
        &[("nama", &nama), ("email", &email), ("id", &id)],
    )?;

    conn.commit()?;
    Ok(baris_terpengaruh > 0)
}

fn hapus_pengguna(conn: &Connection, id: i64) -> Result<bool, Error> {
    let baris_terpengaruh = conn.execute(
        "DELETE FROM pengguna WHERE id = :1",
        &[&id],
    )?;

    conn.commit()?;
    Ok(baris_terpengaruh > 0)
}

Transactions #

Important: Oracle does not auto-commit. Every new connection starts in transactional mode and requires an explicit COMMIT or ROLLBACK:

fn transfer_data(
    conn: &Connection,
    dari_id: i64,
    ke_id: i64,
    jumlah: f64,
) -> Result<(), Error> {
    // No BEGIN needed — Oracle is always in a transaction

    // Lock the row with SELECT FOR UPDATE
    let mut stmt = conn.statement(
        "SELECT saldo FROM akun WHERE id = :id FOR UPDATE"
    ).build()?;

    let rows = stmt.query_named(&[("id", &dari_id)])?;
    let mut saldo: f64 = 0.0;
    for row in rows {
        saldo = row?.get(0)?;
    }

    if saldo < jumlah {
        conn.rollback()?;
        return Err(Error::OciError(oracle::OciError::new(
            20001,
            "Saldo tidak mencukupi",
        )));
    }

    conn.execute(
        "UPDATE akun SET saldo = saldo - :1 WHERE id = :2",
        &[&jumlah, &dari_id],
    )?;

    conn.execute(
        "UPDATE akun SET saldo = saldo + :1 WHERE id = :2",
        &[&jumlah, &ke_id],
    )?;

    // Explicit COMMIT
    conn.commit()?;
    println!("Transfer succeeded");
    Ok(())
}

Stored Procedures and PL/SQL #

Oracle relies heavily on PL/SQL. Here’s how to call stored procedures and anonymous blocks:

-- Oracle stored procedure
CREATE OR REPLACE PROCEDURE sp_buat_pengguna(
    p_nama      IN  pengguna.nama%TYPE,
    p_email     IN  pengguna.email%TYPE,
    p_password  IN  pengguna.password%TYPE,
    p_id_baru   OUT pengguna.id%TYPE
) AS
BEGIN
    INSERT INTO pengguna (nama, email, password)
    VALUES (p_nama, p_email, p_password)
    RETURNING id INTO p_id_baru;
    COMMIT;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        RAISE_APPLICATION_ERROR(-20001, 'Email sudah terdaftar');
END;
/

fn panggil_sp_buat_pengguna(
    conn: &Connection,
    nama: &str,
    email: &str,
    password: &str,
) -> Result<i64, Error> {
    // Call the stored procedure with an OUT parameter
    let mut stmt = conn.statement(
        "BEGIN sp_buat_pengguna(:nama, :email, :password, :id_baru); END;"
    ).build()?;

    stmt.execute_named(&[
        ("nama", &nama),
        ("email", &email),
        ("password", &password),
        ("id_baru", &None::<i64>),
    ])?;

    let id_baru: i64 = stmt.returned_value(0, 0)?;
    Ok(id_baru)
}

// Anonymous PL/SQL block
fn jalankan_plsql(conn: &Connection, id: i64) -> Result<(), Error> {
    conn.execute(
        "BEGIN
            UPDATE pengguna SET aktif = 0 WHERE id = :1;
            -- Log to the audit table
            INSERT INTO audit_log (aksi, target_id, waktu)
            VALUES ('NONAKTIFKAN', :1, SYSTIMESTAMP);
         END;",
        &[&id],
    )?;
    conn.commit()?;
    Ok(())
}

Pagination — Differences from MySQL and MSSQL #

-- MySQL
SELECT * FROM pengguna ORDER BY id LIMIT 10 OFFSET 20;

-- MSSQL
SELECT * FROM pengguna ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle 12c+
SELECT * FROM pengguna ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- same as MSSQL!

-- Oracle 11g and earlier (without FETCH FIRST)
SELECT * FROM (
    SELECT p.*, ROWNUM rn FROM (
        SELECT * FROM pengguna ORDER BY id
    ) p WHERE ROWNUM <= 30  -- offset + limit
) WHERE rn > 20;  -- remove rows before the offset

fn daftar_pengguna_halaman(
    conn: &Connection,
    halaman: i64,
    per_halaman: i64,
) -> Result<Vec<Pengguna>, Error> {
    let offset = (halaman.saturating_sub(1)) * per_halaman;

    // Oracle 12c+ uses the same syntax as MSSQL
    let mut stmt = conn.statement(
        "SELECT id, nama, email, password, peran, aktif, dibuat_pada, diperbarui
         FROM pengguna
         ORDER BY id DESC
         OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY"
    ).build()?;

    let rows = stmt.query_named(&[("offset", &offset), ("limit", &per_halaman)])?;
    let mut hasil = Vec::new();

    for row_result in rows {
        let row = row_result?;
        let aktif_num: i32 = row.get("AKTIF")?;
        hasil.push(Pengguna {
            id: row.get("ID")?,
            nama: row.get("NAMA")?,
            email: row.get("EMAIL")?,
            password: row.get("PASSWORD")?,
            peran: row.get("PERAN")?,
            aktif: aktif_num != 0,
            dibuat_pada: row.get("DIBUAT_PADA")?,
            diperbarui: row.get("DIPERBARUI")?,
        });
    }

    Ok(hasil)
}

Important Differences from MySQL and MSSQL #

MySQL/MSSQL                     Oracle

Auto-commit
  Yes (MySQL default)           No — always needs explicit COMMIT
  Yes (MSSQL per statement)     Same — ROLLBACK if you don't want to save

Parameter binding
  ? (MySQL)                     :nama or :1, :2, :3 (positional)
  @P1 (MSSQL)

Getting the ID after INSERT
  LAST_INSERT_ID() (MySQL)      RETURNING id INTO :var
  OUTPUT INSERTED.id (MSSQL)    or SELECT seq.NEXTVAL FROM DUAL beforehand

Dual table
  Doesn't exist                 SELECT 'nilai' FROM DUAL (required for literals)

Time functions
  NOW() / GETDATE()             SYSDATE (no time) / SYSTIMESTAMP (with time)

NULL concatenation
  NULL + 'a' = NULL (MySQL)     NULL || 'a' = 'a' (Oracle: NULL ignored in ||)

Empty string vs NULL
  '' ≠ NULL                     '' = NULL in Oracle!

Limit/paging
  LIMIT n (MySQL)               FETCH FIRST n ROWS ONLY (Oracle 12c+)
  FETCH NEXT n (MSSQL)          or ROWNUM <= n (Oracle 11g)

Boolean
  TINYINT(1) / BIT              No boolean type — use NUMBER(1)

Identifier case
  Case-insensitive (MySQL)      Case-insensitive except in quotes
  Case-insensitive (MSSQL)      Uppercase internally (nama → NAMA)

In Oracle, the empty string '' equals NULL — this differs from every other database. If you store an empty string, Oracle stores it as NULL. Always use IS NULL rather than = '' to check for empty values in Oracle.

Summary #

Oracle Instant Client must be installed — it’s Oracle’s C library required by all Oracle drivers, including the oracle crate. Set LD_LIBRARY_PATH (Linux) or DYLD_LIBRARY_PATH (macOS) to the Instant Client directory.
The oracle crate is synchronous — for async applications, use tokio::task::spawn_blocking so database operations don’t block the tokio event loop.
Parameter binding uses :nama or :1 — Oracle uses named parameters (:nama) or positional ones (:1, :2), not ? (MySQL) or @P1 (MSSQL).
RETURNING id INTO :var to get the ID after INSERT — Oracle has no LAST_INSERT_ID(). Use RETURNING INTO or a sequence NEXTVAL before the INSERT.
There’s no auto-commit — every DML operation (INSERT/UPDATE/DELETE) must be followed by an explicit COMMIT or it will be rolled back when the connection closes.
The empty string '' = NULL in Oracle — there’s no empty string value in Oracle. Store a single space ' ' if you need a non-NULL “empty” value, or redesign the schema.
Column names are uppercased internally — access columns with row.get("ID") not row.get("id"). Oracle identifiers are case-insensitive but stored as uppercase.
NUMBER(1) as a boolean — Oracle has no BOOL type. Convert manually: let aktif: i32 = row.get("AKTIF")?; let aktif_bool = aktif != 0;
Oracle 12c+ pagination is the same as MSSQL — FETCH FIRST/NEXT n ROWS ONLY. For Oracle 11g and older, use nested subqueries with ROWNUM.

← Previous: MSSQL Next: PostgreSQL →