Oracle EBS + AI Integration
Building an AI-Powered Natural Language Query Tool for Oracle EBS
How I combined 15+ years of Oracle EBS expertise with Claude AI and Python to let business users query enterprise data in plain English — no SQL knowledge required.
The Problem Every Oracle EBS Team Knows
In most Oracle EBS implementations, getting data out of the system follows a painfully familiar cycle. A business user needs a report, raises a ticket to the IT team, a developer writes an SQL query against PO_HEADERS_ALL or AP_SUPPLIERS, runs it, exports to Excel, and emails the file. The whole journey takes three to five days — for data that already exists in the database.
This creates an unnecessary dependency on IT for every business decision. Finance cannot self-serve vendor spend analysis. Procurement cannot check outstanding POs without scheduling a meeting. The data is there; the access is not.
Introducing PranayAI Assistant
PranayAI Assistant is an AI-powered natural language query interface for Oracle EBS R12.2. Business users select a module — Purchasing, Payables, Suppliers, Customers, Inventory, or Order Management — type a question in plain English, and receive results as an interactive table with a one-click Excel download. No SQL. No table names. No IT ticket.
How It Works: The Architecture
The Key Innovation: Dynamic Column Discovery
Most Text-to-SQL tools hardcode table structures. This creates an immediate problem in Oracle EBS: custom columns, client-specific implementations, and version differences mean that any hardcoded schema becomes wrong almost immediately.
PranayAI solves this with a real-time column query before every AI call:
def get_table_columns(cursor, table_names):
"""Query Oracle metadata for real column names"""
all_table_info = ""
for table in table_names:
cursor.execute("""
SELECT column_name, data_type
FROM all_tab_columns
WHERE table_name = :tname
ORDER BY column_id
""", {"tname": table.upper()})
cols = cursor.fetchall()
col_list = ", ".join([c[0] for c in cols])
all_table_info += f"""
Table: {table}
Columns: {col_list}
"""
return all_table_infoThis means Claude receives the actual columns from your specific Oracle EBS instance — not a generic schema from a textbook. The result is SQL that executes the first time, every time.
Supported Business Modules
| Business Module | EBS Tables Included |
|---|---|
| Purchasing | PO_HEADERS_ALL, PO_LINES_ALL, PO_DISTRIBUTIONS_ALL, AP_SUPPLIERS, AP_INVOICES_ALL |
| Payables | AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, AP_PAYMENTS_ALL, AP_SUPPLIERS |
| Suppliers | AP_SUPPLIERS, AP_SUPPLIER_SITES_ALL |
| Customers | HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL |
| Inventory | MTL_SYSTEM_ITEMS_B, MTL_ONHAND_QUANTITIES, MTL_TRANSACTIONS_TEMP |
| Order Management | OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, RA_CUSTOMER_TRX_ALL |
Technology Stack
Business Impact
- 3–5 days per report
- IT ticket required
- SQL expertise needed
- Delayed decisions
- IT team bottleneck
- Results in 30 seconds
- Self-service for business
- Plain English only
- Data-driven decisions
- IT team freed up
Lessons from Building This
The most important lesson: always return values from functions. Several bugs in early development traced back to a missing return all_table_info or return system_prompt. When a function returns nothing, Claude receives an empty schema and starts guessing column names — which produces plausible-looking SQL that fails at runtime with ORA-00904.
The second lesson: Oracle Instant Client initialization must happen outside any file-reading context manager. Calling oracledb.init_oracle_client() inside a with open(...) block causes silent failures that are difficult to trace.
The third lesson: clean Claude's SQL output aggressively. Strip markdown fences, remove trailing semicolons, and call strip() — Oracle's SQL parser has no tolerance for extra characters that a language model naturally adds.
- Function Calling — create EBS transactions via English ("Create supplier Tech Corp")
- Claude Vision — upload an invoice image → auto-extract to Oracle EBS
- LangChain integration for multi-step reasoning
- Oracle APEX + FastAPI production UI
- RAG over EBS documentation — no more hallucination on policies
Conclusion
Combining 15 years of Oracle EBS domain expertise with modern AI is not just possible — it produces tools that genuinely change how enterprise data is accessed. PranayAI Assistant proves that the gap between a business question and an Oracle EBS answer can be measured in seconds rather than days.
The future of Oracle EBS is AI-powered, and the people best placed to build it are the architects who already know what the data means.
15+ years in Oracle EBS (11i / R12.2), OIC, APEX, and enterprise architecture. Currently building the intersection of Oracle EBS and AI at Jtekt. Connect on LinkedIn to follow the PranayAI journey.
0 Comments