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 #

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:

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 #

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.