1️⃣ Basic Oracle SQL with Vibe Coding
🗣 You say (in vibe style)
“Give me top 5 employees by salary in each department from EMP and DEPT tables, only active employees, ordered by department and salary descending.”
🤖 AI generates (Oracle SQL)
SELECT *
FROM (
SELECT
d.department_name,
e.employee_id,
e.first_name,
e.last_name,
e.salary,
ROW_NUMBER() OVER (
PARTITION BY d.department_id
ORDER BY e.salary DESC
) rn
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
WHERE e.status = 'ACTIVE'
)
WHERE rn <= 5
ORDER BY department_name, salary DESC;
You didn’t mention ROW_NUMBER, PARTITION BY, etc.
You just gave the intent → AI handled the correct SQL pattern.
2️⃣ Slightly Advanced: Date Logic + Aggregation
🗣 You say
“Get monthly sales total and count of orders for the last 6 months from the orders table, group by year and month, sorted by most recent month first.”
🤖 AI generates
SELECT
TO_CHAR(order_date, 'YYYY') AS order_year,
TO_CHAR(order_date, 'MM') AS order_month,
SUM(order_total) AS total_sales,
COUNT(*) AS order_count
FROM orders
WHERE order_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6)
GROUP BY
TO_CHAR(order_date, 'YYYY'),
TO_CHAR(order_date, 'MM')
ORDER BY
order_year DESC,
order_month DESC;
Again: you didn’t talk about ADD_MONTHS, TRUNC, GROUP BY details — vibe coding fills them in.
3️⃣ SQL Tuning via Vibe Coding
You already have a query, but it’s slow.
🗣 You say
“Rewrite this query in a more efficient way for Oracle. Prefer analytic functions, avoid correlated subqueries if possible, and make it index-friendly.”
SELECT d.department_name,
e.employee_id,
e.salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
🤖 AI suggests optimized version
SELECT department_name,
employee_id,
salary
FROM (
SELECT
d.department_name,
e.employee_id,
e.salary,
RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) rnk
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
)
WHERE rnk = 1;
Optionally, AI might also suggest:
-
Index on
(department_id, salary DESC) -
Gathering stats
-
Checking execution plan
4️⃣ Oracle Cloud ERP – HR Example (PER_ALL_PEOPLE_F / ASSIGNMENTS)
Scenario
“Show me current active employees with legal employer and job for India legal entities only.”
🗣 You say
“Get a list of active workers in Oracle Fusion HCM with their name, person number, legal employer, job, and assignment status. Only include current assignments and only workers in India legal entities.”
🤖 AI generates (sample-style SQL for Fusion HCM BI/OTBI / DB link-type reporting)
SELECT
ppf.person_number,
ppf.full_name,
paaf.assignment_number,
hle.name AS legal_employer,
pj.job_name,
paaf.assignment_status_type
FROM per_all_people_f ppf
JOIN per_all_assignments_f paaf
ON paaf.person_id = ppf.person_id
JOIN hr_all_organization_units_f hle
ON hle.organization_id = paaf.legal_entity_id
JOIN per_jobs_f pj
ON pj.job_id = paaf.job_id
WHERE TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN hle.effective_start_date
AND hle.effective_end_date
AND hle.location_code = 'IN' -- India
AND paaf.assignment_status_type = 'ACTIVE';
You only told the business requirement → AI handled effective date conditions, joins, and basic filters.
5️⃣ Oracle Cloud ERP – Financials / AR & HZ (Customer + Open Invoices)
Scenario
“Show customers with overdue invoices > 30 days.”
🗣 You say
“Give me a list of customers with overdue invoices more than 30 days, including customer name, invoice number, invoice date, due date, currency, and outstanding amount. Only invoices with balance greater than 0.”
🤖 AI generates
SELECT
hc.party_name AS customer_name,
hca.account_number,
trx.trx_number AS invoice_number,
trx.trx_date,
trx.due_date,
trx.invoice_currency_code,
aps.amount_due_remaining AS outstanding_amount,
(TRUNC(SYSDATE) - trx.due_date) AS days_overdue
FROM ar_payment_schedules_all aps
JOIN ra_customer_trx_all trx
ON trx.customer_trx_id = aps.customer_trx_id
JOIN hz_cust_accounts hca
ON hca.cust_account_id = trx.bill_to_customer_id
JOIN hz_parties hc
ON hc.party_id = hca.party_id
WHERE aps.amount_due_remaining > 0
AND trx.trx_class = 'INV'
AND trx.due_date < TRUNC(SYSDATE) - 30
ORDER BY days_overdue DESC;
You don’t need to remember that overdue = SYSDATE - due_date, or which AR table holds what.
Vibe prompt → proper AR joins.
6️⃣ Using Vibe Coding for OTBI / BI Publisher
Even when you’re not writing pure SQL, vibe coding still helps.
🗣 You say
“Create an OTBI analysis for Payables invoices showing supplier, invoice number, invoice amount, invoice date, payment status, and ledger. Filter for invoices created in the last 90 days and only for primary ledger.”
🤖 AI helps with:
-
Suggesting subject area:
Payables Invoices – Transactions Real Time -
Suggesting columns to add.
-
Filters:
-
Invoice Creation Date >= sysdate - 90 -
Ledger = Primary Ledger
-
-
Prompts: Supplier Name (optional).
It may also generate a BI Publisher data model query if you ask:
“Generate the SQL for a BI Publisher data model for this requirement.”
7️⃣ Vibe Coding for HDL (Worker.dat) – Not SQL, but same concept
🗣 You say
“Create an HDL Worker.dat example to load a new hire with person number 5001, name Ramesh Kumar, hire date 01-Jan-2025, legal employer India LE, assignment to ‘Employee’ worker type and ‘Full-Time’ assignment category.”
🤖 AI generates (sample HDL)
METADATA|Worker|PersonNumber|EffectiveStartDate|EffectiveEndDate|ActionCode|ReasonCode|LegalEmployerName|WorkerType
MERGE|Worker|5001|2025/01/01|4712/12/31|HIRE|NEWHIRE|India LE|EMP
METADATA|WorkerAssignment|PersonNumber|AssignmentNumber|ActionCode|EffectiveStartDate|AssignmentStatusTypeCode|AssignmentType|WorkerType|AssignmentCategory
MERGE|WorkerAssignment|5001|5001-1|HIRE|2025/01/01|ACTIVE|E|EMP|FULL_TIME
You don’t remember HDL syntax exactly; you just express intent.
8️⃣ How you can practically use this with Oracle SQL
Here’s how you can phrase prompts when using any AI assistant (including me 😄) in vibe coding style:
-
“Write Oracle SQL to…”
-
“…get open POs by supplier and BU with total amount and status.”
-
-
“Optimize this Oracle SQL for performance…”
-
Paste your query and say
“…prefer analytic functions, avoid nested subqueries, watch for full table scans.”
-
-
“Convert this query from generic SQL to Oracle-specific syntax…”
-
e.g. using
NVL,TRUNC,TO_CHAR, date format etc.
-
-
“Explain this Oracle SQL in simple language…”
-
Great for reviewing Oracle Cloud technical spec or OTBI SQL.
-
If you like, next I can:
-
Take one of your real Oracle SQLs (e.g., from Fusion ERP / EBS / OTBI / BIP) and:
-
✅ Refactor it using a “vibe” prompt
-
✅ Show you exactly how to phrase the prompt
-
✅ Show before/after and reasoning
-
You can paste any query you often struggle with (date ranges, analytic functions, performance, etc.), and I’ll treat it like a live vibe-coding session.
