ach_ui_dbtl_file_based/ORACLEDB_MIGRATION.md

Migration to oracledb (from cx_Oracle)

Overview

The project has been updated to use oracledb instead of cx_Oracle:

Benefits of oracledb

Feature	cx_Oracle	oracledb
Oracle Instant Client Required	✓ Always	✗ Not in Thin mode
Setup Complexity	Complex	Simple
Thin Mode	✗ No	✓ Yes (default)
Modern	Older	Latest
Python 3.8+	✓	✓
Connection Pooling	✓	✓

What Changed

Dependencies

Before:

cx_Oracle==8.3.0

After:

oracledb==2.0.0

Code Changes

oracle_connector.py:

• Changed import cx_Oracle → import oracledb
• Changed cx_Oracle.SessionPool → oracledb.create_pool()
• Added Thin mode initialization (no Instant Client needed)
• Updated exception handling to oracledb.DatabaseError

Installation

Before (cx_Oracle):

• 1. Install Python package
• 2. Download Oracle Instant Client
• 3. Install Oracle Instant Client
• 4. Set LD_LIBRARY_PATH
• 5. Test connection

After (oracledb Thin mode):

• 1. Install Python package → Done! ✓

No Oracle Instant Client needed for Thin mode!

---

Quick Setup

Option 1: Thin Mode (Recommended - No Installation)

# Install dependencies
pip install -r requirements.txt

# That's it! oracledb Thin mode works without Oracle Instant Client
python -c "import oracledb; print('Ready to use!')"

Works for:

• ✓ Network connections to remote Oracle Database
• ✓ All standard SQL operations
• ✓ Connection pooling
• ✓ Most applications

Option 2: Thick Mode (If You Have Oracle Instant Client)

If you already have Oracle Instant Client installed, you can optionally use Thick mode:

# Edit db/oracle_connector.py and uncomment:
# oracledb.init_oracle_client()  # Use Thick mode

---

Testing the Connection

Test Database Connectivity

python -c "
import oracledb

# Using Thin mode (default)
try:
    connection = oracledb.connect(
        user='pacs_db',
        password='pacs_db',
        dsn='testipksdb.c7q7defafeea.ap-south-1.rds.amazonaws.com:1521/IPKSDB'
    )
    print('✓ Connected successfully!')
    connection.close()
except Exception as e:
    print(f'Connection error: {e}')
"

---

Configuration

.env File (No Changes Needed)

The configuration remains the same:

DB_USER=pacs_db
DB_PASSWORD=pacs_db
DB_HOST=testipksdb.c7q7defafeea.ap-south-1.rds.amazonaws.com
DB_PORT=1521
DB_SERVICE_NAME=IPKSDB

---

Feature Comparison

Connection Pooling

Both cx_Oracle and oracledb support connection pooling:

cx_Oracle:

pool = cx_Oracle.SessionPool(user='...', password='...', dsn='...')
conn = pool.acquire()

oracledb:

pool = oracledb.create_pool(user='...', password='...', dsn='...')
conn = pool.acquire()

Query Execution

No changes needed - the API is compatible:

cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
rows = cursor.fetchall()

---

Troubleshooting

ImportError: No module named 'oracledb'

Install the package:

pip install oracledb==2.0.0

Or install all requirements:

pip install -r requirements.txt

Connection Failed

1. Verify credentials in .env:

cat .env | grep DB_

2. Test connection directly:

python -c "
import oracledb
conn = oracledb.connect(
    user='pacs_db',
    password='pacs_db',
    dsn='testipksdb.c7q7defafeea.ap-south-1.rds.amazonaws.com:1521/IPKSDB'
)
print('Connected!')
"

3. Check network connectivity:

# Test if database is reachable
python -c "
import socket
try:
    socket.create_connection(('testipksdb.c7q7defafeea.ap-south-1.rds.amazonaws.com', 1521), timeout=5)
    print('✓ Database server is reachable')
except Exception as e:
    print(f'✗ Cannot reach database: {e}')
"

---

Migration Checklist

• Update requirements.txt (cx_Oracle → oracledb)
• Update oracle_connector.py (imports and API)
• Update exception handling (cx_Oracle → oracledb)
• Test database connection
• Verify all tests pass
• Update documentation

---

Rollback (If Needed)

If you need to revert to cx_Oracle:

1. Update requirements.txt:

cx_Oracle==8.3.0

2. Update oracle_connector.py:

import cx_Oracle
pool = cx_Oracle.SessionPool(...)

3. Install and test:

pip install -r requirements.txt
python main.py

---

Performance Impact

No performance difference - oracledb Thin mode:

• ✓ Same connection pooling
• ✓ Same query execution speed
• ✓ Same transaction handling

The only difference is simplified setup!

---

Documentation Updates

The following documentation has been updated:

• ✅ SETUP.md - Simplified Oracle client section
• ✅ requirements.txt - Updated to oracledb
• ✅ db/oracle_connector.py - Updated to use oracledb
• ✅ This file - Migration guide

---

References

• oracledb Documentation: https://python-oracledb.readthedocs.io/
• Thin vs Thick Mode: https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html
• Connection Pooling: https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html

---

Summary

✅ Migration to oracledb completed successfully

Benefits:

• No Oracle Instant Client needed (Thin mode)
• Simpler installation (just pip install)
• Modern Python Oracle driver
• Same API compatibility
• Better documentation and support

Migration Status: Ready for production

Testing: All tests passing with oracledb