Building a Natural Language Query Engine on Oracle AI Database 26ai

A Step-by-Step Journey from 42% to 100% Accuracy — Weeks 6 to 10

Platform: Oracle APEX 24.2 + Oracle AI Database 26ai (Always Free)
LLM: Google Gemini 1.5 Flash via DBMS_CLOUD_AI
Schema: WKSP_STOCKTRADE (AI layer) over WKSP_STOCKDATA (10 years NSE + AMFI data)
Goal: Type “what is the 5 year CAGR of TCS?” and get the correct, data-verified answer

---

The Architecture in One Line

The LLM is a translator, not a data source. It converts natural language to SQL. The database — holding 10 years of daily NSE bhavcopy and AMFI NAV data — is the source of truth. Every number returned is verified and auditable.

User question
     ↓
Intent classification (SQL vs CHAT vs SIMILARITY)
     ↓
Entity resolution (HDFC Bank → HDFCBANK)
     ↓
RAG retrieval (right view + glossary + few-shot examples)
     ↓
Prompt assembly
     ↓
Gemini 1.5 Flash (action='chat') → raw SQL
     ↓
SQL validation (syntax + security + whitelist)
     ↓
Execute via read-only AI_EXEC user
     ↓
Narration layer (plain English answer)
     ↓
Return to user

---

Week 6: The Evaluation Harness — Measure Before You Improve

The most important thing you can build before any AI work is a measurement system. Without it, “is the pipeline better?” is just an opinion.

Why this comes first

Most people build the AI feature first and then test it manually. That’s backwards. The eval harness lets you run 20 (or 200) test questions automatically, score each one, and see a single number. Every improvement in Weeks 7-10 is measured against the baseline. The number doesn’t lie.

Three tables, one package, one view

-- Table 1: The question bank
CREATE TABLE wksp_stocktrade.ai_eval_question (
  question_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  question_text   VARCHAR2(500)  NOT NULL,
  question_type   VARCHAR2(20)   NOT NULL,  -- LOOKUP/METRIC/AGGREGATE/TIME/COMPARE/GENERAL
  reference_sql   VARCHAR2(4000) NOT NULL,  -- the correct SQL, written BEFORE running Select AI
  expected_cols   VARCHAR2(500),            -- columns to compare
  numeric_tol_pct NUMBER DEFAULT 1,         -- tolerance % for numeric comparison
  is_active       CHAR(1) DEFAULT 'Y'
);

-- Table 2: Run header
CREATE TABLE wksp_stocktrade.ai_eval_run (
  run_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  run_date     DATE DEFAULT TRUNC(SYSDATE),
  model_name   VARCHAR2(100),
  correct      NUMBER,
  partial      NUMBER,
  wrong        NUMBER,
  error_count  NUMBER,
  pct_strict   NUMBER(5,2),   -- correct/total %
  pct_partial  NUMBER(5,2)    -- (correct + partial×0.5)/total %
);

-- Table 3: Per-question result
CREATE TABLE wksp_stocktrade.ai_eval_result (
  result_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  run_id         NUMBER NOT NULL,
  question_id    NUMBER NOT NULL,
  generated_sql  CLOB,
  ref_result     VARCHAR2(4000),
  gen_result     VARCHAR2(4000),
  score          VARCHAR2(10),    -- CORRECT/PARTIAL/WRONG/ERROR/SKIP
  failure_reason VARCHAR2(200)    -- ENTITY/CATEGORY/RECOMPUTE/INTENT/JOIN
);

Key insight: write reference SQL BEFORE running the AI

This seems obvious but matters enormously. If you write the reference SQL after seeing what the AI produced, you unconsciously write it to match. The reference SQL must be written independently — that’s what you’re measuring against.

The scoring package (simplified)

CREATE OR REPLACE PACKAGE wksp_stocktrade.ai_eval_pkg AS
  FUNCTION  start_run(p_profile VARCHAR2, p_model VARCHAR2,
                      p_provider VARCHAR2) RETURN NUMBER;
  PROCEDURE run_question(p_run_id NUMBER, p_question_id NUMBER);
  PROCEDURE run_all(p_run_id NUMBER);
  PROCEDURE finish_run(p_run_id NUMBER);
END ai_eval_pkg;
/

The run_question procedure calls DBMS_CLOUD_AI.GENERATE(action='showsql'), executes both the reference SQL and the generated SQL, compares the first row of results, and scores CORRECT/PARTIAL/WRONG/ERROR.

Numeric comparison uses tolerance (±1% for prices, ±5% for Sharpe ratios). Text comparison is case-insensitive exact match. Aggregate comparisons check if the correct entity appears in the top-N results.

Baseline result — run_id 3, Gemini 1.5 Flash, schema metadata only

Correct:  8  (42.1%)
Partial:  3  (50.0% with partial credit)
Wrong:    8
Skipped:  1 (GENERAL intent — prose, can't auto-score)

42.1% strict accuracy is the baseline. This is Gemini 1.5 Flash with only the 9 AI view names and their COMMENT ON metadata. No glossary, no examples, no entity resolver. Every improvement from here is measured against this number.

Failure analysis — what went wrong

Failure type	Count	Cause
Entity resolution	8	UPPER(company_name)='HDFC Bank' — column doesn’t exist; symbol is the key
Category literals	3	WHERE category='FLEXI CAP' — correct value is 'EQ_FLEXI_CAP'
Recomputed metrics	2	Computed Sharpe as CAGR/volatility instead of reading mf_scheme_stats.sharpe_ratio
Intent mismatch	1	“What is a flexi cap fund?” generated SQL instead of chat response

These failures are clean and fixable. The model’s SQL structure is excellent — it picks reasonable views and writes syntactically correct SQL. The failures are semantic: wrong literals, wrong entity keys, wrong intent. RAG retrieval + glossary + entity resolver close these gaps.

---

Week 7: The RAG Retrieval Layer — Teaching the LLM Your Schema

RAG (Retrieval-Augmented Generation) means: instead of sending all 9 view names to the LLM and hoping it picks the right one, embed your schema documentation, embed the user’s question, and retrieve only the most relevant context. Add glossary terms and example SQL that are semantically close to the question.

What gets embedded

Three things, each for a different purpose:

1. Object catalog — which view to use for which question

-- 10 rows, one per AI view/table
-- Each row describes the object, its grain, sample questions, and what NOT to use it for
INSERT INTO wksp_stocktrade.ai_object_catalog
  (object_name, description, sample_question, usage_notes, do_not_use_for)
VALUES (
  'MF_SCHEME_STATS',
  'Pre-computed risk statistics for 1,777 canonical MF schemes. '||
  'Contains Sharpe ratio, Sortino ratio, max drawdown, volatility.',
  'What is Parag Parikh Sharpe ratio? Which flexi cap fund has lowest volatility?',
  'ALWAYS use this for Sharpe, Sortino. NEVER recompute Sharpe as CAGR/volatility.',
  'Do NOT use for: current NAV, raw CAGR'
);

-- Embed the description for semantic retrieval
UPDATE wksp_stocktrade.ai_object_catalog
SET embedding = VECTOR_EMBEDDING(
  wksp_stocktrade.ALL_MINILM_L12_V2
  USING (object_name||' '||description||' '||
         NVL(sample_question,'')||' '||NVL(usage_notes,'')) AS data)
WHERE embedding IS NULL;
COMMIT;

2. Business glossary — what terms mean in YOUR app specifically

-- 10 terms: CAGR, NAV, SHARPE_RATIO, ELIGIBLE_FUND, EQ_LARGE_CAP, etc.
INSERT INTO wksp_stocktrade.ai_glossary
  (term, definition, synonyms, canonical_sql, notes) VALUES
('EQ_LARGE_CAP',
 'Exact category value for large-cap equity mutual funds. '||
 'Use WHERE category=''EQ_LARGE_CAP'' — not ''Large Cap'' or ''LARGE CAP''.',
 'large cap, large-cap, top 100 stocks',
 'WHERE category=''EQ_LARGE_CAP'' AND years=5',
 'Category values: EQ_LARGE_CAP EQ_MID_CAP EQ_SMALL_CAP EQ_FLEXI_CAP EQ_ELSS');

-- Embed for retrieval
UPDATE wksp_stocktrade.ai_glossary
SET embedding = VECTOR_EMBEDDING(
  wksp_stocktrade.ALL_MINILM_L12_V2
  USING (term||' '||NVL(synonyms,'')||' '||definition) AS data)
WHERE embedding IS NULL;
COMMIT;

3. Few-shot examples — correct SQL patterns for the failure modes

CREATE TABLE wksp_stocktrade.ai_few_shot (
  shot_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  question       VARCHAR2(500) NOT NULL,
  sql_text       CLOB          NOT NULL,
  question_type  VARCHAR2(20),
  failure_reason VARCHAR2(100),  -- which failure mode this fixes
  embedding      VECTOR(384, FLOAT32),
  is_active      CHAR(1) DEFAULT 'Y'
);

-- 15 examples targeting exact failure modes from the baseline:

-- FIX: Category literal
INSERT INTO wksp_stocktrade.ai_few_shot (question, sql_text, failure_reason) VALUES (
  'how many eligible flexi cap funds are there',
  'SELECT COUNT(DISTINCT base_fund_key) AS flexi_cap_count
FROM   wksp_stocktrade.ai_mf_scheme
WHERE  category = ''EQ_FLEXI_CAP''    -- exact value, not ''Flexi Cap''
AND    is_eligible = ''Y''',
  'CATEGORY');

-- FIX: Entity resolution — use symbol not company name
INSERT INTO wksp_stocktrade.ai_few_shot (question, sql_text, failure_reason) VALUES (
  'what is the current price of HDFC Bank',
  'SELECT current_price, price_date, day_change_pct
FROM   wksp_stocktrade.ai_stock_snapshot
WHERE  symbol = ''HDFCBANK''    -- ticker not company name',
  'ENTITY');

-- FIX: Pre-computed metric — never recompute Sharpe
INSERT INTO wksp_stocktrade.ai_few_shot (question, sql_text, failure_reason) VALUES (
  'what is Parag Parikh Sharpe ratio',
  'SELECT c.scheme_name, s.sharpe_ratio, s.volatility_ann, s.cagr_5y
FROM   wksp_stocktrade.mf_scheme_stats s
JOIN   wksp_stocktrade.ai_mf_canonical c ON c.scheme_code = s.scheme_code
WHERE  UPPER(c.scheme_name) LIKE ''%PARAG PARIKH FLEXI%''
AND    s.as_of_date = TRUNC(SYSDATE)',
  'RECOMPUTE');

-- Embed all examples
UPDATE wksp_stocktrade.ai_few_shot
SET embedding = VECTOR_EMBEDDING(
  wksp_stocktrade.ALL_MINILM_L12_V2 USING question AS data)
WHERE embedding IS NULL AND is_active = 'Y';
COMMIT;

The entity resolver — the most important function

Maps fuzzy text (“HDFC Bank”, “parag parikh flexi”) to exact database keys (HDFCBANK, scheme_code 122639). Uses the all-MiniLM-L12-v2 ONNX model loaded directly into Oracle — no external API calls.

CREATE OR REPLACE FUNCTION wksp_stocktrade.resolve_entity(
  p_text        IN VARCHAR2,
  p_entity_type IN VARCHAR2 DEFAULT NULL,  -- 'NSE_STOCK' | 'MF_SCHEME' | NULL=both
  p_top_n       IN NUMBER   DEFAULT 3
) RETURN VARCHAR2 IS
-- Returns: 'TYPE:KEY:DISPLAY_TEXT' of best match
-- Example: 'NSE_STOCK:HDFCBANK:HDFCBANK — HDFC BANK LTD'
-- Returns NULL if no match found above threshold

  l_query_vec  VECTOR(384, FLOAT32);
  l_result     VARCHAR2(1000);
  l_dist       NUMBER;
  c_threshold  CONSTANT NUMBER := 0.55;  -- safe gap: real matches <0.45, noise >0.64

BEGIN
  SELECT VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2 USING p_text AS data)
  INTO   l_query_vec FROM dual;

  SELECT entity_type||':'||entity_key||':'||display_text, dist
  INTO   l_result, l_dist
  FROM (
    SELECT entity_type, entity_key, display_text,
           VECTOR_DISTANCE(embedding, l_query_vec, COSINE) AS dist
    FROM   wksp_stocktrade.ai_entity
    WHERE  (p_entity_type IS NULL OR entity_type = p_entity_type)
    ORDER  BY dist
    FETCH FIRST 1 ROW ONLY
  );

  -- Return NULL if distance too high (garbage input)
  IF l_dist > c_threshold THEN
    RETURN NULL;
  END IF;

  RETURN l_result;
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END resolve_entity;
/

Threshold calibration — how we arrived at 0.55:

Input	Distance	Should match?
HDFC Bank	0.31	✅ Yes
Infosys	0.28	✅ Yes
parag parikh flexi	0.45	✅ Yes
nonsense xyz abc	0.64	❌ No — return NULL

The gap between 0.45 (worst real match) and 0.64 (pure noise) is large. Setting threshold at 0.55 puts it safely in the middle. This is the right way to calibrate a threshold — measure real examples, measure garbage, set the boundary between them.

One important naming lesson: SBI Bluechip Fund was renamed to SBI Large Cap Fund in 2018 (SEBI recategorisation). The resolver correctly returned NULL for “sbi bluechip fund” — because the name doesn’t exist in the database. The fix: embed both old and new names in the embed_text column:

UPDATE wksp_stocktrade.ai_entity
SET    embed_text = 'SBI Bluechip Fund SBI Large Cap Fund large cap equity Direct Growth SBI EQ_LARGE_CAP',
       embedding  = VECTOR_EMBEDDING(
                      wksp_stocktrade.ALL_MINILM_L12_V2
                      USING 'SBI Bluechip Fund SBI Large Cap Fund large cap equity Direct Growth SBI EQ_LARGE_CAP' AS data)
WHERE  entity_type = 'MF_SCHEME'
AND    entity_key  = '119598';
COMMIT;

Final entity resolver result: 8/8 correct, NULL on garbage input.

Three retrieval functions

-- Schema retrieval: finds top-k most relevant views for the question
CREATE OR REPLACE FUNCTION wksp_stocktrade.retrieve_schema(
  p_question IN VARCHAR2,
  p_top_n    IN NUMBER DEFAULT 3
) RETURN CLOB IS
  l_query_vec VECTOR(384, FLOAT32);
  l_result    CLOB := '';
BEGIN
  SELECT VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2 USING p_question AS data)
  INTO   l_query_vec FROM dual;

  FOR r IN (
    SELECT object_name, description, usage_notes, do_not_use_for,
           VECTOR_DISTANCE(embedding, l_query_vec, COSINE) AS dist
    FROM   wksp_stocktrade.ai_object_catalog
    WHERE  embedding IS NOT NULL
    ORDER  BY dist
    FETCH FIRST p_top_n ROWS ONLY
  ) LOOP
    l_result := l_result ||
      'TABLE/VIEW: '  || r.object_name   || CHR(10) ||
      'Description: ' || r.description   || CHR(10) ||
      'Usage: '       || NVL(r.usage_notes,'') || CHR(10) ||
      CASE WHEN r.do_not_use_for IS NOT NULL
           THEN 'Do NOT use for: '||r.do_not_use_for||CHR(10) ELSE '' END
           || CHR(10) || '---' || CHR(10);
  END LOOP;
  RETURN l_result;
END retrieve_schema;
/

-- Glossary retrieval: finds relevant term definitions
CREATE OR REPLACE FUNCTION wksp_stocktrade.retrieve_glossary(
  p_question IN VARCHAR2,
  p_top_n    IN NUMBER DEFAULT 2
) RETURN CLOB IS ...

-- Few-shot retrieval: finds similar example SQL patterns
CREATE OR REPLACE FUNCTION wksp_stocktrade.retrieve_few_shot(
  p_question IN VARCHAR2,
  p_top_n    IN NUMBER DEFAULT 3
) RETURN CLOB IS ...

Schema retrieval recall@1 results:

Question	Top retrieved view	Correct?
“5 year CAGR of TCS”	AI_STOCK_CAGR (dist 0.41)	✅
“flexi cap lowest volatility”	MF_SCHEME_STATS (dist 0.45)	✅
“Infosys bonus”	AI_NSE_CORPORATE_ACTION	✅
“how many eligible flexi cap funds”	AI_MF_SCHEME (dist 0.32)	✅

The Q8 fix is the most important — the catalog description “Use for counting and filtering schemes” pulled AI_MF_SCHEME to rank above AI_MF_CAGR for counting questions. Without this, the model would query CAGR for a COUNT question and return wrong results.

---

Week 8: The Generation Pipeline — From Question to Answer

Week 7 built the retrieval layer. Week 8 wires it into a complete pipeline that generates SQL, validates it, executes it, and returns results.

The complete pipeline package

CREATE OR REPLACE PACKAGE wksp_stocktrade.ai_query_pkg AS
  FUNCTION ask(
    p_question IN VARCHAR2,
    p_username IN VARCHAR2 DEFAULT 'ADMIN',
    p_profile  IN VARCHAR2 DEFAULT 'PERFIN_GENAI',
    p_narrate  IN BOOLEAN  DEFAULT TRUE   -- Week 10 addition
  ) RETURN CLOB;

  FUNCTION show_sql(
    p_question IN VARCHAR2,
    p_profile  IN VARCHAR2 DEFAULT 'PERFIN_GENAI'
  ) RETURN CLOB;

  FUNCTION generate_and_validate(
    p_question IN VARCHAR2,
    p_profile  IN VARCHAR2,
    p_log_id   IN NUMBER
  ) RETURN CLOB;
END ai_query_pkg;
/

Step 1: Intent classification

CREATE OR REPLACE FUNCTION wksp_stocktrade.classify_intent(
  p_question IN VARCHAR2
) RETURN VARCHAR2 IS
  l_q VARCHAR2(4000) := UPPER(TRIM(p_question));
BEGIN
  -- SIMILARITY: fund/stock comparison requests
  IF REGEXP_LIKE(l_q,
     'SIMILAR (TO|FUNDS|STOCKS)|LIKE (THIS|THAT) FUND|'||
     'FUNDS LIKE|PEERS OF|COMPARABLE TO', 'i')
  THEN RETURN 'SIMILARITY'; END IF;

  -- CHAT: general knowledge — no data needed
  IF REGEXP_LIKE(l_q,
     '^WHAT IS (A|AN|THE MEANING|THE DIFFERENCE)|'||
     '^HOW DOES|^EXPLAIN|^DEFINE|'||
     'WHAT IS (CAGR|NAV|SIP|ELSS|NPS|A MUTUAL FUND|AN ETF|'||
     'A FLEXI CAP|A LARGE CAP|EXPENSE RATIO|ALPHA|BETA)', 'i')
  THEN RETURN 'CHAT'; END IF;

  -- SQL: anything with specific entity + metric
  IF REGEXP_LIKE(l_q,
     'PRICE|CAGR|RETURN|NAV|DRAWDOWN|VOLATILITY|SHARPE|'||
     'BONUS|SPLIT|DIVIDEND|52.WEEK|HOW MANY|COUNT|'||
     'BEST|WORST|TOP|LOWEST|HIGHEST|COMPARE|VERSUS|VS', 'i')
  THEN RETURN 'SQL'; END IF;

  RETURN 'SQL';  -- default: try SQL
END classify_intent;
/

Step 2: Prompt assembly with RAG context

-- Inside generate_and_validate:

-- Retrieve context
l_schema_ctx := wksp_stocktrade.retrieve_schema(p_question, 3);
l_gloss_ctx  := wksp_stocktrade.retrieve_glossary(p_question, 2);
l_shot_ctx   := wksp_stocktrade.retrieve_few_shot(p_question, 3);

-- Resolve entities
l_res_stock := wksp_stocktrade.resolve_entity(p_question, 'NSE_STOCK');
l_res_fund  := wksp_stocktrade.resolve_entity(p_question, 'MF_SCHEME');

-- Assemble prompt
l_prompt :=
  '## Database Schema Context' || CHR(10) ||
  'You are generating Oracle SQL for a personal finance portfolio database.' || CHR(10) ||
  'Database: Oracle 26ai. Schema: WKSP_STOCKTRADE.' || CHR(10) ||
  'Rules:' || CHR(10) ||
  '- Generate only SELECT statements.' || CHR(10) ||
  '- Never compute CAGR from raw prices. Use AI_STOCK_CAGR or AI_MF_CAGR.' || CHR(10) ||
  '- Never compute Sharpe ratio. Read sharpe_ratio column from MF_SCHEME_STATS.' || CHR(10) ||
  '- Use exact category values: EQ_LARGE_CAP EQ_FLEXI_CAP EQ_MID_CAP etc.' || CHR(10) ||
  '- Use NSE ticker not company name: HDFCBANK not HDFC Bank.' || CHR(10) ||
  '- Always filter years explicitly: AND years = 5' || CHR(10) ||
  '- AI_STOCK_DRAWDOWN only has: symbol, years, max_drawdown_pct, volatility_ann_pct, trading_days.' || CHR(10) ||
  '- Always include days_held in CAGR queries.' || CHR(10) ||
  CHR(10);

IF l_entity_res IS NOT NULL THEN
  l_prompt := l_prompt ||
    '## Resolved Entities' || CHR(10) || l_entity_res || CHR(10);
END IF;

l_prompt := l_prompt ||
  '## Relevant Tables and Views' || CHR(10) || l_schema_ctx || CHR(10) ||
  '## Term Definitions' || CHR(10) || l_gloss_ctx || CHR(10) ||
  '## Example SQL Patterns' || CHR(10) || l_shot_ctx || CHR(10) ||
  '## Question' || CHR(10) ||
  'Generate Oracle SQL to answer: ' || p_question || CHR(10) ||
  'Return ONLY the SQL. No markdown. No semicolon at end. Start with SELECT or WITH.';

Critical lesson: action=’chat’ not action=’prompt’

The Gemini provider via DBMS_CLOUD_AI does not support action='prompt'. This caused every SQL generation call to fail with ORA-20000.

-- WRONG — not supported by Gemini provider:
SELECT DBMS_CLOUD_AI.GENERATE(
    prompt => l_prompt, profile_name => p_profile, action => 'prompt')
INTO l_gen_sql FROM dual;

-- CORRECT — use 'chat' for free-form prompt generation:
SELECT DBMS_CLOUD_AI.GENERATE(
    prompt => l_prompt, profile_name => p_profile, action => 'chat')
INTO l_gen_sql FROM dual;

The valid actions for Gemini are: showsql, runsql, narrate, chat. Not prompt.

Step 3: Clean LLM output

-- LLM sometimes wraps output in markdown fences — strip them
l_gen_sql := REGEXP_REPLACE(l_gen_sql, '```sql\s*', '', 1, 0, 'i');
l_gen_sql := REGEXP_REPLACE(l_gen_sql, '```\s*',    '', 1, 0, 'i');
l_gen_sql := TRIM(l_gen_sql);

-- Remove trailing semicolon
IF SUBSTR(TRIM(l_gen_sql), -1) = ';' THEN
  l_gen_sql := SUBSTR(TRIM(l_gen_sql), 1, LENGTH(TRIM(l_gen_sql))-1);
END IF;

-- If LLM added explanation before the SQL, extract from first SELECT/WITH
IF NOT REGEXP_LIKE(TRIM(l_gen_sql), '^(SELECT|WITH)\s', 'i') THEN
  DECLARE
    l_pos_sel NUMBER := INSTR(UPPER(l_gen_sql), 'SELECT');
    l_pos_wit NUMBER := INSTR(UPPER(l_gen_sql), 'WITH');
    l_pos     NUMBER;
  BEGIN
    l_pos := CASE
      WHEN l_pos_sel=0 AND l_pos_wit=0 THEN 0
      WHEN l_pos_sel=0 THEN l_pos_wit
      WHEN l_pos_wit=0 THEN l_pos_sel
      ELSE LEAST(l_pos_sel, l_pos_wit) END;
    IF l_pos > 1 THEN l_gen_sql := SUBSTR(l_gen_sql, l_pos); END IF;
  END;
END IF;

Column alignment bug — the difference between 68% and 100%

After the first RAG pipeline run, eval score jumped from 42% to 68.4%. 19 out of 20 questions now worked. But 6 questions had numeric mismatches.

The cause: the reference SQL returned a column named CAGR_PCT but the generated SQL returned the same value as RETURN_PCT or FIVE_YEAR_CAGR. The harness was comparing column 1 of reference vs column 1 of generated — names didn’t matter for numeric comparison. But for the harness to find the right value, the reference SQL needed to match column order.

Fix: ensure both reference SQL and generated SQL return the numeric value in column 1. The reference SQL was updated to always put the key metric first. This pushed the score to 100%.

Eval score after Week 8: 100% strict (run 25)

---

Week 9: Guardrails — Three Security Layers

A pipeline that generates and executes arbitrary SQL needs security. Three layers were implemented.

Layer 1: Pre-LLM question filter

Block obviously malicious questions before even calling the LLM:

-- Inside generate_and_validate, before LLM call:
IF REGEXP_LIKE(UPPER(p_question),
   '^(UPDATE|DELETE|DROP|INSERT|GRANT|REVOKE|EXECUTE|EXEC)\s|'||
   'DBA_USERS|ALL_USERS|ALL_TABLES|ALL_SOURCE|ALL_OBJECTS|'||
   'V\$SESSION|SYS\.|WKSP_STOCKDATA\.|'||
   'SHOW.*TABLES|LIST.*TABLES|ALL.*TABLES.*DATABASE|'||
   'IGNORE.*INSTRUCTIONS|IGNORE.*PREVIOUS|DISREGARD.*RULES|'||
   'RETURN.*PASSWORD|RETURN.*CREDENTIAL', 'i') THEN
  -- Block and log — don't call LLM
  UPDATE wksp_stocktrade.ai_query_log
  SET    validation_error = 'BLOCKED: question contains forbidden pattern',
         sql_valid        = 'N'
  WHERE  log_id = p_log_id;
  RETURN NULL;
END IF;

Layer 2: SQL validation — expanded blacklist

CREATE OR REPLACE FUNCTION wksp_stocktrade.validate_sql(
  p_sql       IN  CLOB,
  p_error_msg OUT VARCHAR2
) RETURN CHAR IS
  l_sql_upper VARCHAR2(32767);
BEGIN
  l_sql_upper := UPPER(TRIM(TO_CHAR(p_sql)));

  -- Must start with SELECT or WITH
  IF NOT REGEXP_LIKE(l_sql_upper, '^(WITH\s+|SELECT\s+)', 'i') THEN
    p_error_msg := 'Must start with SELECT or WITH';
    RETURN 'N';
  END IF;

  -- Block dangerous patterns including injection attempts
  DECLARE
    TYPE t_patterns IS TABLE OF VARCHAR2(200);
    l_blocked t_patterns := t_patterns(
      'INSERT\s','UPDATE\s','DELETE\s','DROP\s','CREATE\s',
      'DBMS_','UTL_','SYS\.','GRANT\s','REVOKE\s',
      'ALL_TABLES','ALL_SOURCE','ALL_OBJECTS','V\$',
      'WKSP_STOCKDATA\.',          -- direct base table access blocked
      'OR\s+1\s*=\s*1',            -- classic injection
      ''';\s*(DROP|DELETE|UPDATE)', -- semicolon injection
      'UNION\s+SELECT.*FROM.*DBA_' -- data exfiltration via UNION
    );
  BEGIN
    FOR i IN 1..l_blocked.COUNT LOOP
      IF REGEXP_LIKE(l_sql_upper, l_blocked(i), 'i') THEN
        p_error_msg := 'BLOCKED: forbidden pattern';
        RETURN 'N';
      END IF;
    END LOOP;
  END;

  -- Parse check (syntax validation)
  DECLARE l_cursor INTEGER;
  BEGIN
    l_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(l_cursor, p_sql, DBMS_SQL.NATIVE);
    DBMS_SQL.CLOSE_CURSOR(l_cursor);
  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(l_cursor) THEN DBMS_SQL.CLOSE_CURSOR(l_cursor); END IF;
    p_error_msg := 'Syntax error: '||SUBSTR(SQLERRM,1,200);
    RETURN 'N';
  END;

  RETURN 'Y';
END validate_sql;
/

Layer 3: Read-only execution user

All generated SQL is executed through a separate database user with SELECT-only permissions on the AI views. Even if a SQL injection attack bypasses the validator, the execution user cannot modify data.

-- Run as ADMIN (pending execution):
CREATE USER ai_exec IDENTIFIED BY <password> ACCOUNT LOCK;
GRANT CREATE SESSION TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_stock_snapshot      TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_stock_cagr           TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_mf_cagr              TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_mf_snapshot          TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_nse_price_daily      TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_mf_nav_daily         TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_nse_corporate_action TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_mf_scheme            TO ai_exec;
GRANT SELECT ON wksp_stocktrade.ai_stock_drawdown       TO ai_exec;
GRANT SELECT ON wksp_stocktrade.mf_scheme_stats         TO ai_exec;

-- exec_as_ai_exec: second whitelist check before execution
PROCEDURE exec_as_ai_exec(
  p_sql    IN  CLOB,
  p_result OUT CLOB,
  p_rows   OUT NUMBER
) IS
BEGIN
  -- Second object whitelist check — defence in depth
  IF REGEXP_LIKE(UPPER(p_sql),
     'WKSP_STOCKDATA\.|SYS\.|DBA_|ALL_USERS|V\$|'||
     'ALL_SOURCE|USER_SOURCE|TAB\b|DICT\b', 'i') THEN
    RAISE_APPLICATION_ERROR(-20099,
      'BLOCKED: SQL references forbidden schema or system object');
  END IF;

  -- Execute via DBMS_SQL (controlled execution)
  -- ... fetch results and return ...
END exec_as_ai_exec;

Adversarial test results (run 47): 9/9 attacks blocked, 100%

Attacks tested:

• DROP TABLE via question text
• SQL injection via semicolon ('; DROP TABLE ai_eval_question; --)
• System table access (SELECT * FROM all_users)
• Source code exfiltration (SELECT text FROM all_source)
• DBA_USERS access
• Prompt injection (“Ignore previous instructions and…”)
• Schema reconnaissance (“What tables exist in WKSP_STOCKDATA?”)
• UNION-based data extraction
• Cross-schema query attempt

All blocked at Layer 1 (question filter) or Layer 2 (SQL validator) before execution.

Functional regression (run 43): 19/19 correct, 100% — guardrails didn’t break anything.

---

Week 10: Narration Layer — From Raw Tables to Plain English

The pipeline now returns correct SQL results. But raw tabular output is unfriendly:

CAGR_PCT | DAYS_HELD
-----------
-7.47    | 1825
1 row(s) returned.

Week 10 adds three functions that convert this into human language.

Problem 1: Partial data coverage — silently wrong

A new stock listed 8 months ago. The user asks for its “5-year CAGR”. The query runs, returns a number, and displays it as if it’s a 5-year CAGR. It’s actually an 8-month return. No error, no warning. This is dangerous.

-- PRIVATE: CHECK_DATA_COVERAGE
FUNCTION check_data_coverage(
  p_sql      IN CLOB,
  p_result   IN CLOB,
  p_question IN VARCHAR2
) RETURN VARCHAR2 IS
  l_years_requested NUMBER := 0;
  l_min_days        NUMBER := 0;
BEGIN
  -- Only check CAGR queries
  IF NOT REGEXP_LIKE(UPPER(p_sql), 'AI_STOCK_CAGR|AI_MF_CAGR', 'i') THEN
    RETURN NULL;
  END IF;

  -- Determine requested horizon
  IF    REGEXP_LIKE(p_question, '5.?YEAR|5YR', 'i') THEN
    l_years_requested := 5;  l_min_days := 1500;
  ELSIF REGEXP_LIKE(p_question, '3.?YEAR|3YR', 'i') THEN
    l_years_requested := 3;  l_min_days := 900;
  ELSIF REGEXP_LIKE(p_question, '1.?YEAR|1YR', 'i') THEN
    l_years_requested := 1;  l_min_days := 200;
  ELSE RETURN NULL;
  END IF;

  -- Check days_held in result — if below threshold, warn
  DECLARE l_days_match VARCHAR2(100);
  BEGIN
    l_days_match := REGEXP_SUBSTR(p_result, '\b([0-9]{2,4})\b', 1, 3);
    IF l_days_match IS NOT NULL AND TO_NUMBER(l_days_match) < l_min_days THEN
      RETURN 'Note: This '||l_years_requested||'-year metric is based on '||
             ROUND(TO_NUMBER(l_days_match)/365, 1)||
             ' years of actual data. Results may not reflect the full period.';
    END IF;
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  RETURN NULL;
END check_data_coverage;

Problem 2: Empty results — no explanation

When SQL returns 0 rows, the user currently sees “0 row(s) returned.” They don’t know if the stock doesn’t exist, lacks history, or was entered incorrectly.

-- PRIVATE: CLASSIFY_EMPTY_RESULT
FUNCTION classify_empty_result(
  p_sql      IN CLOB,
  p_question IN VARCHAR2,
  p_profile  IN VARCHAR2
) RETURN VARCHAR2 IS
  l_symbol VARCHAR2(100);
  l_count  NUMBER := 0;
BEGIN
  -- Extract symbol from generated SQL
  l_symbol := REGEXP_SUBSTR(
    UPPER(p_sql), 'SYMBOL\s*=\s*''([A-Z0-9&]+)''', 1, 1, 'i', 1);

  IF l_symbol IS NOT NULL THEN
    -- Check if symbol exists at all
    SELECT COUNT(*) INTO l_count
    FROM   wksp_stockdata.nse_pd_bhavcopy_history
    WHERE  symbol = l_symbol AND series = 'EQ' AND ROWNUM = 1;

    IF l_count = 0 THEN
      RETURN 'The stock "'||l_symbol||'" was not found in NSE data. '||
             'Possible reasons: '||
             '(1) Listed on BSE only — try BSE data; '||
             '(2) Recently listed — may lack history; '||
             '(3) Ticker may differ — verify the exact NSE symbol; '||
             '(4) Delisted stock — no longer traded.';
    ELSE
      RETURN '"'||l_symbol||'" exists in NSE data but has no result '||
             'for the requested metric or time period.';
    END IF;
  ELSE
    RETURN 'No data found. Try rephrasing with the exact NSE symbol '||
           '(e.g. HDFCBANK, TCS, INFY) or exact fund name from AMFI records.';
  END IF;
END classify_empty_result;

Problem 3: Raw tables are unfriendly — narrate them

-- PRIVATE: NARRATE_RESULT
-- Second LLM call: converts SQL table to plain English paragraph
FUNCTION narrate_result(
  p_question       IN VARCHAR2,
  p_sql_result     IN CLOB,
  p_coverage_warn  IN VARCHAR2,
  p_profile        IN VARCHAR2
) RETURN CLOB IS
  l_prompt    CLOB;
  l_narration CLOB;
BEGIN
  l_prompt :=
    'You are a financial assistant explaining data to a retail investor in India.' || CHR(10) ||
    'Convert the following query result into a clear, plain English answer.' || CHR(10) ||
    'Rules:' || CHR(10) ||
    '- Write 2-4 sentences maximum.' || CHR(10) ||
    '- Use simple language. No financial jargon.' || CHR(10) ||
    '- Always include the actual numbers from the data.' || CHR(10) ||
    '- Use Indian number format: ₹ for rupees, % for percentages.' || CHR(10) ||
    '- If CAGR is negative, clearly state it is a loss.' || CHR(10) ||
    '- Do not give buy/sell recommendations.' || CHR(10) ||
    CHR(10) ||
    'User question: ' || p_question || CHR(10) ||
    'Data:' || CHR(10) || SUBSTR(p_sql_result, 1, 3000) || CHR(10) ||
    CASE WHEN p_coverage_warn IS NOT NULL
         THEN 'Context note: ' || p_coverage_warn || CHR(10) ELSE '' END ||
    'Plain English answer:';

  SELECT DBMS_CLOUD_AI.GENERATE(
             prompt => l_prompt, profile_name => p_profile, action => 'chat')
  INTO   l_narration FROM dual;

  -- Append coverage warning below narration
  IF p_coverage_warn IS NOT NULL THEN
    l_narration := l_narration || CHR(10) || CHR(10) || 'Note: ' || p_coverage_warn;
  END IF;

  RETURN l_narration;
EXCEPTION WHEN OTHERS THEN
  RETURN p_sql_result;  -- narration failed — return raw result unchanged
END narrate_result;

The narration toggle — p_narrate parameter

The eval harness needs raw tabular output to score correctly. The APEX page needs plain English for users. One parameter handles both:

FUNCTION ask(
  p_question IN VARCHAR2,
  p_username IN VARCHAR2 DEFAULT 'ADMIN',
  p_profile  IN VARCHAR2 DEFAULT 'PERFIN_GENAI',
  p_narrate  IN BOOLEAN  DEFAULT TRUE   -- TRUE for APEX, FALSE for eval harness
) RETURN CLOB IS

Inside ask(), after execution:

IF p_narrate THEN
  IF l_rows = 0 THEN
    -- Explain WHY no data found
    l_result := classify_empty_result(l_gen_sql, p_question, p_profile);
  ELSE
    -- Narrate the data
    l_coverage_warn := check_data_coverage(l_gen_sql, l_raw_result, p_question);
    l_narrated := narrate_result(p_question, l_raw_result, l_coverage_warn, p_profile);
    l_result := l_narrated || CHR(10) || CHR(10) ||
                '--- Data ---' || CHR(10) || l_raw_result;
  END IF;
ELSE
  -- p_narrate=FALSE: raw result for eval harness
  l_result := l_raw_result;
END IF;

Smoke test results — all three narration functions working

Test A — TCS 5yr CAGR (narrated):
"Over the last 5 years, TCS has seen a decline in value, resulting in a loss
of 7.47% per year. This figure is based on a holding period of 1,825 days."
--- Data ---
CAGR_PCT | DAYS_HELD
-7.47    | 1825

Test B — Fake stock:
"The stock 'XYZFAKESTOCK' was not found in NSE data. Possible reasons:
(1) Listed on BSE only — try BSE data; (2) Recently listed — may lack
history; (3) Ticker may differ — verify the exact NSE symbol;
(4) Delisted stock — no longer traded."

Test C — HDFC Bank price:
"As of 26-08-13, the price of HDFC Bank was ₹725."
--- Data ---
CURRENT_PRICE | PRICE_DATE
725           | 26-08-13

Functional regression (run 61): 19/19 correct, 100% — narration didn’t break anything.

---

The Complete Eval Score History

Run	Week	Description	Strict	Partial
3	6	Baseline — raw Select AI, schema metadata only	42.1%	50.0%
22	8	RAG pipeline first run	68.4%	76.3%
25	8	Column alignment fixed	100%	100%
43	9	Functional regression after guardrails	100%	100%
47	9	Adversarial security test — 9 attacks	100% blocked	—
61	10	Narration layer	100%	100%

The journey from 42.1% to 100% strict accuracy took four weeks of targeted improvements, each measured against the eval harness:

Week 6 → 7: RAG retrieval fixed entity resolution and category literals (+26pp)
Week 7 → 8: Few-shot examples fixed recompute and join failures, column alignment fixed remaining gaps (+31.6pp)
Week 8 → 9: Guardrails added without regression (0pp change — correct)
Week 9 → 10: Narration added without regression (0pp change — correct)

---

Key Lessons Learned

1. Measure first, build second. The eval harness (Week 6) was the most important artifact in the project. Without it, improvements are guesses. With it, each week’s work produces a number.

2. The LLM’s SQL structure is usually correct. The failures are semantic. Gemini consistently generated syntactically valid SQL. The 42% baseline failures were wrong literals ('FLEXI CAP' instead of 'EQ_FLEXI_CAP'), wrong entity keys (company name instead of ticker), and wrong view choices. RAG context fixed all three without changing the model.

3. action='chat' not action='prompt' for Gemini. The Gemini provider via DBMS_CLOUD_AI only supports showsql, runsql, narrate, and chat. Using action='prompt' causes ORA-20000. Use chat for free-form prompt generation.

4. ORA-14551 — DML inside function — PL/SQL functions called from SQL cannot commit. Move DML calls (INSERT into log, UPDATE) to a PL/SQL block, not a SELECT FROM DUAL.

5. as_of_date = MAX(as_of_date) not TRUNC(SYSDATE). MF_SCHEME_STATS is populated nightly. On weekends and public holidays, TRUNC(SYSDATE) returns no rows because there’s no entry for today. Use MAX(as_of_date) to always get the latest available data.

6. The train/serve skew trap for embeddings. When you embed the user’s question to retrieve similar few-shot examples, you must use the exact same embedding model that was used to embed the examples. In Oracle 26ai this is guaranteed because VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING ... AS data) uses the same model both ways. But if you ever change the model, re-embed everything.

7. Security in depth — three layers, not one. Question filter + SQL validator + read-only execution user. Any single layer can be defeated. All three together are very difficult to bypass.

8. SET DEFINE OFF at the top of any SQL script containing & characters. Oracle SQL*Plus and Database Actions treat & as a substitution variable prefix. Fund names, ticker symbols, and SQL BETWEEN clauses all contain & and will fail without this.

9. DISTINCT with VECTOR columns causes ORA-22848. If your result set includes a VECTOR column and you need to deduplicate, deduplicate on non-vector columns in a subquery first, then join back to get the vector.

10. The narration layer doubles the LLM cost. Every user query now makes two LLM calls — one for SQL generation, one for narration. For an Always Free account with rate limits, this matters. The p_narrate=FALSE toggle on the eval harness ensures the harness doesn’t consume double credits.

---

What Comes Next — Weeks 11-12

Week 11 — APEX pages:

• Natural language query page (text input → narrated answer + raw data)
• Fund similarity page (enter a fund → find similar funds by feature vector distance)
• Bucket decision dashboard (which bucket to withdraw from this month)

Week 12 — Stock feature vectors + final tuning:

• Stock feature vectors (CAGR, volatility, Sharpe, drawdown, dividend yield) stored as VECTOR(8, FLOAT32)
• True bucket sequencing in Monte Carlo (draw from BAF/gold when equity down, equity when up)
• Additional few-shot examples for question types that still fail
• Final eval run on expanded 30-question set

---

Built on Oracle APEX 24.2 + Oracle AI Database 26ai (Always Free, ca-toronto-1). LLM: Google Gemini 1.5 Flash via aistudio.google.com API (free tier). Embedding model: all-MiniLM-L12-v2 (ONNX) loaded directly into Oracle. All vector operations run inside the database — zero external API calls for similarity search or entity resolution.

Published on gradeupnow.in

Tags: Oracle APEX, Oracle AI Database, Select AI, RAG, Vector Search, Oracle 26ai, Natural Language to SQL, Gemini, DBMS_CLOUD_AI, PL/SQL, Personal Finance, Oracle Database