Oraffi - Oracle Database Driver for Dart

A high-performance Oracle database driver for Dart, using FFI to interface with Oracle's ODPI-C library. Provides a clean, type-safe API with support for connection pooling, transactions, stored procedures, LOBs, and more.

Features

• High Performance - Direct FFI bindings to Oracle ODPI-C
• Connection Pooling - Built-in pool with configurable min/max connections
• Prepared Statements - Parameterized queries to prevent SQL injection
• Transaction Management - Full commit/rollback control with isolation levels
• Stored Procedures - Call procedures with IN/OUT/INOUT parameters
• REF CURSOR - Return result sets from stored procedures
• LOB Support - CLOB, BLOB, NCLOB read/write operations
• Batch Operations - executemany() for bulk inserts (10-100x faster)
• UTF-8 Support - Full international character and emoji support
• Type Safety - Strong typing with comprehensive error handling

Requirements

This package requires two native libraries:

1. Oracle Instant Client - Oracle's client libraries (from Oracle)
2. ODPI-C - Oracle Database Programming Interface for C (build from source)

Step 1: Oracle Instant Client

Download from Oracle (free registration required): https://www.oracle.com/database/technologies/instant-client/downloads.html

macOS

# Download "Basic Package" DMG from Oracle for your architecture (Intel or ARM64)
# Mount and copy to /usr/local/oracle:
sudo mkdir -p /usr/local/oracle
sudo cp -r /Volumes/instantclient-basic-macos.arm64-*/. /usr/local/oracle/instantclient

# Set library path (add to ~/.zshrc or ~/.bashrc)
export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient:$DYLD_LIBRARY_PATH"

Linux

# Install libaio (required dependency)
sudo apt-get install libaio1  # Debian/Ubuntu
sudo yum install libaio       # RHEL/CentOS

# Download and extract Basic Package
mkdir -p ~/oracle
cd ~/oracle
unzip instantclient-basic-linux.x64-*.zip

# Set library path (add to ~/.bashrc)
export LD_LIBRARY_PATH="$HOME/oracle/instantclient_23_5:$LD_LIBRARY_PATH"

Windows

# Download and extract Basic Package to C:\oracle\instantclient
# Add to System PATH: C:\oracle\instantclient

Step 2: ODPI-C Library

ODPI-C must be built from source (Oracle does not provide pre-built binaries).

macOS / Linux

# Clone and build ODPI-C
git clone https://github.com/oracle/odpi.git
cd odpi
make

# Install (copies libodpic to /usr/local/lib)
sudo make install

# On Linux, update library cache
sudo ldconfig

Windows

# Requires Visual Studio Build Tools
git clone https://github.com/oracle/odpi.git
cd odpi
nmake -f Makefile.win32

# Copy odpic.dll to your PATH or application directory

Verify Installation

# Check ODPI-C is installed
ls -la /usr/local/lib/libodpic*

# Check Oracle Instant Client
ls -la /usr/local/oracle/instantclient/libclntsh*

Installation

Add to your pubspec.yaml:

dependencies:
  oraffi: ^1.0.0

dart pub get

Quick Start

Option 1: Direct Parameters (Recommended for Development)

import 'package:oraffi/oraffi.dart';

Future<void> main() async {
  final db = OracleDb();

  // Initialize Oracle client (auto-finds ODPI-C library)
  await db.initOracleClient();

  // OR specify ODPI-C library path explicitly
  // await db.initOracleClient(libraryPath: '/usr/local/lib/libodpic.dylib');

  // Connect with direct parameters
  final conn = await db.createConnection(
    config: OracleConfig.create(
      username: 'myuser',
      password: 'mypassword',
      connectionString: 'localhost:1521/XEPDB1',
    ),
  );

  try {
    // Query data
    final results = await conn.query('SELECT * FROM employees WHERE department_id = :1', params: [10]);

    for (final row in results) {
      print('${row['FIRST_NAME']} ${row['LAST_NAME']}');
    }
  } finally {
    await conn.close();
  }

  await db.shutdown();
}

Option 2: Environment Variables (Recommended for Production)

# Set environment variables
export ORACLE_USER="myuser"
export ORACLE_PASSWORD="mypassword"
export ORACLE_CONNECTION_STRING="localhost:1521/XEPDB1"

import 'package:oraffi/oraffi.dart';

Future<void> main() async {
  final db = OracleDb();
  await db.initOracleClient();

  // Load config from environment
  final conn = await db.createConnection(
    config: OracleConfig.fromEnvironment(),
  );

  try {
    final results = await conn.query('SELECT SYSDATE FROM DUAL');
    print('Server time: ${results.first['SYSDATE']}');
  } finally {
    await conn.close();
  }

  await db.shutdown();
}

Connection Configuration

OracleConfig Options

final config = OracleConfig.create(
  username: 'myuser',
  password: 'mypassword',
  connectionString: 'host:port/service_name',  // Simple format
  // OR
  // connectionString: '//host:port/service_name',  // Easy Connect
  // OR
  // connectionString: 'mydb',  // TNS alias (requires tnsnames.ora)
);

Connection String Formats

Format	Example
Simple	localhost:1521/XEPDB1
Easy Connect	//localhost:1521/XEPDB1
TNS Alias	MYDB (requires tnsnames.ora)
Full TNS	(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=...)))

Connection Pooling

final db = OracleDb();
await db.initOracleClient();

// Create a connection pool
await db.createPool(
  config: OracleConfig.create(
    username: 'myuser',
    password: 'mypassword',
    connectionString: 'localhost:1521/XEPDB1',
  ),
  poolConfig: PoolConfig(
    poolMin: 2,
    poolMax: 10,
    poolIncrement: 1,
  ),
);

// Get connection from pool
final conn = await db.getConnection();
try {
  final results = await conn.query('SELECT * FROM users');
  // Process results...
} finally {
  await conn.close();  // Returns to pool
}

// Check pool statistics
final pool = db.getPool();
final stats = await pool!.getStatistics();
print('Open: ${stats.openConnections}, Busy: ${stats.busyConnections}');

await db.shutdown();

Parameterized Queries

Positional Parameters

// Use :1, :2, :3 placeholders (Oracle native syntax)
final results = await conn.query(
  'SELECT * FROM employees WHERE department_id = :1 AND salary > :2',
  params: [10, 50000],
);

Named Parameters

// Use :name placeholders
final results = await conn.query(
  'SELECT * FROM employees WHERE department_id = :dept_id AND salary > :min_salary',
  namedParams: {'dept_id': 10, 'min_salary': 50000},
);

Transactions

try {
  await conn.executeUpdate('INSERT INTO accounts (id, balance) VALUES (:1, :2)', params: [1, 1000]);
  await conn.executeUpdate('INSERT INTO accounts (id, balance) VALUES (:1, :2)', params: [2, 2000]);

  await conn.commit();  // Commit transaction
} catch (e) {
  await conn.rollback();  // Rollback on error
  rethrow;
}

Autocommit Mode

await conn.setAutocommit(true);  // Each statement auto-commits

await conn.executeUpdate('INSERT INTO logs (msg) VALUES (:1)', params: ['Event 1']);
// Automatically committed

await conn.setAutocommit(false);  // Back to manual commit

Transaction Isolation Levels

await conn.setTransactionIsolation(TransactionIsolation.serializable);
// Options: readCommitted, serializable, readOnly

Stored Procedures

Basic Call with OUT Parameters

// Create OUT parameters
final nameOut = await conn.outString(maxSize: 100);
final salaryOut = await conn.outNumber();

await conn.callproc(
  'get_employee_info',
  parameters: [101],  // IN parameter: employee_id
  keywordParameters: {
    'emp_name': nameOut,
    'emp_salary': salaryOut,
  },
);

print('Name: ${nameOut.getValue()}');
print('Salary: ${salaryOut.getValue()}');

// Cleanup
nameOut.dispose();
salaryOut.dispose();

REF CURSOR (Returning Result Sets)

final cursorOut = await conn.outRefCursor();

await conn.callproc(
  'get_employees_by_dept',
  parameters: [10],  // department_id
  keywordParameters: {'emp_cursor': cursorOut},
);

// Get cursor as statement
final cursor = cursorOut.getValue() as OracleStatement;

// Iterate results
await for (final row in cursor.rows()) {
  print('${row['FIRST_NAME']} ${row['LAST_NAME']}');
}

await cursor.close();
cursorOut.dispose();

Batch Operations

Insert thousands of rows efficiently with executemany():

final data = [
  ['John', 'john@example.com', 30],
  ['Jane', 'jane@example.com', 25],
  ['Bob', 'bob@example.com', 35],
  // ... thousands more rows
];

final stmt = await conn.prepare(
  'INSERT INTO users (name, email, age) VALUES (:1, :2, :3)',
);

await stmt.executemany(data);
await conn.commit();
await stmt.close();

LOB Handling

CLOB (Character Large Object)

// Create and write
final clob = await conn.createLob(LobType.clob);
await clob.write('Large text content...');

// Insert into table
await conn.executeUpdate(
  'INSERT INTO documents (id, content) VALUES (:1, :2)',
  params: [1, clob],
);

// Read from table
final results = await conn.query('SELECT content FROM documents WHERE id = :1', params: [1]);
final lobValue = results.first['CONTENT'] as OracleLob;
final text = await lobValue.read();
print('Content: $text');

await clob.close();
clob.dispose();

Streaming Large Result Sets

For memory-efficient processing of large results:

final stmt = await conn.execute('SELECT * FROM large_table');

// Set prefetch for better performance
await stmt.setPrefetchRows(1000);

// Stream rows instead of loading all into memory
int count = 0;
await for (final row in stmt.rows()) {
  count++;
  // Process row...
}
print('Processed $count rows');

await stmt.close();

Error Handling

try {
  await conn.query('SELECT * FROM nonexistent_table');
} on OracleStatementException catch (e) {
  print('SQL Error: ${e.message}');
  print('Error Code: ${e.errorCode}');  // e.g., 942 for table not found
  print('SQL: ${e.sql}');
} on OracleConnectionException catch (e) {
  print('Connection Error: ${e.message}');
} on OracleException catch (e) {
  print('Oracle Error: ${e.message}');
}

Examples

The example/ directory contains 13 complete, runnable examples:

Example	Description
01_basic_connection.dart	Connect and query
02_simple_queries.dart	SELECT statements
03_parameterized_queries.dart	Bind parameters
04_batch_operations.dart	Bulk inserts with executemany
05_stored_procedures.dart	Calling PL/SQL
06_lob_handling.dart	CLOB/BLOB operations
07_connection_pooling.dart	Pool management
08_transactions.dart	Commit/rollback
09_streaming_large_results.dart	Memory-efficient streaming
10_utf8_international.dart	Unicode support
11_named_parameters.dart	Named bind variables
12_ref_cursor.dart	REF CURSOR from procedures
13_autocommit_and_isolation.dart	Transaction modes

Run any example:

export ORACLE_USER="system"
export ORACLE_PASSWORD="MyPassword123"
export ORACLE_CONNECTION_STRING="localhost:1521/XEPDB1"
export DYLD_LIBRARY_PATH="/path/to/instantclient:$DYLD_LIBRARY_PATH"

dart run example/01_basic_connection.dart

Testing with Docker

Quick Oracle database for development:

# Start Oracle XE
docker run -d --name oracle-xe -p 1521:1521 \
  -e ORACLE_PWD=MyPassword123 \
  gvenzl/oracle-xe:21-slim

# Wait for startup (takes 1-2 minutes)
docker logs -f oracle-xe

# Set connection credentials
export ORACLE_USER="system"
export ORACLE_PASSWORD="MyPassword123"
export ORACLE_CONNECTION_STRING="localhost:1521/XEPDB1"

# Ensure native libraries are accessible (see Requirements section)
# macOS:
export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient:$DYLD_LIBRARY_PATH"
# Linux:
export LD_LIBRARY_PATH="$HOME/oracle/instantclient_23_5:$LD_LIBRARY_PATH"

# Run an example
dart run example/01_basic_connection.dart

Library Configuration

ODPI-C Library Path

ODPI-C can be specified in three ways (checked in order):

// 1. Direct parameter (highest priority)
await db.initOracleClient(libraryPath: '/path/to/libodpic.dylib');

// 2. Environment variable
// export ORACLE_CLIENT_LIB_PATH="/path/to/libodpic.dylib"

// 3. Auto-search (checks /usr/local/lib, /usr/lib, current directory, etc.)
await db.initOracleClient();

Oracle Instant Client Path

Oracle Instant Client must be in the system library path:

# macOS
export DYLD_LIBRARY_PATH="/path/to/instantclient:$DYLD_LIBRARY_PATH"

# Linux
export LD_LIBRARY_PATH="/path/to/instantclient:$LD_LIBRARY_PATH"

# Windows - add to PATH environment variable

Note: Oracle Instant Client path cannot be set in Dart code. ODPI-C uses OS-level dynamic loading (dlopen), which reads from system library paths. This is standard for all Oracle database drivers.

Troubleshooting

"Cannot locate Oracle Client library"

Ensure both libraries are accessible:

# Check ODPI-C
ls -la /usr/local/lib/libodpic*

# Check Oracle Instant Client (macOS)
ls -la $DYLD_LIBRARY_PATH/libclntsh*

# Check Oracle Instant Client (Linux)
ls -la $LD_LIBRARY_PATH/libclntsh*

"ORA-12154: TNS could not resolve"

Check connection string format:

// Correct
connectionString: 'localhost:1521/XEPDB1'

// Wrong
connectionString: 'localhost/XEPDB1'  // Missing port

"ORA-01017: invalid username/password"

Verify credentials:

print('User: ${config.username}');
print('Connection: ${config.connectionString}');

API Reference

Core Classes

Class	Description
OracleDb	Main entry point, manages pools and client
OracleConnection	Database connection with query methods
OracleConnectionPool	Connection pool with statistics
OracleStatement	Prepared statement for queries
OracleVariable	Bind variable for OUT parameters
OracleLob	LOB (CLOB/BLOB) handling
OracleConfig	Connection configuration
PoolConfig	Pool configuration

Key Methods

OracleConnection:

• query(sql, {params, namedParams}) - Execute and return all rows
• execute(sql, {params, namedParams}) - Execute and return statement for streaming
• executeUpdate(sql, {params, namedParams}) - Execute DML, return rows affected
• prepare(sql) - Prepare statement for batch execution
• callproc(name, {parameters, keywordParameters}) - Call stored procedure
• commit() / rollback() - Transaction control
• ping() - Check connection health
• close() - Close connection

OracleStatement:

• rows() - Stream result rows
• fetchOne() - Fetch single row
• fetchAll() - Fetch all rows
• executemany(data) - Batch execute
• close() - Close statement

License

MIT License - See LICENSE file for details.

Contributing

Contributions welcome! Please read the contributing guidelines and submit pull requests.