apex_extract

A Python utility that parses an Oracle APEX application export (.sql) and writes per-page .sql, .js, .html, and .css files — making APEX applications easier to review, diff, and version-control.

---

The Problem

APEX application exports are monolithic .sql files containing thousands of lines of PL/SQL API calls. Embedded SQL queries, JavaScript, HTML, and CSS are encoded as string literals — invisible to standard diff tools and impossible to review meaningfully in version control.

apex_extract solves this by decomposing the export into discrete, human-readable files organized by page.

---

What It Extracts

Asset	Output
SQL queries, PL/SQL processes, validations, LOVs	page_NNNNN_<name>.sql
JavaScript (inline, Dynamic Actions)	page_NNNNN_<name>.js
HTML (static content regions)	page_NNNNN_<name>.html
CSS (inline page/region styles)	page_NNNNN_<name>.css
Shared List of Values	shared_lovs.sql
Page navigation map	navigation_map.md

---

Requirements

• Python 3.8+
• No external dependencies — standard library only

---

Usage

python apex_extract.py <exported_app.sql> [output_dir]

Examples:

# Output to ./extracted/
python apex_extract.py f100.sql

# Output to a specific directory
python apex_extract.py f100.sql my_app_extracted

---

Output Structure

output_dir/
├── shared_lovs.sql
├── navigation_map.md
├── page_00001_home.sql
├── page_00001_home.js
├── page_00010_employees.sql
├── page_00010_employees.css
└── ...

Each file is prefixed with the zero-padded page ID and the page name, making them sort naturally and diff cleanly.

---

Workflow: Version Control with Git

apex_extract is designed to fit into your own version control workflow. A typical Git setup:

# After each APEX export, re-extract and commit the diff
python apex_extract.py f100.sql extracted/
cd extracted/
git add .
git commit -m "APEX export 2024-01-15 — updated employee page SQL"

Because files are stable and consistently named, git diff will surface exactly what changed between exports — SQL logic, JavaScript behavior, HTML structure, or navigation.

---

Extracted Content Detail

SQL / PL/SQL

• Region source queries
• Page processes
• Validations
• Item sources (SQL/PL/SQL)
• Inline and shared LOV queries
• Display conditions

JavaScript

• Page-level javascript_code_onload
• Dynamic Action JavaScript code blocks
• Dynamic Action metadata (event, element, trigger)

HTML

• Static content regions
• HTML-detected region sources

CSS

• Page-level inline CSS
• Region-level inline CSS

Navigation Map (navigation_map.md)

• All page-to-page navigation links resolved by source
• Covers buttons, branches, and column links
• Targets linked to their output .sql files where resolvable

---

Limitations

• Targets APEX exports using the wwv_flow_imp_page / wwv_flow_imp_shared API (APEX 20.1+)
• Static LOV values (defined via create_static_lov_data) are noted but not expanded
• Plugin regions are noted but their internal source is not extracted
• The output is intended for review and diffing, not re-import into APEX

---

License

MIT