Building a Natural Language to SQL platform on Oracle APEX and OCI GenAI — so non-technical operations staff could query live transport data just by typing a question.
RoleArchitect & Lead Developer
PlatformOracle Cloud (OCI)
DomainTransport Operations
TypeAI / GenAI POC → Production
The Problem
Operations data locked behind SQL — invisible to the people who need it most
The transport company's operations team managed hundreds of vehicles, routes, drivers, and consignment records inside an Oracle Autonomous Database. Every meaningful business question required raising a ticket to IT for a custom SQL report — creating a 2–3 day lag between question and answer.
Business Goal: Allow operations managers to type questions in plain English and receive accurate, live query results from the Oracle ADB — with zero SQL knowledge required.
Architecture
Two-layer AI pipeline on Oracle infrastructure
1
Oracle APEX Chat InterfaceOperations staff enter plain-English questions via a lightweight APEX page embedded directly in the existing ops portal.
2
PL/SQL Sanitisation WrapperA custom PL/SQL function applies REGEXP_REPLACE to strip dangerous tokens (DROP, DELETE, TRUNCATE, semicolons) and enforces SELECT-only queries before passing to the AI layer.
3
DBMS_CLOUD_AI with OpenAI GPT-4o-miniOracle's native AI integration converts sanitised natural language into SQL using full schema context injected as system prompt.
4
Live ADB Query & Result RenderingGenerated SQL runs against the Oracle Autonomous Database. Results surface in APEX as a formatted data grid with plain-English explanation.
5
OCI GenAI as FallbackFor latency-sensitive queries, OCI GenAI (Cohere Command) was configured as a secondary profile, keeping data entirely within Oracle's cloud boundary.
Key Challenges
What nearly broke this project
⚠️
ORA-03048 / ORA-00904 — alias resolution errorsDBMS_CLOUD_AI generated SQL with computed column aliases Oracle's parser couldn't resolve in nested queries. Fixed with a REGEXP_REPLACE pass to rewrite alias references before execution.
🔐
Unauthorized ADB access security incidentA misconfigured network ACL exposed the ADB endpoint during testing. Remediated by locking down VCN security rules, rotating credentials, and implementing IP allowlisting for the APEX mid-tier only.
🤖
Hallucinated table and column namesGPT-4o-mini occasionally invented column names not in the schema. Solved by injecting full CREATE TABLE DDL as structured context and adding post-generation column existence validation.
🐌
OpenAI API latency on complex queriesMulti-table joins took 6–8 seconds. Mitigated by caching frequent query patterns and routing simpler questions to OCI GenAI.
Outcomes
Measurable impact
87%
First-attempt query accuracy
<10s
Average query response time
2–3d
IT report wait time eliminated
0
SQL knowledge required
Result: Approved for Phase 2 expansion — adding write-back capabilities and integration with the vehicle tracking API.