The final two weeks of a 12-week project building a natural language portfolio query engine on Oracle APEX 24.2 + Oracle AI Database 26ai.
Weeks 1-10 recap: Built a complete NL-to-SQL pipeline on 10 years of NSE and AMFI data. Eval score: 42.1% → 100% strict accuracy. Weeks 11-12: wire it into APEX pages and add stock/fund similarity via vector search.
Week 11: The APEX Pages — Two Weeks of Debugging in One Week
Week 11 was supposed to be straightforward. The pipeline was working. The designer was fixed. Two pages to build.
It was not straightforward.
The Privilege Chain That Broke Everything
After Oracle upgraded the ADB from 19c to 26ai, three separate privilege chains broke silently:
Break 1 — APEX page designer: One invalid package (WWV_FLOW_FLOW_PROPERTY_DEV) broke the entire App Builder visual designer. Fix: grant SELECT on the new 26ai DBA_PROPERTY_GRAPHS view to the APEX schema and recompile.
Break 2 — AI pipeline ORA-01031: DBMS_CLOUD_AI is owned by C##CLOUD$SERVICE, not SYS. The grant to WKSP_STOCKTRADE needed to specify the owner explicitly. Fix: revoke the bad grant, regrant with full owner prefix.
Break 3 — ORA-06598 INHERIT PRIVILEGES: ai_query_pkg uses AUTHID CURRENT_USER (invoker rights). When APEX calls it via ORDS, the session user is ORDS_PLSQL_GATEWAY. Oracle requires explicit INHERIT PRIVILEGES grants from every calling user to the package owner.
The complete fix — run once after any 26ai upgrade:
-- Fix 1: APEX designer
GRANT SELECT ON sys.dba_property_graphs TO apex_240200;
BEGIN DBMS_UTILITY.COMPILE_SCHEMA('APEX_240200', FALSE, TRUE); END;
/
-- Fix 2: AI pipeline access
REVOKE EXECUTE ON c##cloud$service.dbms_cloud_ai FROM wksp_stocktrade;
GRANT EXECUTE ON c##cloud$service.dbms_cloud_ai TO wksp_stocktrade;
GRANT EXECUTE ON c##cloud$service.dbms_cloud TO wksp_stocktrade;
-- Fix 3: INHERIT PRIVILEGES for every calling user
GRANT INHERIT PRIVILEGES ON USER ADMIN TO wksp_stocktrade;
GRANT INHERIT PRIVILEGES ON USER WKSP_STOCKTRADE TO wksp_stocktrade;
GRANT INHERIT PRIVILEGES ON USER ORDS_PLSQL_GATEWAY TO wksp_stocktrade;
-- Fix 4: Recompile pipeline
BEGIN DBMS_UTILITY.COMPILE_SCHEMA('WKSP_STOCKTRADE', FALSE, TRUE); END;
/
One more code change: Replace all DBMS_CLOUD_AI.GENERATE calls in ai_query_pkg with a thin AUTHID CURRENT_USER wrapper function. This ensures the privilege check runs against the calling user (who has EXECUTE WITH GRANT OPTION) rather than the package owner.
-- Create wrapper with AUTHID CURRENT_USER
CREATE OR REPLACE FUNCTION wksp_stocktrade.ai_generate(
p_prompt IN CLOB,
p_profile_name IN VARCHAR2,
p_action IN VARCHAR2 DEFAULT 'chat'
) RETURN CLOB
AUTHID CURRENT_USER IS
BEGIN
RETURN c##cloud$service.dbms_cloud_ai.generate(
prompt => p_prompt,
profile_name => p_profile_name,
action => p_action
);
END ai_generate;
/
GRANT EXECUTE ON wksp_stocktrade.ai_generate TO PUBLIC;
The diagnostic that identified the ORDS session user — add this temporarily to any APEX process when debugging privilege issues:
SELECT SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user
FROM dual;
-- Result: ORDS_PLSQL_GATEWAY / WKSP_STOCKTRADE
That single query identified the missing INHERIT PRIVILEGES grant target. Before finding it, the error was generic ORA-06598 with no indication of which user was the problem.
Page 25: Ask Your Portfolio
The natural language query page. Type a question, get a plain English answer plus the raw data and generated SQL.
Architecture:
User types question → P25_QUESTION item
→ ASK button → Submit Page
→ PL/SQL process calls ai_query_pkg.ask()
→ Result split into narration + raw data
→ P25_ANSWER shows plain English
→ P25_SQL shows generated SQL
→ Query History report shows last 10 queries
The PL/SQL process:
DECLARE
l_result CLOB;
BEGIN
l_result := wksp_stocktrade.ai_query_pkg.ask(
p_question => :P25_QUESTION,
p_username => :APP_USER,
p_narrate => TRUE
);
-- Split narration from raw data
IF INSTR(l_result, '--- Data ---') > 0 THEN
:P25_ANSWER := SUBSTR(l_result, 1,
INSTR(l_result, '--- Data ---') - 1);
:P25_SQL := SUBSTR(l_result,
INSTR(l_result, '--- Data ---') + 12);
ELSE
:P25_ANSWER := l_result;
:P25_SQL := NULL;
END IF;
END;
Query History report SQL:
SELECT asked_on,
SUBSTR(question_raw, 1, 80) question,
intent,
result_rows,
elapsed_ms,
SUBSTR(result_preview, 1, 100) result_preview
FROM wksp_stocktrade.ai_query_log
WHERE username = :APP_USER
ORDER BY log_id DESC
FETCH FIRST 10 ROWS ONLY
Live test results from the page:
Question: "what is the current price of HDFC Bank"
Answer: "As of 14-AUG-26, the current price of HDFC Bank is ₹727."
Question: "which stock gave the best return in last 1 year"
Answer: "Over the last year, UFBL provided the highest return with
a growth of 692.25%. This performance is based on a period
of 264 trading days."
Question: "what is the current price of XYZFAKE"
Answer: "The stock 'XYZFAKE' was not found in NSE data. Possible
reasons: (1) Listed on BSE only; (2) Recently listed;
(3) Ticker may differ; (4) Delisted stock."
Page 26: Fund Similarity
The vector similarity page. Type a fund name, get the top 10 most similar funds ranked by quantitative distance.
Architecture:
User types fund name → P26_FUND_NAME
→ Category filter → P26_CATEGORY (EQ_FLEXI_CAP, EQ_LARGE_CAP etc.)
→ FIND SIMILAR FUNDS button → Submit Page
→ PL/SQL process:
resolve_entity() → scheme_code
Load reference fund stats → P26_REF_STATS
→ Classic Report runs VECTOR_DISTANCE query
→ Shows top 10 similar funds with stats
The entity resolver — the magic behind the name lookup:
The user types “Parag Parikh Flexi Cap” — a partial, informal name. The resolve_entity() function embeds this text using all-MiniLM-L12-v2 (ONNX model loaded into Oracle 26ai) and finds the closest match in the entity store using cosine distance.
-- resolve_entity finds the closest matching fund
l_entity := wksp_stocktrade.resolve_entity(
p_text => :P26_FUND_NAME,
p_entity_type => 'MF_SCHEME'
);
-- Returns: 'MF_SCHEME:122639:Parag Parikh Flexi Cap Fund...'
-- Extract scheme_code
:P26_SCHEME_CODE := REGEXP_SUBSTR(l_entity, '[^:]+', 1, 2);
-- Returns: '122639'
The similarity query:
SELECT
c.scheme_name,
c.amc_name,
c.category,
ROUND(s.cagr_5y, 2) cagr_5yr,
ROUND(s.volatility_ann, 2) volatility,
ROUND(s.sharpe_ratio, 3) sharpe,
ROUND(s.max_drawdown_pct, 2) max_drawdown,
ROUND(VECTOR_DISTANCE(
f.feature_vec,
(SELECT feature_vec
FROM wksp_stocktrade.mf_scheme_feature
WHERE scheme_code = TO_NUMBER(:P26_SCHEME_CODE)
AND as_of_date = (SELECT MAX(as_of_date)
FROM wksp_stocktrade.mf_scheme_feature)),
EUCLIDEAN), 4) similarity_distance
FROM wksp_stocktrade.mf_scheme_feature f
JOIN wksp_stocktrade.ai_mf_canonical c ON c.scheme_code = f.scheme_code
JOIN wksp_stocktrade.mf_scheme_stats s
ON s.scheme_code = f.scheme_code
AND s.as_of_date = (SELECT MAX(as_of_date)
FROM wksp_stocktrade.mf_scheme_stats)
WHERE f.scheme_code != TO_NUMBER(:P26_SCHEME_CODE)
AND f.as_of_date = (SELECT MAX(as_of_date)
FROM wksp_stocktrade.mf_scheme_feature)
AND c.category LIKE :P26_CATEGORY
ORDER BY similarity_distance
FETCH FIRST 10 ROWS ONLY
Live test — Parag Parikh Flexi Cap, Flexi Cap category:
| Fund | CAGR 5yr | Vol | Sharpe | Drawdown | Distance |
|---|---|---|---|---|---|
| Franklin India Flexi Cap — Direct | 14.08% | 13.89% | 0.546 | -17.65% | 0.938 |
| Aditya Birla Flexi Cap — Direct | 13.69% | 13.80% | 0.521 | -20.04% | 1.150 |
| Edelweiss Flexi Cap — Direct | 14.72% | 14.53% | 0.566 | -19.03% | 1.234 |
| ICICI Prudential Flexicap — Direct | 17.02% | 13.63% | 0.772 | -19.71% | 1.378 |
Franklin India Flexi Cap is the closest peer — similar CAGR, similar conservative drawdown profile. ICICI Prudential has higher returns but higher drawdown, ranking it further away. The vector distance correctly captures the risk-return profile similarity, not just return ranking.
One interesting finding: Parag Parikh Flexi Cap is an outlier within its own category. Its volatility (11.67%) is significantly lower than all its flexi cap peers (13-14.5%). This reflects its international diversification — the fund holds global stocks like Alphabet and Meta which have different volatility patterns from pure Indian equity. The similarity search correctly shows that its true peers (by risk-return profile) span multiple categories — large-cap and multicap funds match it better than most flexi-cap funds.
Week 12: Stock Vectors + Weight Tuning + Final Eval
12A: NSE Stock Feature Vectors
1,078 NSE EQ stocks vectorised using 7 financial features:
| Feature | Weight | Rationale |
|---|---|---|
| CAGR 1yr | 1.5 | Short-term momentum |
| CAGR 3yr | 1.5 | Medium-term trend |
| CAGR 5yr | 2.0 | Primary return signal |
| Volatility 5yr | 1.5 | Long-term risk |
| Max Drawdown 5yr | 2.0 | Tail risk — key differentiator |
| Volatility 1yr | 1.0 | Recent risk |
| Max Drawdown 1yr | 1.0 | Recent tail risk |
Features are z-score standardised and weighted at storage time. The weights are applied during vector construction — not at query time — so VECTOR_DISTANCE(EUCLIDEAN) automatically respects the importance hierarchy.
Population script (condensed):
INSERT INTO wksp_stocktrade.nse_stock_feature
(symbol, as_of_date, feature_vec, cagr_1y, cagr_3y, cagr_5y,
vol_5y, dd_5y, vol_1y, dd_1y)
WITH sc AS (
-- Pivot scaling parameters into one row
SELECT
MAX(CASE WHEN feature_name='CAGR_1Y' THEN mean_val END) m1,
MAX(CASE WHEN feature_name='CAGR_1Y' THEN stddev_val END) s1,
MAX(CASE WHEN feature_name='CAGR_1Y' THEN weight END) w1,
-- ... repeat for all 7 features
FROM wksp_stocktrade.nse_stock_feature_scaling
),
zscores AS (
SELECT c5.symbol,
-- Z-score with cap at ±5 to prevent outlier dominance
GREATEST(LEAST((NVL(c1.cagr_pct,0)-sc.m1)/NULLIF(sc.s1,0)*sc.w1,5),-5) z1,
GREATEST(LEAST((NVL(c3.cagr_pct,0)-sc.m2)/NULLIF(sc.s2,0)*sc.w2,5),-5) z2,
-- ... repeat for all features
FROM wksp_stocktrade.ai_stock_cagr c5
JOIN wksp_stocktrade.ai_stock_cagr c1 ON c1.symbol=c5.symbol AND c1.years=1
JOIN wksp_stocktrade.ai_stock_cagr c3 ON c3.symbol=c5.symbol AND c3.years=3
JOIN wksp_stocktrade.ai_stock_drawdown d5 ON d5.symbol=c5.symbol AND d5.years=5
CROSS JOIN sc
WHERE c5.years=5 AND d5.trading_days>=1000
AND c5.symbol NOT LIKE '%ETF%'
)
SELECT symbol, TRUNC(SYSDATE),
cagr_1y, cagr_3y, cagr_5y, vol_5y, dd_5y, vol_1y, dd_1y,
TO_VECTOR('['||z1||','||z2||','||z3||','||z4||','||z5||','||z6||','||z7||']',
7, FLOAT32)
FROM zscores;
-- 1,078 rows inserted
TCS similarity results:
| Stock | CAGR 5yr | Vol 5yr | Drawdown | Distance |
|---|---|---|---|---|
| PGHH | -7.94% | 21.34% | -54.17% | 0.937 |
| INFY | -7.18% | 25.96% | -50.73% | 0.945 |
| SBICARD | -8.99% | 26.25% | -50.19% | 0.963 |
INFY at position 2 is the obvious sanity check — same sector, similar correction, similar risk profile. The vector search found this without any sector metadata — purely from return and risk numbers.
12D: Fund Similarity Weight Tuning
The problem with equal weights:
The original fund feature vectors used weights of 1.0-1.5 for all 15 features. Two features were causing noise:
CALMAR_RATIO: stddev = 22.15 — at weight 1.0 it dominated the distance calculationRETURN_KURTOSIS: stddev = 137.32 — extreme outliers in a few funds overwhelmed the similarity scoreOBS_COUNT: not a financial metric — should not influence similarity at all
Final tuned weights:
| Feature | Old Weight | New Weight | Reason |
|---|---|---|---|
| SHARPE_RATIO | 1.2 | 3.5 | Primary differentiator — most important |
| CAGR_5Y | 1.5 | 2.0 | Core return signal |
| MAX_DRAWDOWN | 1.5 | 2.0 | Tail risk |
| SORTINO_RATIO | 1.0 | 2.0 | Downside-adjusted return |
| ROLL12_STDEV | 0.8 | 2.5 | Consistency signal |
| CALMAR_RATIO | 1.0 | 0.5 | Reduce — stddev=22.15 dominates |
| RETURN_KURTOSIS | 0.7 | 0.3 | Reduce — stddev=137.32 extreme |
| OBS_COUNT | 0.5 | 0.0 | Not a financial metric — exclude |
Impact:
- ESG fund (Sharpe=0.41) dropped out of Parag Parikh top-10 ✅
- Mahindra Aggressive Hybrid dropped out ✅
- Category filter now cleanly separates same-category peers from cross-category financial twins ✅
Key insight from weight tuning: Parag Parikh Flexi Cap is genuinely unique within the flexi-cap category. Its true financial peers — funds with similar risk-return profile — are large-cap and multicap funds, not other flexi-cap funds. The vector search discovered this from numbers alone, without any category metadata in the distance calculation.
12C: Final Evaluation
Run 81: gemini-1.5-flash-rag-w12-final
Correct: 19
Partial: 0
Wrong: 0
Errors: 0
PCT_STRICT: 100%
PCT_PARTIAL: 100%
Zero failures. Zero errors. 0 rows returned on the failure query.
Complete eval score history:
| Run | Week | Description | Strict |
|---|---|---|---|
| 3 | 6 | Baseline — schema metadata only | 42.1% |
| 22 | 8 | RAG pipeline first run | 68.4% |
| 25 | 8 | Column alignment fixed | 100% |
| 43 | 9 | Guardrails added | 100% |
| 47 | 9 | Adversarial security — 9/9 blocked | 100% |
| 61 | 10 | Narration layer | 100% |
| 81 | 12 | Final — all components | 100% |
The Complete Object Inventory — What Was Built
Schema: WKSP_STOCKTRADE
Tables (AI layer):
ai_entity— 4,126 embedded entities (stocks + funds)ai_object_catalog— 11 view descriptions with embeddingsai_glossary— 10 financial term definitions with embeddingsai_few_shot— 17 example SQL patterns with embeddingsai_query_log— complete query telemetry (300+ entries)ai_eval_question— 30 test questions (20 functional + 9 adversarial)ai_eval_run— 15 eval runsai_eval_result— per-question resultsmf_scheme_feature— 1,667 MF feature vectors VECTOR(15, FLOAT32)mf_feature_scaling— 15 feature scaling parameters with weightsnse_stock_feature— 1,078 stock feature vectors VECTOR(7, FLOAT32)nse_stock_feature_scaling— 7 feature scaling parameters with weights
Functions:
classify_intent()— routes SQL / CHAT / SIMILARITY / STOCK_SIMILARITYresolve_entity()— fuzzy name to database key via cosine distanceretrieve_schema()— top-k relevant views via semantic searchretrieve_glossary()— relevant term definitionsretrieve_few_shot()— similar example SQL patternsvalidate_sql()— 20+ security checks + syntax validationai_generate()— AUTHID CURRENT_USER wrapper for DBMS_CLOUD_AIfind_similar_stocks()— stock similarity via VECTOR_DISTANCE
Packages:
ai_query_pkg— complete NL-to-SQL pipeline (ask, show_sql, generate_and_validate, narrate_result, classify_empty_result, check_data_coverage)ai_eval_pkg— evaluation harness (start_run, run_all, finish_run)
APEX Application 107:
- Page 25: Ask Your Portfolio (NL query interface)
- Page 26: Fund Similarity (vector similarity search)
What the App Can Now Answer
From a text box, in plain English, verified against 10 years of actual market data:
"what is the current price of HDFC Bank"
→ As of 14-AUG-26, the price of HDFC Bank was ₹727.
"which flexi cap funds have the best 5 year returns"
→ Over the last five years, HDFC Flexi Cap Fund (Direct Plan)
has delivered the highest returns at 18.73%...
"what is the 5 year CAGR of TCS"
→ Over the last 5 years, TCS has seen a decline in value,
resulting in a loss of 7.47% per year based on 1,825 days.
"which stock gave the best return in last 1 year"
→ Over the last year, UFBL provided the highest return with
a growth of 692.25% based on 264 trading days.
"find funds similar to Parag Parikh Flexi Cap"
→ [Page 26 shows top 10 similar funds with distance scores]
"what is the price of XYZFAKE"
→ The stock 'XYZFAKE' was not found in NSE data.
Possible reasons: (1) BSE-only...
Key Technical Lessons — Weeks 11 and 12
1. AUTHID CURRENT_USER breaks when called through ORDS. APEX calls PL/SQL via ORDS_PLSQL_GATEWAY. This user needs INHERIT PRIVILEGES granted explicitly. The error ORA-06598 gives no indication of which user is missing — add the session context query to every debugging session.
2. After a major Oracle version upgrade, check all grants. Three separate privilege chains broke after the 26ai upgrade — APEX schema, DBMS_CLOUD_AI, and INHERIT PRIVILEGES. None showed up as errors until the affected feature was tested. A comprehensive post-upgrade grant script is essential.
3. C##CLOUD$SERVICE owns DBMS_CLOUD_AI — not SYS. Granting EXECUTE ON DBMS_CLOUD_AI works syntactically but the grant is from the wrong grantor. Must specify EXECUTE ON C##CLOUD$SERVICE.DBMS_CLOUD_AI to grant correctly. The bad grant shows in dba_tab_privs with GRANTOR = ADMIN — a red flag that it won’t work in a definer-rights context.
4. Definer-rights packages cannot use role-based grants. The EXECUTE grant on DBMS_CLOUD_AI went to WKSP_STOCKTRADE directly, which should have worked. It didn’t because inside a definer-rights package (AUTHID DEFINER), only direct object grants apply — not role-based grants. The DWROLE that originally gave WKSP_STOCKTRADE access to DBMS_CLOUD_AI was a role-based grant that the package couldn’t see. The wrapper function with AUTHID CURRENT_USER was the correct architectural fix.
5. Vector weight tuning — reduce high-stddev features aggressively. CALMAR_RATIO (stddev=22.15) and RETURN_KURTOSIS (stddev=137.32) dominated the Euclidean distance calculation at any weight above 0.3. Z-scoring doesn’t fully neutralise extreme distributions — the ±5 cap on z-scores is essential, and weight reduction for these features is necessary for meaningful similarity results.
6. TRADING_DAYS vs DAYS_HELD — always check column names. The LLM generated TRADING_DAYS which doesn’t exist in ai_stock_cagr — the column is DAYS_HELD. The few-shot examples need to use the exact column names from the actual views. When adding new few-shot examples, always verify column names first.
What Comes Next
The pipeline is production-ready. The APEX pages are live. The remaining enhancements for a production deployment:
True bucket sequencing in Monte Carlo — the retirement calculator currently uses proportional withdrawal. Implementing the real bucket draw rule (BAF first, gold in corrections, equity only in bull years) adds 3-5 percentage points to the success rate.
Stock similarity in NL pipeline — the intent classifier routes “find stocks similar to TCS” to STOCK_SIMILARITY but the ask() function doesn’t yet handle this intent. Wiring it to the nse_stock_feature vector table completes the stock similarity feature.
Liquidity filter for best-performer queries — UFBL returned 692% but trades only ₹3 crore daily. Adding a minimum volume filter prevents micro-cap outliers from dominating “best return” queries.
ORDS_PUBLIC_USER password — the VM-based ORDS install still has an invalid pool. Resolving this enables a separate ORDS deployment that can serve the app independently of the ADB’s built-in ORDS.
Built on Oracle APEX 24.2 + Oracle AI Database 26ai (Always Free, ca-toronto-1). LLM: Google Gemini 1.5 Flash via DBMS_CLOUD_AI. Embedding model: all-MiniLM-L12-v2 (ONNX) in Oracle. Vector operations: VECTOR_DISTANCE(EUCLIDEAN) on VECTOR(7,FLOAT32) and VECTOR(15,FLOAT32) columns.
Published on gradeupnow.in
Tags: Oracle APEX, Oracle 26ai, DBMS_CLOUD_AI, Vector Search, RAG, PL/SQL, Natural Language SQL, Fund Similarity, Stock Vectors, ORDS_PLSQL_GATEWAY, INHERIT PRIVILEGES, Oracle Autonomous Database