example/README.md

Oraffi Examples #

This directory contains comprehensive, high-quality examples demonstrating all major features of the Oraffi Oracle database client for Dart.

📋 Table of Contents #

• Prerequisites
• Setup
• Running Examples
• Example List
• Docker Setup
• Troubleshooting

Prerequisites #

Before running the examples, ensure you have:

1. Dart SDK (3.0 or higher)

   dart --version
   

2. Oracle Instant Client installed and accessible

   • Download from: https://www.oracle.com/database/technologies/instant-client/downloads.html
   • Or install via package manager (Homebrew on macOS, apt on Linux)

3. Oracle Database (11g or higher)

   • Local installation, remote server, or Docker container
   • Oracle Express Edition (XE) works great for testing

4. Oraffi package installed

   dart pub get
   

Setup #

1. Install Oracle Instant Client #

macOS (Homebrew):

brew tap InstantClientTap/instantclient
brew install instantclient-basic

Linux (Ubuntu/Debian):

# Download from Oracle website, then:
sudo mkdir -p /opt/oracle
sudo unzip instantclient-basic-linux.x64-*.zip -d /opt/oracle/
echo /opt/oracle/instantclient_* | sudo tee /etc/ld.so.conf.d/oracle-instantclient.conf
sudo ldconfig

Windows:

1. Download Instant Client from Oracle
2. Extract to C:\oracle\instantclient_21_x
3. Add to PATH environment variable

2. Set Environment Variables #

Create a .env file in the project root or export variables:

export ORACLE_USER="your_username"
export ORACLE_PASSWORD="your_password"
export ORACLE_CONNECTION_STRING="localhost:1521/XEPDB1"
export ORACLE_CLIENT_LIB_PATH="/path/to/instantclient"  # Optional

Alternative environment variable names (for compatibility):

# These also work:
export DB_USER="your_username"
export DB_PASSWORD="your_password"
export DB_CONNECTION_STRING="localhost:1521/XEPDB1"

3. Test Connection #

Run the basic connection example to verify setup:

dart run example/01_basic_connection.dart

Running Examples #

Run a Single Example #

dart run example/01_basic_connection.dart

Run All Examples (Sequential) #

for file in example/*.dart; do
  echo "Running $file..."
  dart run "$file"
  echo "---"
done

Run with Custom Configuration #

ORACLE_USER=myuser \
ORACLE_PASSWORD=mypass \
ORACLE_CONNECTION_STRING=dbserver:1521/PROD \
dart run example/02_simple_queries.dart

Example List #

1. Basic Connection #

File: 01_basic_connection.dart

Learn the fundamentals of connecting to Oracle:

• Initialize Oracle client
• Create connection from environment variables
• Verify connection with ping()
• Get server version and metadata
• Properly close connections

Topics: Connection lifecycle, configuration, cleanup

2. Simple Queries #

File: 02_simple_queries.dart

Execute basic SQL queries:

• SELECT queries with various clauses
• Fetch all rows at once
• Access different column types
• Handle NULL values
• Use aggregate functions (COUNT, AVG, MAX, etc.)

Topics: SELECT, WHERE, ORDER BY, aggregates, NULL handling

3. Parameterized Queries #

File: 03_parameterized_queries.dart

Use bind parameters for security and efficiency:

• Prevent SQL injection attacks
• Bind different data types (string, int, double, DateTime)
• Use positional parameters (?)
• Reuse prepared statements
• Demonstrate SQL injection prevention

Topics: Bind parameters, SQL injection prevention, prepared statements

4. Batch Operations #

File: 04_batch_operations.dart

Efficient bulk operations with executemany():

• Insert 1,000+ rows in single call (10-100x faster)
• Use Oracle array DML
• Get individual row counts
• Handle mixed data types
• Batch UPDATE and DELETE operations

Topics: executemany(), array DML, bulk operations, performance

5. Stored Procedures #

File: 05_stored_procedures.dart

Call stored procedures and functions:

• Procedures with IN parameters
• Handle OUT parameters
• Handle IN/OUT parameters
• Call functions and get return values
• Execute anonymous PL/SQL blocks

Topics: callproc(), createVar(), OUT parameters, PL/SQL

6. LOB Handling #

File: 06_lob_handling.dart

Work with Large Objects (CLOB and BLOB):

• Write and read CLOBs (text data up to 4GB)
• Write and read BLOBs (binary data)
• Use LOB buffering for 10-50x performance
• Use setFromBytes() for one-shot writes
• Update existing LOBs
• Query LOB metadata

Topics: CLOB, BLOB, LOB buffering, large data

7. Connection Pooling #

File: 07_connection_pooling.dart

Manage connection pools for scalability:

• Create and configure pools
• Acquire connections from pool
• Release connections back to pool
• Monitor pool statistics
• Use predefined configurations (high-traffic, low-traffic)
• Handle concurrent connections

Topics: Connection pools, poolConfig, pool statistics, scalability

8. Transactions #

File: 08_transactions.dart

Manage database transactions:

• Commit and rollback
• Handle transaction boundaries
• Implement error recovery
• Use SELECT FOR UPDATE (pessimistic locking)
• Batch processing with periodic commits
• ACID properties

Topics: commit(), rollback(), transactions, locks, ACID

9. Streaming Large Results #

File: 09_streaming_large_results.dart

Efficiently handle large result sets:

• Stream 10,000+ rows with constant memory
• Use rows() for lazy evaluation
• Configure prefetch and fetch array size
• Handle millions of rows
• Process in chunks
• Compare streaming vs loading all rows

Topics: rows(), streaming, prefetch, memory efficiency, large datasets

10. UTF-8 & International Characters #

File: 10_utf8_international.dart

Work with international text:

• Handle UTF-8 correctly
• Store and retrieve Chinese, Arabic, Cyrillic, etc.
• Work with emojis and special Unicode
• Understand character length vs byte length
• Search international text
• Batch operations with multilingual data

Topics: UTF-8, Unicode, international characters, emojis, NLS

11. Named Parameters #

File: 11_named_parameters.dart

Use named parameters (:name syntax) for better SQL readability:

• INSERT with named parameters
• SELECT with named parameters
• UPDATE and DELETE with named parameters
• Complex queries with multiple named parameters
• Benefits over positional parameters
• Best practices for parameter naming

Topics: Named parameters, :name syntax, SQL readability, maintainability

12. REF CURSOR - Returning Result Sets #

File: 12_ref_cursor.dart

Work with REF CURSOR to return result sets from stored procedures:

• Procedures with REF CURSOR OUT parameters
• Functions returning REF CURSOR
• Multiple REF CURSORs from single procedure
• Dynamic SQL with REF CURSOR
• Streaming large result sets from cursors
• Best practices for cursor management

Topics: REF CURSOR, SYS_REFCURSOR, stored procedures, result sets

13. Autocommit Mode & Transaction Isolation Levels #

File: 13_autocommit_and_isolation.dart

Master transaction control with autocommit mode and isolation levels:

• Enable/disable autocommit mode
• Autocommit performance impact (batch vs single commit)
• READ COMMITTED isolation level (default)
• SERIALIZABLE isolation level (strictest)
• READ ONLY transactions
• Combining autocommit with isolation levels
• Error handling best practices

Topics: Autocommit, transaction isolation, READ COMMITTED, SERIALIZABLE, READ ONLY

Docker Setup #

Quick Start with Oracle XE in Docker #

Run Oracle Express Edition in a Docker container:

# Pull and run Oracle XE (this may take a few minutes first time)
docker run -d \
  --name oracle-xe \
  -p 1521:1521 \
  -e ORACLE_PWD=MyPassword123 \
  gvenzl/oracle-xe:21-slim

# Wait for Oracle to be ready (check logs)
docker logs -f oracle-xe

# When you see "DATABASE IS READY TO USE!", press Ctrl+C

Configure Environment for Docker #

export ORACLE_USER="system"
export ORACLE_PASSWORD="MyPassword123"
export ORACLE_CONNECTION_STRING="localhost:1521/XEPDB1"

Connect to Oracle in Container #

# SQL*Plus (if installed)
sqlplus system/MyPassword123@localhost:1521/XEPDB1

# Or use Docker exec
docker exec -it oracle-xe sqlplus system/MyPassword123@XEPDB1

Stop and Remove Container #

docker stop oracle-xe
docker rm oracle-xe

Troubleshooting #

Common Issues #

1. "Cannot find Oracle Client library"

Solution:

• Verify Oracle Instant Client is installed
• Set ORACLE_CLIENT_LIB_PATH environment variable
• On Linux: run sudo ldconfig after installation
• On macOS: ensure library is in /usr/local/lib or set DYLD_LIBRARY_PATH

# Example for macOS:
export ORACLE_CLIENT_LIB_PATH="/usr/local/lib"

# Example for Linux:
export ORACLE_CLIENT_LIB_PATH="/usr/lib/oracle/21/client64/lib"

2. "ORA-12541: TNS:no listener"

Solution:

• Verify Oracle database is running
• Check connection string format: host:port/service_name
• Verify port 1521 is accessible
• Check firewall rules

# Test connection with tnsping
tnsping localhost:1521/XEPDB1

# Or use telnet
telnet localhost 1521

3. "ORA-01017: invalid username/password"

Solution:

• Verify credentials are correct
• Check if user account is locked
• Ensure using correct service name (XE, XEPDB1, ORCL, etc.)

-- Unlock user (as SYSTEM)
ALTER USER your_user ACCOUNT UNLOCK;

4. "ORA-28040: No matching authentication protocol"

Solution:

• Update Oracle Instant Client to match server version
• Or configure server to allow older protocols

5. Examples fail with "Table doesn't exist"

Solution:

• Each example creates its own tables
• Ensure user has CREATE TABLE privilege
• Run examples sequentially (they clean up after themselves)

-- Grant required privileges
GRANT CREATE TABLE TO your_user;
GRANT CREATE PROCEDURE TO your_user;
GRANT UNLIMITED TABLESPACE TO your_user;

Getting Help #

If you encounter issues:

1. Check Oracle Database Logs:

   # Docker
   docker logs oracle-xe
   
   # Local installation
   tail -f $ORACLE_HOME/diag/rdbms/*/trace/alert_*.log
   

2. Enable Oraffi Debug Logging:

   // Add to your code
   MemoryManager.setLogger(
     warning: (msg) => print('[WARNING] $msg'),
     error: (msg) => print('[ERROR] $msg'),
   );
   

3. Test with SQL*Plus:

   sqlplus username/password@connection_string
   

4. Check Environment Variables:

   echo $ORACLE_USER
   echo $ORACLE_PASSWORD
   echo $ORACLE_CONNECTION_STRING
   

Best Practices #

When using these examples as templates for your own code:

1. Always use bind parameters (?) for user input - never string concatenation
2. Close connections when done - use try/finally blocks
3. Use connection pools for web applications and high-traffic scenarios
4. Use streaming (rows()) for large result sets
5. Commit transactions explicitly - don't rely on autocommit
6. Handle errors with try-catch and rollback on failure
7. Use LOB buffering when writing large objects
8. Dispose variables after use (OUT parameters)
9. Set reasonable timeouts for long-running queries
10. Monitor pool statistics to tune configuration

Performance Tips #

• Batch operations: Use executemany() for bulk inserts (10-100x faster)
• Statement caching: Enable in pool config for frequently-used queries
• Prefetch rows: Set setPrefetchRows(1000) for large result sets
• LOB buffering: Use openResource()/closeResource() for multiple LOB writes
• Connection pooling: Reuse connections instead of creating new ones
• Array fetch size: Adjust setFetchArraySize() based on row size

Next Steps #

After running these examples:

1. Check the Integration Tests
2. Explore the Source Code
3. Read the Main README for API reference
4. Build your own application!

Contributing #

Found an issue or want to add an example?

1. Fork the repository
2. Create your feature branch
3. Add tests for new functionality
4. Submit a pull request

License #

These examples are part of the Oraffi project and share the same license.

For questions or issues, please open an issue in the repository.