Generic AI fails at Oracle HCM because it doesn't know the schema. Here's how to load the right context — and the exact system prompt that makes Claude handle HDL errors, OTBI queries, and Fast Formula like a senior consultant.
Ask ChatGPT or Claude cold: "Write me a SQL query to get current headcount by department in Oracle HCM."
You'll get something that looks right. You'll run it. It will either fail or return 5–20 rows per employee. Here's why:
PER_ALL_ASSIGNMENTS_F — deprecated since Oracle's 24B update. Current table is PER_ALL_ASSIGNMENTS_M, which has a completely different deduplication pattern.EFFECTIVE_LATEST_CHANGE = 'Y' — the filter that prevents duplicate rows in _M tables. Without it, you get multiple assignment records per employee.HR_WORKER_NOT_FOUND has 6 common causes. Generic AI picks one at random. An HCM-trained assistant knows which cause matches which attribute value pattern.The model isn't the problem. Context is the problem. Oracle Fusion HCM has 14,950 tables and 1.2 million columns — a body of knowledge no general-purpose AI has seen deeply enough. Load that context, and the same Claude model that gave you wrong answers becomes a useful senior consultant.
The unlock: Oracle HCM has enough documentation, error patterns, and SQL conventions that a well-structured system prompt covers 80–90% of daily consultant tasks. You don't need a fine-tuned model. You need the right context window.
You cannot load Oracle's full schema documentation into a context window. You don't need to. The 20% of Oracle HCM knowledge that solves 80% of consultant problems is:
| Context Category | What to Include | Skip |
|---|---|---|
| Table cheat sheet | Top 15 tables per module with their primary key and date-effective filter pattern | All 14,950 tables — context overflow, noise |
| HDL error codes | Top 20 error names, their common causes, and the verification SQL query for each | Full HDL API documentation — too dense, rarely needed |
| OTBI rules | Logical SQL syntax differences, subject area names, common errors and fixes | Full OTBI administrator guide — not relevant to query writers |
| Fast Formula patterns | Working template for each formula type (payroll, absence, benefits) with common function signatures | Full Fast Formula Language Reference — too long |
| Date-effective rules | The 3 date filter patterns that prevent duplicate rows in every common join | All 30+ date filter variants — creates ambiguity |
This fits in a Claude Project system prompt (~3,000 tokens). It's enough to handle HDL debugging, OTBI query generation, Fast Formula issues, and compliance SQL — the daily work of an Oracle HCM consultant or HRIS analyst.
Claude Projects (available in Claude Pro and Claude for Teams) let you store a persistent system prompt and files that persist across conversations. This is the right setup for Oracle HCM work — you set it up once and every conversation has the context loaded automatically.
In Claude.ai, click "Projects" in the left sidebar → "New Project." Name it "Oracle HCM Assistant" or similar. You can create multiple projects for different modules (e.g., "Payroll Oracle HCM" separate from "Benefits Oracle HCM").
In the project settings, find "Custom Instructions" or "Project Instructions." Paste in your Oracle HCM system prompt here. This text is injected automatically into every conversation in this project. See the prompt structure below.
You can upload files to the project that Claude can search. Good candidates: your organization's lookup code reference spreadsheet, your custom element list, the specific OTBI subject area columns for your implementation, and any HDL error logs from recent failed loads.
Before using in production: paste a real HDL error log and verify the root cause is accurate. Ask for a headcount query and verify the table names are current (should use PER_ALL_ASSIGNMENTS_M, not _F). Submit an OTBI question and check that the Logical SQL syntax is correct.
Data sensitivity: Do not paste real employee data (names, person numbers, SSNs) into Claude or any external AI. Use dummy person numbers during testing. Oracle HCM data is PII — keep it on-prem or in Oracle's tenancy.
Here's the framework for an effective Oracle HCM system prompt. Each section has a specific purpose — don't skip sections, but keep each one tight.
## Identity You are an Oracle Fusion Cloud HCM specialist. You know: - The HCM data model (key tables, date-effective join patterns) - HDL error diagnosis (error codes → root causes → verification SQL) - OTBI Logical SQL (not standard SQL — different syntax, different rules) - Fast Formula (payroll, absence, benefits formula types) If you are uncertain about a table or column name, say so. Do NOT hallucinate Oracle schema. ## Critical Rules (Always Apply) 1. ASSIGNMENTS: Use PER_ALL_ASSIGNMENTS_M (not _F — deprecated 24B). Filter: EFFECTIVE_LATEST_CHANGE = 'Y' 2. DATE-EFFECTIVE TABLES (_F suffix): Always filter: TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE 3. OTBI LOGICAL SQL: Column paths use quoted subject area format. NOT: FROM PER_ALL_ASSIGNMENTS_M YES: FROM "Workforce Management - Worker Assignment Real Time" 4. HDL ERRORS: Check HRC_INTEGRATION_RUNS → HRC_INTEGRATION_ERRORS → HRC_INTEGRATION_ERROR_MESSAGES in that order. ## Key Tables Quick Reference [paste your table cheat sheet here — 15-20 rows max] ## HDL Error Code Library [paste top 10-20 HDL errors with root cause + verification SQL] ## OTBI Subject Areas [paste subject area name → use case mapping] ## Response Format 1. Show SQL/code first, then explain 2. For HDL: show verification query before suggesting fix 3. For OTBI: state which subject area the query targets 4. Flag deprecated tables/approaches 5. State assumptions explicitly (LDG, business group, country)
The full version of this prompt — with complete table cheat sheets, all 20 HDL error codes, subject area mapping, Fast Formula templates, and response format rules — is available as a ready-to-paste document. See the link below.
The full system prompt includes: 15-table cheat sheet, 20 HDL error codes with verification SQL, OTBI subject area guide, Fast Formula templates for payroll/absence/benefits, and response format rules. Copy-paste into Claude Projects or ChatGPT Custom Instructions.
Get the System Prompt →The single highest-ROI use case for an Oracle HCM AI assistant is HDL error debugging. A support call for an HDL failure runs 2–5 days on average. A well-prompted AI can reduce this to 15 minutes for common errors.
The key: Don't just paste the error message. Paste the output from the 3-table debug chain.
SELECT hir.INTEGRATION_RUN_ID, hie.BUSINESS_OBJECT, hiem.MESSAGE_NAME, hiem.ATTRIBUTE_NAME, hiem.ATTRIBUTE_VALUE, hiem.MESSAGE_TEXT FROM HRC_INTEGRATION_RUNS hir JOIN HRC_INTEGRATION_ERRORS hie ON hir.INTEGRATION_RUN_ID = hie.INTEGRATION_RUN_ID JOIN HRC_INTEGRATION_ERROR_MESSAGES hiem ON hie.INTEGRATION_ERROR_ID = hiem.INTEGRATION_ERROR_ID WHERE hir.INTEGRATION_RUN_ID = :your_run_id ORDER BY hie.RECORD_NUMBER, hiem.ERROR_SEQUENCE;
Then paste the results into your AI assistant with this prompt structure:
With the correct system prompt loaded, you'll get a specific root cause (likely: person number exists but effective date gap, or ASSIGNMENT_TYPE mismatch), the exact verification query, and the fix. Without the system prompt, you get generic Oracle documentation summary that doesn't help.
Most likely cause: The person number exists but there is a date-effective gap — the person record has EFFECTIVE_END_DATE before the Work Relationship effective date in your .dat file. This is different from the person not existing at all (which would show no rows at all in the verification query).
Verification query: SELECT PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE FROM PER_ALL_PEOPLE_F WHERE PERSON_NUMBER = '1234567' ORDER BY EFFECTIVE_START_DATE — look for a gap between the last end date and your intended work relationship start date.
Fix: Either update the person's effective end date in Oracle or adjust your Work Relationship START_DATE in the .dat file to fall within a valid person record date range.
OTBI Logical SQL fails when you don't give the AI enough context about the subject area and the specific column names. Here's the prompt pattern that gets correct Logical SQL on the first attempt:
The critical elements:
| Mistake | How to Spot It | Fix Prompt |
|---|---|---|
| Uses standard SQL table names | Query has FROM PER_ALL_ASSIGNMENTS_M | "Use OTBI Logical SQL with quoted subject area column paths, not database table names" |
| Uses DATEDIFF instead of TIMESTAMPDIFF | Query has DATEDIFF(day, ...) | "Use TIMESTAMPDIFF(SQL_TSI_DAY, ...) — OTBI doesn't support DATEDIFF" |
| Tries to JOIN subject areas with commas | Query has FROM subjectA, subjectB | "OTBI doesn't support multi-subject-area joins in Logical SQL — use one subject area or switch to BI Publisher SQL" |
| Uses SELECT DISTINCT | Query starts with SELECT DISTINCT | "Use COUNT DISTINCT or GROUP BY instead — OTBI doesn't support SELECT DISTINCT" |
| Wrong effective date filter | No date filter, or wrong column used | "Add effective date filter using the 'Effective Date' column in the subject area, not a database column" |
Fast Formula is one of the highest-value AI use cases because Stack Overflow and Google have essentially no Fast Formula coverage. An AI with the right context can debug formula syntax errors and write working formulas from business requirements.
Pro tip: Always specify the formula type (Payroll, Absence, Benefits, Compensation) in your prompt. Fast Formula has different built-in functions and context variables available in each type — the AI needs this to generate correct code.
Both models work with the right system prompt. The differences matter for Oracle HCM:
| Capability | Claude (Anthropic) | ChatGPT (OpenAI) |
|---|---|---|
| Context window | 200K tokens (Claude Pro) | 128K tokens (GPT-4o) |
| System prompt storage | Claude Projects — persists across sessions | GPT Custom Instructions — persists, less structured |
| File upload | Project files — searched across conversations | Attachments per conversation, not persistent |
| Code generation quality | Slightly better at following strict syntax rules | Comparable on SQL; slightly better at explaining errors |
| Hallucination rate | Lower when instructed to flag uncertainty | Higher tendency to generate plausible-looking wrong schema |
| Oracle HCM specificity | Needs explicit context (as described above) | Same — no model has been trained on Oracle HCM specifically |
| Verdict for HCM work | Better for long sessions, HDL multi-step debug | Better for quick one-off queries, when no project setup |
Bottom line: Claude Projects wins for ongoing Oracle HCM consulting work because the context is loaded automatically. ChatGPT custom GPTs are the equivalent setup but require more manual configuration. Either model works — the system prompt quality matters more than the model choice.
Setting the right expectations matters. There are things an AI assistant — even one with perfect Oracle HCM context — should not be trusted to do:
COUNT(*) first, then a FETCH FIRST 10 ROWS ONLY preview before full execution.The verification rule: For any AI-generated SQL that will touch production Oracle HCM data, run SELECT COUNT(*) FROM (...your query...) first. If the count is unexpected, investigate before proceeding. The AI is a first draft, not a final answer.
Within those guardrails, an Oracle HCM AI assistant with the right setup is a genuine force multiplier. HDL errors that would take 2 days get resolved in 20 minutes. OTBI queries that would require a consultant get written in 5. Fast Formula syntax issues that have no Stack Overflow answers get debugged in one conversation.
The setup is a one-time 30-minute investment. The daily time savings compound every week you use it.
Ready-to-paste system prompt, 50+ production SQL queries, and the OBIEE Logical SQL cheat sheet — the three documents that make Oracle HCM consulting faster. Used as the foundation for this guide.
Request the Toolkit →Beyond Oracle HCM
Looking for AI tools outside the Oracle ecosystem? AI Tools HQ covers 1,000+ AI tools for HR, finance, and business operations — with verified reviews and use-case comparisons that help you evaluate what to pair with your HCM stack.
Browse AI Tools HQ →