From Apache mod_plsql to ORDS: Architectural Shifts, CGI Environments & Character Set Gotchas

Executive Summary: Under the Hood of the PL/SQL Gateway

When migrating custom enterprise web applications from Oracle HTTP Server (OHS) mod_plsql to Oracle REST Data Services (ORDS), database teams often expect identical runtime behaviors. After all, the application code—written with HTP, HTF, and OWA_UTIL—remains entirely within Oracle Database stored procedures.

However, the middleware engine executing the HTTP-to-SQL translation is completely transformed:

1. mod_plsql was a compiled C module running inside Apache’s Prefork or Worker Multi-Processing Modules (MPM). It communicated with Oracle using direct Oracle Call Interface (OCI) C libraries. Connections were either dedicated per Apache child process or explicitly terminated at request completion.
2. ORDS is a Java Enterprise application built on top of the Eclipse Jetty / Tomcat servlet container and Oracle’s Universal Connection Pool (UCP). It communicates with Oracle via the JDBC Thin driver, maintaining long-lived, multiplexed, pooled database sessions.

This architectural shift introduces profound operational nuances in four key areas:

• Session Lifecycle & Memory State: Handling package variables, global temporary tables, and open cursors across pooled connections.
• CGI Environment Variables: Forwarding client IPs, headers, and reverse proxy metadata through OWA_UTIL.GET_CGI_ENV.
• Character Encoding & Globalization: Transitioning from environment-variable-driven NLS_LANG conversions to JVM-managed UTF-8 streaming.
• Error Handling & Exception Semantics: How gateway timeouts and unhandled PL/SQL exceptions propagate to end-user browsers.

---

1. Architectural Model Comparison: OCI vs. JDBC Thin

The fundamental distinction between mod_plsql and ORDS lies in how web requests bind to physical database processes.

Detailed Component Comparison

Dimension	Apache mod_plsql	Oracle REST Data Services (ORDS)
Runtime Language	Compiled C (mod_plsql.so)	Java (JDK 17 or JDK 21 bytecode)
Database Driver	Native OCI (Oracle Call Interface)	Java Database Connectivity (JDBC Thin)
Connection Pooling	Apache Child Process level (Stateful OCI)	Universal Connection Pool (UCP) in JVM
Database Session Binding	1:1 with Apache child or re-authenticated	Dynamically borrowed from UCP pool
Default Character Conversion	Client-side NLS_LANG OS environment variable	Java internal UTF-16 to DB charset via JDBC
Web Gateway Entrypoint	/pls/<dad_name>/procedure	/ords/<pool_name>/procedure
Concurrency Ceiling	Constrained by Apache MPM process/thread limits	High-concurrency non-blocking async servlets

---

2. Session Lifecycle & Package State Pollution (The ORA-04068 Pitfall)

In mod_plsql, each Apache worker process typically held its own database connection. When an Apache process recycled or died, its database session terminated, automatically flushing all package body package state, package specifications, and session-level Global Temporary Tables (GTTs).

Under ORDS, database sessions within the UCP pool remain open for hours or days.

The Problem: Package Variable Leakage

Consider a package containing session-level state:

CREATE OR REPLACE PACKAGE BODY security_context_pkg IS
    g_current_user_id   NUMBER := NULL;
    g_organization_id   NUMBER := NULL;

    PROCEDURE set_context(p_user_id IN NUMBER, p_org_id IN NUMBER) IS
    BEGIN
        g_current_user_id := p_user_id;
        g_organization_id := p_org_id;
    END set_context;

    FUNCTION get_user_id RETURN NUMBER IS
    BEGIN
        RETURN g_current_user_id;
    END get_user_id;
END security_context_pkg;
/

In mod_plsql, if a request forgot to reset g_current_user_id, the next request handled by a different Apache child process started with uninitialized variables (NULL).

In ORDS, Request A (User 101) borrows Connection #4 from the pool and sets g_current_user_id = 101. When the request finishes, Connection #4 returns to the pool with g_current_user_id = 101 still present in memory. When Request B (User 202) arrives and borrows Connection #4, if it fails to call set_context, it inadvertently executes queries using User 101’s context.

Package State Invalidation: ORA-04068 and ORA-04061

Furthermore, if a DBA compiles or replaces a package while ORDS has open pooled connections, subsequent calls fail with:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "PORTAL_USER.MY_PKG" has been invalidated

The Solution: Enforcing DBMS_SESSION.RESET_PACKAGE

ORDS provides automated connection lifecycle hooks to reset session state upon connection checkout or return.

In ORDS pool configuration (/etc/ords/config/databases/<pool_name>/pool.xml), verify that session state cleanup is enabled:

<!-- Ensure session state is reset when borrowed or returned -->
<entry key="procedure.rest.preHook">PORTAL_USER.ords_session_hook.pre_request</entry>
<entry key="procedure.rest.postHook">PORTAL_USER.ords_session_hook.post_request</entry>

Within the database, implement the session cleanup procedure:

CREATE OR REPLACE PACKAGE PORTAL_USER.ords_session_hook IS
    PROCEDURE pre_request;
    PROCEDURE post_request;
END ords_session_hook;
/

CREATE OR REPLACE PACKAGE BODY PORTAL_USER.ords_session_hook IS
    PROCEDURE pre_request IS
    BEGIN
        -- Clear application context and set session defaults
        NULL;
    END pre_request;

    PROCEDURE post_request IS
    BEGIN
        -- Evict all package state to prevent cross-request leakage
        DBMS_SESSION.RESET_PACKAGE;
    END post_request;
END ords_session_hook;
/

Alternatively, configure global temporary tables with ON COMMIT DELETE ROWS rather than relying on session-duration rows, preventing data leakage across pooled invocations.

---

3. CGI Environment Variables & Reverse Proxy Metadata

Legacy mod_plsql applications heavily queried CGI environment variables via OWA_UTIL.GET_CGI_ENV:

v_client_ip  := OWA_UTIL.GET_CGI_ENV('REMOTE_ADDR');
v_user_agent := OWA_UTIL.GET_CGI_ENV('HTTP_USER_AGENT');
v_server_name:= OWA_UTIL.GET_CGI_ENV('SERVER_NAME');

The Problem: False Client IPs Behind Proxies

In a legacy OHS setup, Apache was directly exposed to clients or had mod_remoteip compiled in.

When moving to ORDS placed behind a reverse proxy (such as Caddy, Nginx, or an F5 Big-IP load balancer), REMOTE_ADDR received by ORDS is the IP address of the reverse proxy (e.g., 127.0.0.1 or 10.0.1.5), NOT the actual client browser IP.

If your PL/SQL code performs IP address whitelisting, session verification, or audit logging via OWA_UTIL.GET_CGI_ENV('REMOTE_ADDR'), it will log the reverse proxy’s internal IP address, breaking audit compliance.

The Solution: Forwarding and Extracting X-Forwarded-For

1. Configure Reverse Proxy Headers: In Nginx or Caddy, ensure headers are passed explicitly:

   proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
   proxy_set_header X-Real-IP $remote_addr;
   proxy_set_header X-Forwarded-Proto $scheme;
   proxy_set_header X-Forwarded-Host $host;

2. Configure ORDS to Trust Headers: In ORDS /etc/ords/config/global/settings.xml, enable header recognition:

   <entry key="misc.enableRemoteAddressHeader">true</entry>

3. Update PL/SQL Client IP Resolution Helper: Create an enterprise helper function to extract the true client IP reliably:

CREATE OR REPLACE FUNCTION PORTAL_USER.get_real_client_ip RETURN VARCHAR2 IS
    v_ip VARCHAR2(100);
BEGIN
    -- Check X-Forwarded-For first
    v_ip := OWA_UTIL.GET_CGI_ENV('HTTP_X_FORWARDED_FOR');
    
    IF v_ip IS NOT NULL THEN
        -- If multiple comma-separated proxy IPs exist, pick the first (original client)
        IF INSTR(v_ip, ',') > 0 THEN
            v_ip := TRIM(SUBSTR(v_ip, 1, INSTR(v_ip, ',') - 1));
        END IF;
        RETURN v_ip;
    END IF;

    -- Check X-Real-IP
    v_ip := OWA_UTIL.GET_CGI_ENV('HTTP_X_REAL_IP');
    IF v_ip IS NOT NULL THEN
        RETURN TRIM(v_ip);
    END IF;

    -- Fallback to standard REMOTE_ADDR
    RETURN OWA_UTIL.GET_CGI_ENV('REMOTE_ADDR');
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'UNKNOWN';
END get_real_client_ip;
/

---

4. Character Set Handling: NLS_LANG vs. JVM UTF-8 Encoding

Character encoding is one of the most common causes of silent data corruption during mod_plsql migrations.

How mod_plsql Handled Character Sets

In mod_plsql, character conversion was controlled by the operating system environment variable NLS_LANG or the PlsqlNLSLanguage parameter in dads.conf:

PlsqlNLSLanguage AMERICAN_AMERICA.WE8MSWIN1252

When an HTTP client posted form data, mod_plsql passed bytes directly to OCI. OCI inspected the client NLS_LANG and converted characters into the database character set (e.g., WE8ISO8859P1 or AL32UTF8). If misconfigured, “pass-through” storage occurred where Windows-1252 characters were stored in ISO-8859-1 columns without validation.

How ORDS Handles Character Sets

ORDS operates entirely in the Java Virtual Machine. Java’s internal string representation is UTF-16.

1. Incoming HTTP requests are parsed using the character set declared in the HTTP request’s Content-Type header (or defaulting to UTF-8).
2. Characters are converted into Java String objects.
3. The Oracle JDBC Thin driver converts Java UTF-16 strings directly into the target database’s character set (e.g., AL32UTF8).

The Gotcha: ORA-06502 (Numeric or Value Error) on Multi-Byte Characters

If your database character set is AL32UTF8 but your PL/SQL table columns or variables are defined using byte semantics instead of char semantics:

-- Byte semantics (Default in many older 11g/12c schemas)
v_name VARCHAR2(30 BYTE);

When users submit multi-byte UTF-8 characters (accents, umlauts, emojis, or international scripts), characters like é require 2 bytes, and characters like € require 3 bytes.

Under mod_plsql with WE8MSWIN1252, é occupied only 1 byte. In ORDS streaming UTF-8, submitting a 25-character string containing accents can consume 35 bytes, instantly triggering:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Mitigation Strategy

1. Verify Database NLS Semantics:

   SELECT parameter, value FROM nls_database_parameters 
   WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_LENGTH_SEMANTICS');

2. Enforce JVM UTF-8 Environment: Ensure your ORDS startup environment forces standard UTF-8 file encoding:

   JAVA_OPTS="-Dfile.encoding=UTF-8 -Dsun.jnu.encoding=UTF-8"

3. Migrate PL/SQL Declarations to Character Semantics: Audit and update table columns and stored procedure variable definitions:

   -- Recommended: Define variables with explicit CHAR semantics
   v_name VARCHAR2(30 CHAR);

---

5. Architectural Decision Matrix: mod_plsql vs. ORDS Migration

Evaluate your migration roadmap using this operational matrix:

Feature / Scenario	Legacy mod_plsql Pattern	ORDS Production Target	Action Required
Authentication	Direct schema password in dads.conf	ORDS_PUBLIC_USER with Proxy Authentication	Grant CONNECT THROUGH ORDS_PUBLIC_USER
Session State	Process termination clears memory	Persistent JDBC pooled sessions	Add DBMS_SESSION.RESET_PACKAGE hooks
Client IP Audit	Reads REMOTE_ADDR directly	Reads reverse proxy internal IP	Update code to parse X-Forwarded-For
String Sizing	Single-byte OS NLS_LANG	Multi-byte UTF-8 through JDBC	Expand buffers to VARCHAR2(N CHAR)
URL Paths	Bound to /pls/<dad>/	Bound to /ords/<pool>/	Deploy Nginx/Caddy URL rewrite rules
File Downloads	wpg_docload.download_file	Fully supported via servlet bridge	Ensure Content-Disposition headers set

---

6. Operational Troubleshooting & Error Triage

When diagnosing runtime anomalies in migrated applications, consult this diagnostic matrix:

Diagnostic 1: Resolving ORA-04068 / ORA-04061 Package Discard Errors

• Symptom: Users encounter ORA-04068: existing state of packages has been discarded after code deployments.
• Underlying Cause: Stored packages were recompiled while active database connections in the ORDS pool retained old memory states.
• Resolution:
  1. Restart the ORDS service or flush the database connection pool:

     systemctl restart ords

  2. Implement DBMS_SESSION.RESET_PACKAGE in the post-execution hook to ensure clean session state between calls.

Diagnostic 2: Resolving ORA-06502 String Buffer Overflow

• Symptom: Form submissions containing accents or special characters fail with ORA-06502: PL/SQL: numeric or value error: character string buffer too small.
• Underlying Cause: Byte semantic sizing (VARCHAR2(N BYTE)) truncated multi-byte UTF-8 strings passed by ORDS JDBC.
• Resolution:
  1. Inspect the failing procedure parameters.
  2. Modify variable and column definitions from BYTE to CHAR semantics:

     ALTER TABLE portal_user.contact_info MODIFY (last_name VARCHAR2(50 CHAR));

Diagnostic 3: Resolving HTTP 500 Gateway Timeout on Long-Running Procedures

• Symptom: Reports or batch jobs executed via web procedures terminate after 60 seconds with HTTP 500 or 504 Gateway Timeout.
• Underlying Cause: Reverse proxy or ORDS JDBC socket timeout expired before the PL/SQL engine completed execution.
• Resolution:
  1. In ORDS settings.xml, increase timeout limits:

     <entry key="jdbc.statementTimeout">300</entry>

  2. In Caddy / Nginx, align proxy read timeouts:

     proxy_read_timeout 300s;

---

📚 Official Documentation & Technical References

• Oracle Database PL/SQL Packages and Types Reference: DBMS_SESSION Package Specification — Details on package state invalidation, session cleanup, and RESET_PACKAGE.
• Oracle REST Data Services Documentation: Oracle REST Data Services Installation and Configuration Guide (Release 24.x) — Comprehensive coverage of Universal Connection Pool (UCP) mechanics and servlet lifecycle.
• Oracle Database Globalization Support Guide: Character Sets and Byte vs. Character Length Semantics — Best practices for managing UTF-8 and AL32UTF8 conversions in web applications.

---

Need architectural assistance troubleshooting ORDS connection pools, diagnosing session leakage, or completing a zero-downtime PL/SQL web migration? Consult with our Principal Oracle Architects or explore our Database Performance & Modernization Services.