Oracle 10g Legacy Bridge — Case Study

Legacy Systems Engineering

Bridging Oracle 10g
with the
Modern World

How a FastAPI-to-SQL*Plus gateway solved what modern drivers couldn't — preserving Arabic legacy encoding while making a 20-year-old database fully programmable.

Oracle 10g

FastAPI

SQL*Plus

AR8MSWIN1256

Python

Legacy Engineering

Marwan Mohamed

Head of Software Department
@ LIMU

oracle_bridge.py

# The core bridge
result = subprocess.run(
  ["sqlplus", "-S",
   self.connection_string],
  input=script.encode(
    self.sqlplus_encoding),
  capture_output=True,
  timeout=30,
)

# Arabic CHR() encoding
encoded = value.encode("cp1256")
return " || ".join(
  [f"CHR({byte})"
   for byte in encoded]
)

10g

Oracle Version Targeted

Driver Compatibility Issues

Languages Attempted First

Working Architecture

The Problem

Not broken. Just hard to reach.

An Oracle 10g database still powered real workflows, held financial transactions, employee records, and student data. The database wasn't the problem — getting modern apps to talk to it reliably was.

Legacy systems are rarely abandoned because they fail. They endure because they matter — embedded in processes, holding years of critical data, powering workflows that would take enormous effort to migrate.

The Oracle 10g database contained financial transactions, employee records, student-related data, and years of operational history. It didn't need replacing. It needed a bridge.

The instinct was to use modern drivers — Node.js, Python, PHP, different Oracle clients, downgraded runtimes. Each combination failed in a slightly different way.

The real breakthrough: instead of making modern tools speak Oracle, use Oracle's own native tool as the communication layer.

Attempts That Failed

Oracle client too newNode.js with modern Oracle drivers — version mismatch with 10g server

Architecture mismatchPython cx_Oracle — 32/64-bit conflicts and missing Instant Client libs

Encoding failure on ArabicPHP OCI8 connected fine locally, broke silently on Arabic text inserts

Works local, fails on serverFragile environment-specific setups that couldn't survive deployment

Connected, then broke on encodingArabic data came back as mojibake (Øª...) — silent corruption

System Design

The Bridge Architecture

Modern apps talk HTTP. The gateway receives, validates, translates, and returns clean JSON — while SQL*Plus handles Oracle communication natively.

🌐

Modern Apps

HTTP / JSON

Any Client

REST API

⚡

FastAPI Gateway

Auth · Validate · Encode

The Bridge

subprocess

🛠

SQL*Plus

Oracle Native CLI

Native Layer

TNS / Net8

🗄

Oracle 10g

AR8MSWIN1256

Legacy Database

How It Works

Speaking the language Oracle understands

SQL*Plus is native to Oracle. It understands Oracle. For a legacy Oracle 10g system, that matters more than elegance. The gateway wraps it in a modern API.

🔌

SQL*Plus as the Database Bridge

subprocess.run() replaces fragile drivers

Instead of wrestling with driver compatibility, the API executes SQL through Oracle's own native command-line tool. SQL*Plus handles authentication, session management, and result formatting — natively.

The FastAPI service builds the SQL*Plus input script, pipes it through subprocess, captures the output, and parses it into clean JSON. No Oracle client libraries needed at the Python level.

If SQL*Plus can connect, the API can connect — and SQL*Plus always knows how to talk to Oracle 10g.

🔒

Controlled Business Operations

Specific endpoints, not raw SQL exposure

The gateway exposes specific, validated business operations — not a raw SQL interface. Each endpoint represents a meaningful action: inserting a financial transaction, reading employee records, checking table structure.

Operations are scoped to business intent, with type validation, encoding rules, and audit logging built in at each endpoint.

Read operations, inserts, deletes, updates, and workflow automations can all be added as structured endpoints without ever exposing the underlying SQL engine.

Implementation

The Code Behind the Bridge

Three critical pieces work together: subprocess execution, NLS environment alignment, and the Arabic CHR() encoding that preserves data integrity across character sets.

# FastAPI receives request → builds SQL*Plus script → executes
def execute_sqlplus(self, sqlplus_script: str, timeout: int = 30):
    env = os.environ.copy()
    env["NLS_LANG"] = self.nls_lang  # "ARABIC_AMERICA.AR8MSWIN1256"

    result = subprocess.run(
        ["sqlplus", "-S", self.connection_string],
        input=sqlplus_script.encode(self.sqlplus_encoding),
        capture_output=True,
        timeout=timeout,
        env=env,
    )

    if result.returncode != 0:
        raise DatabaseError(result.stderr.decode("utf-8", errors="replace"))

    return self.parse_sqlplus_output(
        result.stdout.decode(self.sqlplus_encoding, errors="replace")
    )

Arabic Text Handling

Preserving decades of Arabic data

The database uses AR8MSWIN1256 — Oracle's legacy Arabic character encoding, mapped to Windows cp1256. Modern applications work in Unicode. Bridging these without corrupting data required careful design.

Simply sending Unicode wasn't an option. The existing data was stored in cp1256. A Unicode insert would produce rows that look correct in some tools and broken in others — silent corruption.

The solution: align the SQL*Plus session with the database's NLS character set, then encode every Arabic string as CHR() byte expressions before sending. The database never sees Unicode — it sees its own native encoding.

If input arrives as mojibake (Øª...), the API detects and repairs it first, then re-encodes through the correct cp1256 path.

Oracle Charset → Python Codec Map

AR8MSWIN1256 cp1256 ← Arabic

WE8MSWIN1252 cp1252 ← Western

AL32UTF8 utf-8 ← Modern

UTF8 utf-8 ← Unicode

Arabic CHR() Encoding Example

مرحبا

encoded = value.encode("cp1256")
# → bytes: b'\xe3\xd1\xcd\xc8\xc7'

CHR(227)||CHR(209)||CHR(205)
||CHR(200)||CHR(199)

Security Architecture

A gate, not an open door

Once an API can insert, delete, or modify records in a legacy Oracle system, it is not a convenience layer — it is a gate into sensitive production data.

API Key Authentication

Every request passes through router-level dependency injection. No valid API key means no access — not to read operations, not to inserts, not to anything.

IP Allowlist Enforcement

Access is further restricted by allowed IP addresses. Even with a valid API key, requests from unknown IPs return 403. Two-layer authorization at every entry point.

Scoped Business Operations

Endpoints represent specific, validated actions — not raw SQL. Delete and update operations carry stricter validation, audit logging, and explicit business rule enforcement.

Dangerous

"Here is an endpoint where you can run SQL."

Exposes the entire database surface. No validation, no audit trail, no scope control.

Controlled

"Here is an endpoint that inserts a validated financial transaction into TMP_FTRAN using the database's legacy encoding rules."

Specific intent, validated inputs, encoding rules enforced, auditable.

Key Takeaways

What this project taught

LESSON 01

Solve the right problem, not the obvious one

The failure wasn't incompetence — it was framing. Trying to make Oracle 10g behave like a modern integration was the wrong goal. Speaking to it in its native language was the right one.

LESSON 02

Native tools outperform compatibility layers

SQL*Plus is not exciting. But it's what Oracle ships, what Oracle understands, and what Oracle 10g has always worked with. Native beats elegant when reliability is the constraint.

LESSON 03

Correctness means matching production behavior

Using Unicode felt modern. Matching AR8MSWIN1256 was correct. The goal wasn't to adopt a new standard — it was to preserve the exact storage behavior that had kept production data consistent for decades.

LESSON 04

Legacy is not dead — it's important enough to bridge

A legacy system that still matters is a system worth engineering around carefully. The best decision isn't always replacement. Sometimes it's a careful, respectful bridge between what was and what is.

Conclusion

Legacy does not mean dead. Sometimes it means important enough that you cannot afford to treat it casually.

The Oracle 10g gateway isn't just a connectivity solution. It's a compatibility layer between two worlds — one that speaks HTTP and JSON, one that speaks SQL*Plus and AR8MSWIN1256.

By respecting the database's existing storage model, enforcing security at every layer, and exposing controlled business operations rather than raw SQL, the gateway transformed an isolated legacy system into a programmable backend any modern application can safely consume.

Modern apps speak HTTP and JSON

↕

FastAPI Gateway — the translation layer

↕

Oracle 10g speaks SQL*Plus + cp1256

The value is in the middle

Stack at a Glance

API Framework

FastAPI

DB Bridge

SQL*Plus

DB Version

Oracle 10g

Encoding

AR8MSWIN1256 / cp1256

Language

Python 3

Auth

API Key + IP Allowlist

Bridging Oracle 10g with the Modern World

Not broken. Just hard to reach.

The Bridge Architecture

Speaking the language Oracle understands

The Code Behind the Bridge

Preserving decades of Arabic data

A gate, not an open door

What this project taught

Bridging Oracle 10g
with the
Modern World