Migrating Legacy mod_plsql Applications to Oracle REST Data Services (ORDS): The Complete Production Runbook

Executive Summary: The Path Off mod_plsql

For decades, thousands of mission-critical enterprise portals, legacy student portals, internal inventory systems, and financial tools were built on the Oracle PL/SQL Web Toolkit (HTP, HTF, OWA_UTIL, and OWA_COOKIE). The entry point for these applications was mod_plsql, an Apache HTTP Server C module originally integrated into Oracle WebDB, Oracle 9iAS, and later Oracle HTTP Server (OHS) 10g, 11g, and 12c.

However, mod_plsql is deprecated and permanently removed from modern Oracle Fusion Middleware and OHS distributions. Maintaining legacy OHS servers exposes organizations to critical unpatched CVEs, outdated OpenSSL libraries, and unsupported 32-bit or early 64-bit C runtimes.

Oracle REST Data Services (ORDS) is the direct, supported successor for serving PL/SQL Web Toolkit applications. ORDS provides a high-throughput Java servlet engine that natively interprets PL/SQL gateway requests and translates them over connection-pooled JDBC connections to Oracle Database 19c or 23ai.

This production runbook details the end-to-end migration process: mapping dads.conf directives to modern ORDS database pool configuration settings, implementing reverse proxy rewrite rules to maintain zero client-side bookmark disruption, enabling legacy file uploads and downloads (wpg_docload), and hardening the database tier against common security pitfalls.

---

1. Migration Lifecycle & Architecture Overview

The shift from mod_plsql to ORDS moves the application tier from a multi-process Apache C architecture to an event-driven, multithreaded Java servlet container running standalone or on Apache Tomcat.

In mod_plsql, each incoming HTTP request parsed a Database Access Descriptor (DAD) defined in Apache configuration files. Apache dedicated server child processes connected directly to Oracle via OCI.

In ORDS, requests flow through an edge reverse proxy (such as Caddy or Nginx) to ORDS. ORDS evaluates the URI path prefix against its configured pools and routes the call through an Universal Connection Pool (UCP) over JDBC Thin:

[ Web Browser ]
       |
       v (HTTPS /pls/custom_app/pkg.procedure)
[ Reverse Proxy (Caddy / Nginx) ] (Preserves or rewrites URI path)
       |
       v (HTTP :8080/ords/custom_app/pkg.procedure)
[ Oracle REST Data Services (ORDS) ]
       | (JDBC Thin Connection Pool via ORDS_PUBLIC_USER)
       v (Proxy Authentication / ALTER SESSION)
[ Oracle Database 19c / 23ai ]
       | (PL/SQL Gateway: HTP Buffer Execution)
[ Custom Schema / PL/SQL Web Toolkit ]

---

2. Phase 1: Auditing and Deconstructing Legacy dads.conf

Before provisioning ORDS, you must inventory every parameter within your legacy Apache configuration ($ORACLE_INSTANCE/config/OHS/ohs1/moduleconf/dads.conf or mod_plsql/conf/dads.conf).

Typical Legacy dads.conf Stanza

<Location /pls/portal_dad>
    SetHandler pls_handler
    Order deny,allow
    Allow from all
    AllowCONNECT on
    PlsqlDatabaseUsername         PORTAL_USER
    PlsqlDatabasePassword         EncryptedPortalPass123
    PlsqlDatabaseConnectString    dbhost.internal.net:1521/PRODPDB.corp
    PlsqlAuthenticationMode       Basic
    PlsqlDefaultPage              portal_home.main
    PlsqlDocumentTablename        app_documents_table
    PlsqlDocumentPath             docs
    PlsqlDocumentProcedure        portal_doc_download.show
    PlsqlUploadAsLongRaw          wpg_docload
    PlsqlNLSLanguage              AMERICAN_AMERICA.AL32UTF8
    PlsqlPathAlias                home
    PlsqlPathAliasProcedure       portal_home.main
</Location>

Parameter Mapping Matrix: dads.conf to ORDS

legacy dads.conf Directive	ORDS Equivalent Property	Description & Function
PlsqlDatabaseConnectString	db.connectionType = "tns" or "basic"	TNS alias or EZConnect string (//dbhost:1521/service)
PlsqlDatabaseUsername	db.username = "ORDS_PUBLIC_USER"	ORDS uses proxy authentication instead of direct schema logins
PlsqlDatabasePassword	db.password	Proxy user secret managed securely via ords config secret
PlsqlDefaultPage	Reverse proxy redirect or rewrite rule	Default landing procedure when naked DAD URI is requested
PlsqlDocumentTablename	plsql.gateway.docTable = "app_documents_table"	Table used for binary file uploads via multipart forms
PlsqlDocumentPath	plsql.gateway.docPath = "docs"	Virtual path element triggering direct document fetch
PlsqlDocumentProcedure	plsql.gateway.docProcedure = "..."	Custom PL/SQL download dispatcher procedure
PlsqlUploadAsLongRaw	Handled natively via BLOB columns	File uploads automatically deserialize into BLOB columns
PlsqlNLSLanguage	JVM system property (-Dfile.encoding=UTF-8)	Character set conversions handled cleanly by Java runtime

---

3. Phase 2: Database Preparation and Security Hardening

In legacy mod_plsql, DADs frequently connected directly as the schema owner that held the application code and tables (PORTAL_USER). If SQL injection or procedure traversal vulnerabilities existed, the web client operated with full schema owner privileges.

With ORDS, architecture best practices mandate Proxy Authentication:

1. ORDS connects as a low-privileged gateway user: ORDS_PUBLIC_USER.
2. Upon receiving a request destined for the target schema, ORDS opens a proxy session (ALTER SESSION SET CURRENT_SCHEMA = ... or proxy connect ORDS_PUBLIC_USER[TARGET_SCHEMA]).

Step 1: Verify and Unlock ORDS_PUBLIC_USER

-- Connect as SYSDBA or PDB Admin to your target container
ALTER SESSION SET CONTAINER = PRODPDB;

-- Verify user status
SELECT username, account_status, lock_date 
FROM dba_users 
WHERE username IN ('ORDS_PUBLIC_USER', 'ORDS_METADATA');

-- If creating a dedicated gateway proxy user for legacy migration:
CREATE USER ORDS_PUBLIC_USER IDENTIFIED BY "Complex_Passwd_2026#!"
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  QUOTA 10M ON USERS
  ACCOUNT UNLOCK;

GRANT CREATE SESSION TO ORDS_PUBLIC_USER;

Step 2: Grant Proxy Privileges to Target Application Schemas

Allow ORDS_PUBLIC_USER to proxy into the legacy application schema without knowing the application schema’s master password:

-- Grant proxy connection privilege
ALTER USER PORTAL_USER GRANT CONNECT THROUGH ORDS_PUBLIC_USER;

-- Verify proxy grants
SELECT client, proxy, authentication 
FROM dba_proxies 
WHERE proxy = 'ORDS_PUBLIC_USER';

Step 3: Grant Access to PL/SQL Web Toolkit Packages

The target schema must have execution rights on the core OWA packages:

GRANT EXECUTE ON OWA TO PORTAL_USER;
GRANT EXECUTE ON OWA_UTIL TO PORTAL_USER;
GRANT EXECUTE ON HTP TO PORTAL_USER;
GRANT EXECUTE ON HTF TO PORTAL_USER;
GRANT EXECUTE ON WPG_DOCLOAD TO PORTAL_USER;

---

4. Phase 3: Configuring the ORDS Database Pool

In modern ORDS (versions 22.x, 23.x, and 24.x), configuration is managed using the ords command-line utility, which stores XML and JSON settings in hierarchical configuration directories.

Directory Structure

/etc/ords/config/
|-- databases/
|   |-- default/
|   |   \-- pool.xml
|   \-- portal_dad/
|       \-- pool.xml
\-- global/
    \-- settings.xml

Step 1: Initialize the Target Database Pool

Execute the ords config CLI to create a dedicated pool matching the legacy DAD name:

# Set ORDS configuration folder
export ORDS_CONFIG=/etc/ords/config

# Create pool named "portal_dad"
ords config --config /etc/ords/config set-property --pool portal_dad db.connectionType basic
ords config --config /etc/ords/config set-property --pool portal_dad db.hostname dbhost.internal.net
ords config --config /etc/ords/config set-property --pool portal_dad db.port 1521
ords config --config /etc/ords/config set-property --pool portal_dad db.servicename PRODPDB.corp
ords config --config /etc/ords/config set-property --pool portal_dad db.username ORDS_PUBLIC_USER

# Securely set the database password
ords config --config /etc/ords/config secret --pool portal_dad db.password

Step 2: Configure PL/SQL Gateway Parameters in pool.xml

Edit the generated /etc/ords/config/databases/portal_dad/pool.xml or use CLI properties to enable PL/SQL gateway processing and document handling:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <comment>Configuration for legacy mod_plsql portal_dad migration</comment>
  <entry key="db.connectionType">basic</entry>
  <entry key="db.hostname">dbhost.internal.net</entry>
  <entry key="db.port">1521</entry>
  <entry key="db.servicename">PRODPDB.corp</entry>
  <entry key="db.username">ORDS_PUBLIC_USER</entry>
  
  <!-- JDBC Pool Sizing Tuning -->
  <entry key="jdbc.InitialLimit">10</entry>
  <entry key="jdbc.MinLimit">10</entry>
  <entry key="jdbc.MaxLimit">100</entry>
  <entry key="jdbc.MaxConnectionReuseCount">10000</entry>
  
  <!-- PL/SQL Gateway Handlers -->
  <entry key="plsql.gateway.mode">direct</entry>
  <entry key="plsql.gateway.defaultPage">portal_home.main</entry>
  <entry key="plsql.gateway.docTable">PORTAL_USER.app_documents_table</entry>
  <entry key="plsql.gateway.docPath">docs</entry>
  <entry key="plsql.gateway.docProcedure">PORTAL_USER.portal_doc_download.show</entry>
  
  <!-- Security Lockdown: Prevent execution of unauthorized packages -->
  <entry key="procedure.rest.preHook">PORTAL_USER.portal_security.validate_request</entry>
</properties>

---

5. Phase 4: Preserving Legacy URLs via Reverse Proxy (Caddy & Nginx)

In mod_plsql, client browsers, desktop shortcuts, and third-party integrations relied on endpoints starting with /pls/portal_dad/.... By default, standalone ORDS listens at /ords/portal_dad/....

Changing URLs breaks existing user bookmarks and automated external scripts. Deploying an enterprise reverse proxy in front of ORDS seamlessly maps legacy /pls/ requests to ORDS without requiring client adjustments.

Option A: Hardened Caddy 2 Reverse Proxy Configuration

portal.enterprise.net {
    log {
        output file /var/log/caddy/portal_access.log {
            roll_size 50mb
            roll_keep 10
        }
    }

    # Redirect naked DAD requests to default procedure
    redir /pls/portal_dad /pls/portal_dad/portal_home.main permanent
    redir /pls/portal_dad/ /pls/portal_dad/portal_home.main permanent

    # Route legacy /pls/ traffic to backend ORDS instance
    handle_path /pls/* {
        rewrite * /ords{path}
        reverse_proxy 127.0.0.1:8080 {
            header_up X-Forwarded-Prefix /pls
            header_up Host {host}
            header_up X-Real-IP {remote_host}
        }
    }

    # Route native /ords/ traffic directly
    handle /ords/* {
        reverse_proxy 127.0.0.1:8080 {
            header_up Host {host}
            header_up X-Real-IP {remote_host}
        }
    }

    # Block access to internal ORDS metadata and admin panels
    @blocked_endpoints {
        path /ords/_*
        path /ords/metadata/*
    }
    respond @blocked_endpoints 403
}

Option B: Hardened Nginx Reverse Proxy Configuration

upstream ords_backend {
    server 127.0.0.1:8080 max_fails=3 fail_timeout=10s;
    keepalive 32;
}

server {
    listen 443 ssl http2;
    server_name portal.enterprise.net;

    ssl_certificate /etc/ssl/certs/portal.enterprise.net.crt;
    ssl_certificate_key /etc/ssl/certs/portal.enterprise.net.key;
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers HIGH:!aNULL:!MD5;

    # Redirect naked DAD to default entry point
    location = /pls/portal_dad {
        return 301 https://$host/pls/portal_dad/portal_home.main;
    }
    location = /pls/portal_dad/ {
        return 301 https://$host/pls/portal_dad/portal_home.main;
    }

    # Rewrite /pls/ into /ords/ transparently
    location /pls/ {
        rewrite ^/pls/(.*)$ /ords/$1 break;
        proxy_pass http://ords_backend;
        
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_http_version 1.1;
        proxy_set_header Connection "";
        
        # Buffer tuning for large PL/SQL Web Toolkit HTML payloads
        proxy_buffers 16 32k;
        proxy_buffer_size 64k;
        proxy_read_timeout 120s;
    }
}

---

6. Document Uploads, Downloads & Flexible Parameter Handling

Legacy mod_plsql systems frequently rely on file upload capabilities via multipart/form-data and flexible parameter passing via the exclamation mark syntax (!procedure_name).

Document Table Migration

In mod_plsql, files uploaded from HTML forms were written to a table specified by PlsqlDocumentTablename. The standard schema structure requires BLOB or LONG RAW columns:

CREATE TABLE PORTAL_USER.app_documents_table (
    name           VARCHAR2(256) PRIMARY KEY,
    mime_type      VARCHAR2(128),
    doc_size       NUMBER,
    dad_charset    VARCHAR2(128),
    last_updated   DATE,
    content_type   VARCHAR2(128),
    blob_content   BLOB
);

File Download Execution via wpg_docload

When serving file downloads in PL/SQL, legacy procedures call wpg_docload.download_file. Under ORDS, ensure your download procedure sets HTTP headers and invokes the package cleanly:

CREATE OR REPLACE PROCEDURE PORTAL_USER.download_report(p_file_id IN NUMBER) IS
    v_blob      BLOB;
    v_mime      VARCHAR2(100);
    v_filename  VARCHAR2(255);
    v_length    NUMBER;
BEGIN
    SELECT file_name, mime_type, file_data, DBMS_LOB.GETLENGTH(file_data)
      INTO v_filename, v_mime, v_blob, v_length
      FROM portal_attachments
     WHERE attachment_id = p_file_id;

    OWA_UTIL.MIME_HEADER(NVL(v_mime, 'application/octet-stream'), FALSE);
    HTP.P('Content-Length: ' || v_length);
    HTP.P('Content-Disposition: attachment; filename="' || v_filename || '"');
    OWA_UTIL.HTTP_HEADER_CLOSE;

    -- Hand over stream to ORDS servlet
    WPG_DOCLOAD.DOWNLOAD_FILE(v_blob);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        OWA_UTIL.STATUS_LINE(404, 'Not Found');
        HTP.P('<h1>404 File Not Found</h1>');
END download_report;
/

Flexible Parameter Passing (!procedure_name)

In mod_plsql, when HTML forms submit variable numbers of input fields with arbitrary names, the receiving procedure is prefixed with an exclamation mark:

-- Prefix with exclamation mark in HTML form target:
-- <form action="/pls/portal_dad/!process_dynamic_form" method="POST">

CREATE OR REPLACE PROCEDURE PORTAL_USER.process_dynamic_form(
    name_array   IN OWA.VC_ARR,
    value_array  IN OWA.VC_ARR
) IS
BEGIN
    FOR i IN 1..name_array.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(name_array(i) || ' = ' || value_array(i));
    END LOOP;
    
    HTP.HTMLOPEN;
    HTP.BODYOPEN;
    HTP.P('<h2>Submission Received Successfully</h2>');
    HTP.BODYCLOSE;
    HTP.HTMLCLOSE;
END process_dynamic_form;
/

ORDS supports flexible parameter passing natively without syntax modification.

---

7. Operational Troubleshooting & Triage Runbook

When cutting over from mod_plsql to ORDS, issues typically arise from authentication, connection exhaustion, or reverse proxy rewrites.

Diagnostic 1: Resolving HTTP 404 (Not Found)

• Symptom: Browser receives HTTP 404 immediately when requesting /pls/portal_dad/my_procedure.
• Root Cause:
  1. The reverse proxy rewrite rule failed to forward the correct pool prefix to ORDS.
  2. The procedure is not executable by ORDS_PUBLIC_USER or does not exist in the target schema.
• Resolution Steps:
  1. Check ORDS standalone logs or Tomcat catalina.out for requested URI path.
  2. Validate database pool registration:

     ords --config /etc/ords/config config list --pool portal_dad

  3. Verify execution grant:

     GRANT EXECUTE ON PORTAL_USER.my_procedure TO ORDS_PUBLIC_USER;

Diagnostic 2: Resolving ORA-01017 (invalid username/password)

• Symptom: ORDS logs show ORA-01017: invalid username/password; logon denied when initiating connections.
• Root Cause: The password stored in the ORDS secret store does not match the database password for ORDS_PUBLIC_USER, or proxy connect privileges were omitted.
• Resolution Steps:
  1. Reset the database password in Oracle:

     ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY "NewSecurePass2026!";

  2. Re-encrypt the secret in the ORDS pool configuration:

     ords --config /etc/ords/config secret --pool portal_dad db.password

  3. Verify that proxy connection is permitted:

     ALTER USER PORTAL_USER GRANT CONNECT THROUGH ORDS_PUBLIC_USER;

Diagnostic 3: Resolving ORA-12541 (TNS:no listener)

• Symptom: ORDS connection pool errors out with ORA-12541: Cannot connect to database.
• Root Cause: Target database listener is down or blocked by network firewalls.
• Resolution Steps:
  1. Test connectivity from the ORDS host using nc or sql:

     nc -zv dbhost.internal.net 1521
     sql ORDS_PUBLIC_USER@"dbhost.internal.net:1521/PRODPDB.corp"

  2. Verify that the Oracle database listener is running:

     lsnrctl status

Diagnostic 4: Resolving HTTP 500 (Internal Server Error)

• Symptom: Browser returns HTTP 500 with a generic ORDS error page.
• Root Cause: An unhandled PL/SQL runtime exception occurred within the application procedure (e.g., VALUE_ERROR, NO_DATA_FOUND).
• Resolution Steps:
  1. Enable temporary debug output in /etc/ords/config/global/settings.xml:

     <entry key="debug.debugger">true</entry>
     <entry key="debug.printDebugToScreen">true</entry>

  2. Re-trigger the request in your browser to view the complete PL/SQL call stack trace.
  3. Disable debug.printDebugToScreen immediately after resolving the exception to prevent information disclosure in production.

---

8. Cutover Checklist & Rollback Strategy

Execute this structured checklist during your production maintenance window:

• Pre-Cutover Verification:
  • ORDS standalone or container service started and enabled under systemd.
  • Target database ORDS_PUBLIC_USER unlocked with proxy connect granted to application schemas.
  • Document table access and wpg_docload procedures verified in test environment.
  • Reverse proxy (Caddy/Nginx) configured with /pls/ rewrite blocks.
  • Firewall rules validated between ORDS host and database listener port 1521.
• Production Cutover Execution:
  • Set Apache/OHS server to maintenance mode.
  • Update DNS or internal load balancer VIP to point traffic from legacy OHS servers to the new ORDS reverse proxy.
  • Tail ORDS access logs: journalctl -u ords -f.
  • Execute smoke test test scripts validating login, data rendering, file upload, and file download.
• Rollback Plan:
  • In the event of an unresolvable failure, revert DNS/VIP traffic routing back to legacy OHS mod_plsql server instances.
  • No database-level changes require rollback since ORDS_PUBLIC_USER proxy grants operate non-invasively alongside direct schema access.

---

📚 Official Documentation & Technical References

• Oracle REST Data Services Documentation: ORDS Installation and Configuration Guide (Release 24.x) — Comprehensive instructions for deploying ORDS pools and configuring servlet containers.
• Oracle Database PL/SQL Packages and Types Reference: PL/SQL Web Toolkit Documentation (HTP, HTF, OWA) — Reference guide for standard OWA procedures and hypertext output formatting.
• Oracle REST Data Services Developer’s Guide: Developing PL/SQL Applications with ORDS — Configuring PL/SQL Gateway mode, flexible parameter passing, and custom document procedures.

---

Planning a legacy mod_plsql decommission, or modernizing enterprise PL/SQL applications for Oracle 19c and 23ai? Consult with our Principal Oracle Architects or explore our Application Modernization & Database Migration Services.