Narrative + SQL embedded inline, in the order things happened
This is a learning journal. It records what we actually built, what we discovered, why each decision was made, and what each error taught us. Every SQL block appears at the point in the story where it was written — not in a separate appendix.
Schema conventions throughout:
ADMIN— DBA user, runs DDL on other schemasWKSP_STOCKDATA— raw data layer, loaders own itWKSP_STOCKTRADE— application layer, AI objects live here
THE PROJECT IN ONE PARAGRAPH
You are a DBA with 21 years of Oracle, Postgres and Cassandra experience. You have built a personal portfolio app in Oracle APEX on ADB Always Free, covering Indian equity, mutual funds, NPS, US/Canada stocks, FDs, metals, real estate, and retirement. The app holds 10 years of daily NSE bhavcopy data and AMFI NAV history.
The goal of this 12-week project: add two AI features.
- Natural language query page — type “what is the 5-year CAGR of TCS?” and get the correct verified answer from your own database.
- Fund similarity — “find funds like Parag Parikh Flexi Cap” using quantitative risk-return feature vectors, not marketing text descriptions.
Platform: Oracle AI Database 26ai (Always Free). This release includes native VECTOR datatype, DBMS_VECTOR (in-database ONNX model inference), and Select AI — all at no additional cost.
This is architecture diagram:-
WHY YOUR OWN DATABASE IS ESSENTIAL
A large language model is a translator, not a data source.
An LLM trained on internet text knows that TCS exists. It does not know TCS’s closing price on July 15, 2023. It cannot tell you the 5-year CAGR of Parag Parikh Flexi Cap because it has no access to those specific dates and NAV values. Any financial metric it gives you is either hallucinated or recalled from training text that may be wrong, outdated, or from a different source.
What the LLM does in your system:
User: "What is the 5-year CAGR of TCS?"
↓
LLM reads the question + your schema metadata
↓
Generates SQL:
SELECT cagr_pct FROM ai_stock_cagr WHERE symbol='TCS' AND years=5
↓
Your database executes it → returns -5.98% (correct, verified, auditable)
The LLM is the translator (English → SQL). Your database is the truth. Without the database, the LLM gives opinions. Without the LLM, your database has no natural language interface. Both are necessary. Neither replaces the other.
On token costs: Only SQL generation and prose narration touch an external LLM. Vector similarity, entity resolution, schema retrieval, SQL validation — all run inside Oracle at zero API cost.
On the DBA’s value: AI practitioners without database skills build systems on unverified data. They don’t check corporate action factors, don’t audit NAV gaps, don’t design schemas an LLM can reason over unambiguously. You caught three data quality issues in Week 1 that would have silently corrupted every analytics result. That instinct is irreplaceable.
WEEK 1: UPGRADE, CLONE, AND DATA AUDIT
The infrastructure decision — two instances
Always Free ADB allows two instances per tenancy. Rather than developing on PROD:
| Instance | Role |
|---|---|
| PROD | Live portfolio app, daily loaders, real data |
| DEV (clone of PROD) | All AI development, weeks 1–12 |
PROD was upgraded from 19c → 26ai in-place (15-minute downtime, automatic revert on failure). DEV was cloned from PROD after upgrade.
Workload type discovery: PROD was an APEX Service instance — no SQL client connectivity. When cloned at 26ai, the DEV clone arrived as Transaction Processing (ATP) with full client access. Always verify workload type after cloning.
Verifying 26ai
SELECT banner_full FROM v$version;
-- Oracle AI Database 26ai Enterprise Edition Release 23.26.3.1.0
-- Vector smoke test — the one query that confirms the whole mechanism
SELECT VECTOR_DISTANCE(VECTOR('[1,0]',2,FLOAT32),
VECTOR('[0,1]',2,FLOAT32), COSINE) FROM dual;
-- Returns: 1.0 (orthogonal vectors = maximum cosine distance — correct)
-- Build and query a tiny vector table
CREATE TABLE vec_smoke (id NUMBER, v VECTOR(3,FLOAT32));
INSERT INTO vec_smoke VALUES (1,'[1,0,0]');
INSERT INTO vec_smoke VALUES (2,'[0.9,0.1,0]');
INSERT INTO vec_smoke VALUES (3,'[0,0,1]');
COMMIT;
SELECT id, ROUND(VECTOR_DISTANCE(v, VECTOR('[1,0,0]',3,FLOAT32), COSINE),4) dist
FROM vec_smoke ORDER BY dist;
-- Must return order: 1 (dist=0), 2 (dist small), 3 (dist=1)
Baseline capture before any changes
-- Object inventory
CREATE TABLE upgrade_baseline_objects AS
SELECT owner, object_type, status, COUNT(*) cnt
FROM dba_objects
WHERE owner IN ('WKSP_STOCKTRADE','WKSP_STOCKDATA')
GROUP BY owner, object_type, status;
-- Row counts
CREATE TABLE upgrade_baseline_rowcounts AS
SELECT 'MF_NAV_HISTORY' t, COUNT(*) c FROM wksp_stockdata.mf_nav_history
UNION ALL SELECT 'NSE_PD_BHAVCOPY_HISTORY', COUNT(*) FROM wksp_stockdata.nse_pd_bhavcopy_history
UNION ALL SELECT 'NSE_CORPORATE_ACTIONS_HISTORY', COUNT(*) FROM wksp_stockdata.nse_corporate_actions_history
UNION ALL SELECT 'MF_SCHEMES', COUNT(*) FROM wksp_stockdata.mf_schemes;
-- Performance timing on key views
DECLARE
t0 NUMBER; n NUMBER;
TYPE tlist IS TABLE OF VARCHAR2(100);
v tlist := tlist('STOCK_CAGR_DIVIDEND','MF_CAGR_DIVIDEND',
'PORTFOLIO_RISK_SUMMARY_V','MF_PORTFOLIO_VAL');
BEGIN
FOR i IN 1..v.COUNT LOOP
BEGIN
t0 := DBMS_UTILITY.GET_TIME;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v(i) INTO n;
INSERT INTO upgrade_baseline_timing(view_name,phase,elapsed_ms,row_count)
VALUES (v(i),'BEFORE',(DBMS_UTILITY.GET_TIME-t0)*10,n);
EXCEPTION WHEN OTHERS THEN
INSERT INTO upgrade_baseline_timing(view_name,phase,elapsed_ms,row_count)
VALUES (v(i),'BEFORE',-1,-1);
END;
END LOOP;
COMMIT;
END;
/
The data integrity audit
Before building anything, we audited the foundation. “Loaded without error” and “data is correct” are two different statements.
Corporate actions — the highest-stakes check
Your existing STOCK_CAGR_DIVIDEND uses EXP(SUM(LN(adj_factor))) to adjust historical prices. If all adj_factors were 1, every CAGR in your live app would be wrong for any stock that had a split or bonus.
SELECT action_type, series, COUNT(*),
MIN(adj_factor) min_f, MAX(adj_factor) max_f
FROM wksp_stockdata.nse_corporate_actions_history
GROUP BY action_type, series ORDER BY 3 DESC;
-- Result: 418 BONUS rows with real factors, 377 SPLIT rows — CAGR logic is trustworthy
-- Also check for unrecorded splits (large single-day gap, no action logged)
SELECT symbol, price_date, close_price, prev_close,
ROUND(close_price/NULLIF(prev_close,0),3) ratio
FROM (
SELECT symbol, bhav_date price_date, close_price,
LAG(close_price) OVER (PARTITION BY symbol ORDER BY bhav_date) prev_close
FROM wksp_stockdata.nse_pd_bhavcopy_history WHERE series='EQ'
)
WHERE prev_close > 0
AND (close_price/prev_close < 0.6 OR close_price/prev_close > 1.6)
AND NOT EXISTS (
SELECT 1 FROM wksp_stockdata.nse_corporate_actions_history c
WHERE c.symbol=symbol AND c.ex_date=price_date
AND c.action_type IN ('BONUS','SPLIT'))
ORDER BY ratio FETCH FIRST 30 ROWS ONLY;
Duplicate rows — must be zero
SELECT 'MF_NAV' src, COUNT(*) dup_keys FROM (
SELECT scheme_code, nav_date FROM wksp_stockdata.mf_nav_history
GROUP BY scheme_code, nav_date HAVING COUNT(*) > 1)
UNION ALL
SELECT 'NSE_BHAV', COUNT(*) FROM (
SELECT symbol, bhav_date FROM wksp_stockdata.nse_pd_bhavcopy_history
WHERE series='EQ' GROUP BY symbol, bhav_date HAVING COUNT(*) > 1);
-- Result: 0 / 0
NAV gaps — the eligibility gate insight
-- Funds with gaps >10 days in their NAV series
SELECT scheme_code, COUNT(*) nav_days, MAX(gap_days) worst_gap
FROM (
SELECT scheme_code, nav_date,
nav_date - LAG(nav_date) OVER (PARTITION BY scheme_code ORDER BY nav_date) gap_days
FROM wksp_stockdata.mf_nav_history)
GROUP BY scheme_code HAVING MAX(gap_days) > 10
ORDER BY worst_gap DESC FETCH FIRST 30 ROWS ONLY;
Key insight: funds with 900+ observations but gaps of 2,700+ days. Observation count is the wrong gate. Continuity within the analysis window is the right gate.
-- Count eligible funds using the continuity gate
SELECT COUNT(*) eligible_schemes
FROM (
SELECT scheme_code, COUNT(*) nav_days, MAX(gap) worst_gap
FROM (
SELECT scheme_code, nav_date,
nav_date - LAG(nav_date) OVER (PARTITION BY scheme_code ORDER BY nav_date) gap
FROM wksp_stockdata.mf_nav_history
WHERE nav_date >= ADD_MONTHS(SYSDATE, -60)) -- trailing 60 months only
GROUP BY scheme_code)
WHERE nav_days >= 1000 AND worst_gap <= 7;
-- Result: ~5,553 eligible schemes out of 14,200 total
Week 1 confirmed results
| Check | Result |
|---|---|
| 26ai version | Oracle AI Database 26ai Enterprise Edition Release 23.26.3.1.0 |
| Vector orthogonal distance | 1.0 (correct) |
| Corporate action factors | 418 BONUS + 377 SPLIT with real adj_factor values |
| Duplicate rows | 0 in both tables |
| Eligible schemes | ~5,553 (trailing 5-year continuity gate) |
WEEK 2: ENRICHING THE SCHEME DIMENSION
The problem: 14,200 schemes with no metadata
All schemes had SCHEME_NAME and ISINs populated but AMC_NAME, CATEGORY, PLAN_TYPE were all NULL. The similarity metadata gate, the variant grouping, and entity resolution text all depended on these fields. Without them, the project couldn’t proceed.
Step 1: Add enrichment columns
SET DEFINE OFF
ALTER TABLE wksp_stockdata.mf_schemes ADD (
amc_code VARCHAR2(3),
amc_name_d VARCHAR2(120), -- _d = derived, not overwriting original
category_d VARCHAR2(60),
plan_type_d VARCHAR2(10),
option_type_d VARCHAR2(20),
is_investable CHAR(1) DEFAULT 'Y',
is_eligible CHAR(1) DEFAULT 'N',
base_fund_key VARCHAR2(200)
);
Step 2: ISIN → AMC map
Indian MF ISINs follow INF + 3-character issuer code + suffix. Characters 4–6 map deterministically to the fund house.
CREATE TABLE wksp_stockdata.isin_amc_map (
amc_code VARCHAR2(3) PRIMARY KEY,
amc_name VARCHAR2(120),
notes VARCHAR2(200)
);
INSERT ALL
INTO wksp_stockdata.isin_amc_map VALUES ('109','ICICI Prudential',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('204','Nippon India','ex-Reliance')
INTO wksp_stockdata.isin_amc_map VALUES ('789','UTI',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('174','Kotak Mahindra',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('179','HDFC',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('200','SBI',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('194','Bandhan','ex-IDFC')
INTO wksp_stockdata.isin_amc_map VALUES ('209','Aditya Birla Sun Life',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('846','Axis',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('740','DSP',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('090','Franklin Templeton',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('277','Tata',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('769','Mirae Asset',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('754','Edelweiss',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('666','Groww','ex-Indiabulls')
INTO wksp_stockdata.isin_amc_map VALUES ('205','Invesco India',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('247','Motilal Oswal',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('903','Sundaram',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('251','Baroda BNP Paribas',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('336','HSBC',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('192','JM Financial',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('582','Union',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('767','LIC',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('760','Canara Robeco',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('966','Quant',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('761','Bank of India',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('0QA','Bajaj Finserv',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('223','PGIM India',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('082','Quantum',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('879','Parag Parikh (PPFAS)',NULL)
INTO wksp_stockdata.isin_amc_map VALUES ('2VO','AlphaGrep',NULL)
-- (full 65-entry version in mf_schemes_enrichment.sql)
SELECT * FROM dual;
COMMIT;
Step 3: Derive AMC code — the literal dash finding
Critical finding: AMFI stores a literal dash - (not SQL NULL) where no ISIN exists. Standard NVL doesn’t help because - is not NULL. Must test != '-'.
-- WRONG: NVL doesn't catch literal '-'
-- CORRECT: explicit literal dash test
UPDATE wksp_stockdata.mf_schemes
SET amc_code = COALESCE(
CASE WHEN isin_div_payout_growth IS NOT NULL
AND isin_div_payout_growth != '-' -- literal dash, not SQL NULL
THEN SUBSTR(isin_div_payout_growth,4,3) END,
CASE WHEN isin_div_reinvestment IS NOT NULL
AND isin_div_reinvestment != '-'
THEN SUBSTR(isin_div_reinvestment,4,3) END);
UPDATE wksp_stockdata.mf_schemes s
SET amc_name_d = (SELECT m.amc_name FROM wksp_stockdata.isin_amc_map m
WHERE m.amc_code = s.amc_code);
COMMIT;
-- Coverage result: 98.8% — 170 unclaimed schemes had no ISIN, flagged non-investable
Step 4: Category classifier — three iterations to 2.3% unclassified
Two Oracle-specific lessons learned the hard way:
\bword boundaries are unreliable in Oracle regex — use(^| )WORD( |$|-)instead&in patterns causes substitution variable prompts in Database Actions — alwaysSET DEFINE OFF
SET DEFINE OFF
UPDATE wksp_stockdata.mf_schemes
SET category_d = CASE
-- Priority order: non-investable → structural wrapper → hybrid → equity → debt → solution
WHEN REGEXP_LIKE(scheme_name,'UNCLAIMED|SEGREGATED PORTFOLIO','i') THEN 'NON_INVESTABLE'
WHEN REGEXP_LIKE(scheme_name,'(GOLD|SILVER).*ETF|ETF.*(GOLD|SILVER)','i') THEN 'COMMODITY_ETF'
WHEN REGEXP_LIKE(scheme_name,'GOLD SILVER|SILVER.*GOLD|GOLD.*FOF|GOLD FUND','i') THEN 'COMMODITY_ETF'
WHEN REGEXP_LIKE(scheme_name,'BHARAT BOND.*FOF','i') THEN 'FOF_OVERSEAS'
WHEN REGEXP_LIKE(scheme_name,'BHARAT BOND','i') THEN 'ETF'
WHEN REGEXP_LIKE(scheme_name,'\bETF\b|EXCHANGE TRADED','i') THEN 'ETF'
WHEN REGEXP_LIKE(scheme_name,
'FUND OF FUND|\bFOF\b|OMNI FOF|OFF-?SHORE|OVERSEAS|'||
'US BLUECHIP|INTERNATIONAL EQUITY|GLOBAL BRAND|\bGLOBAL\b','i') THEN 'FOF_OVERSEAS'
WHEN REGEXP_LIKE(scheme_name,
'NIFTY|SENSEX|\bBSE\b|\bNSE\b|EQUAL WEIGHT|LOW VOLATILITY|'||
'QUALITY [0-9]|TOTAL MARKET|\bINDEX\b|MULTI.FACTOR','i') THEN 'INDEX'
WHEN REGEXP_LIKE(scheme_name,'AGGRESSIVE HYBRID','i') THEN 'HYBRID_AGGRESSIVE'
WHEN REGEXP_LIKE(scheme_name,'HYBRID.{0,10}(95|EQUITY HYBRID)','i') THEN 'HYBRID_AGGRESSIVE'
WHEN REGEXP_LIKE(scheme_name,'EQUITY.{0,5}DEBT|DEBT.{0,5}EQUITY','i') THEN 'HYBRID_AGGRESSIVE'
WHEN REGEXP_LIKE(scheme_name,
'CONSERVATIVE HYBRID|MIP\b|MONTHLY INCOME|HYBRID.*DEBT|DEBT.*HYBRID','i') THEN 'HYBRID_CONSERVATIVE'
WHEN REGEXP_LIKE(scheme_name,'BALANCED ADVANTAGE|DYNAMIC ASSET','i') THEN 'HYBRID_BALANCED_ADV'
WHEN REGEXP_LIKE(scheme_name,'MULTI.ASSET','i') THEN 'HYBRID_MULTI_ASSET'
WHEN REGEXP_LIKE(scheme_name,'ARBITRAGE','i') THEN 'HYBRID_ARBITRAGE'
WHEN REGEXP_LIKE(scheme_name,'\bEQUITY SAVINGS\b','i') THEN 'HYBRID_EQUITY_SAVINGS'
WHEN REGEXP_LIKE(scheme_name,'\bHYBRID\b|BALANCED','i') THEN 'HYBRID_OTHER'
WHEN REGEXP_LIKE(scheme_name,'FLEXI.?CAP','i') THEN 'EQ_FLEXI_CAP'
WHEN REGEXP_LIKE(scheme_name,'LARGE.{0,3}MID','i') THEN 'EQ_LARGE_MID_CAP'
WHEN REGEXP_LIKE(scheme_name,'LARGE.?CAP','i') THEN 'EQ_LARGE_CAP'
WHEN REGEXP_LIKE(scheme_name,'MID.?CAP','i') THEN 'EQ_MID_CAP'
WHEN REGEXP_LIKE(scheme_name,'SMALL.?CAP','i') THEN 'EQ_SMALL_CAP'
WHEN REGEXP_LIKE(scheme_name,'MULTI.?CAP','i') THEN 'EQ_MULTI_CAP'
WHEN REGEXP_LIKE(scheme_name,'\bVALUE\b|CONTRA|VALUE DISC','i') THEN 'EQ_VALUE_CONTRA'
WHEN REGEXP_LIKE(scheme_name,'FOCUSS?ED','i') THEN 'EQ_FOCUSED'
WHEN REGEXP_LIKE(scheme_name,'DIVIDEND YIELD','i') THEN 'EQ_DIVIDEND_YIELD'
WHEN REGEXP_LIKE(scheme_name,
'\bELSS\b|TAX SAVER|TAX SAVING|LONG TERM TAX|TAX ADVANTAGE|'||
'EQUITY LINKED SAVING','i') THEN 'EQ_ELSS'
WHEN REGEXP_LIKE(scheme_name,
'BUSINESS CYCLE|PIONEER|ESG|SUSTAINABILITY|QUANT.?FUND|\bQUANT\b|'||
'SPECIAL OPP|OPPORTUNIT|MOMENTUM|\bMNC\b|ETHICAL|SHARIAH|'||
'INNOVATION FUND|DIGITAL INDIA','i') THEN 'EQ_THEMATIC'
WHEN REGEXP_LIKE(scheme_name,
'INFRA|BANKING.{0,3}FINAN|TECHNOLOGY|TECK|PHARMA|CONSUMPTION|'||
'MANUFACTUR|TRANSPORT|AUTO|MINING|ENERGY|PSU EQUIT|HEALTHCARE|'||
'FINANCIAL SERVICES|FMCG|CONSUMER|COMMA\b|(^| )PSU( |$)|'||
'COMMODIT|SERVICES FUND','i') THEN 'EQ_SECTORAL'
WHEN REGEXP_LIKE(scheme_name,'OVERNIGHT','i') THEN 'DEBT_OVERNIGHT'
WHEN REGEXP_LIKE(scheme_name,'LIQUID','i') THEN 'DEBT_LIQUID'
WHEN REGEXP_LIKE(scheme_name,
'ULTRA SHORT|SAVINGS FUND|SAVINGS PLUS|TREASURY ADVANTAGE|\bINSTA\b','i') THEN 'DEBT_ULTRA_SHORT'
WHEN REGEXP_LIKE(scheme_name,'LOW DURATION','i') THEN 'DEBT_LOW_DURATION'
WHEN REGEXP_LIKE(scheme_name,'MONEY MARKET','i') THEN 'DEBT_MONEY_MARKET'
WHEN REGEXP_LIKE(scheme_name,'FLOATING RATE|FLOATER|FLOATING INTEREST','i') THEN 'DEBT_FLOATING'
WHEN REGEXP_LIKE(scheme_name,'SHORT DURATION|SHORT TERM','i') THEN 'DEBT_SHORT_DURATION'
WHEN REGEXP_LIKE(scheme_name,
'MEDIUM TO LONG|LONG DURATION|LONG TERM BOND|LONG TERM DEBT','i') THEN 'DEBT_LONG_DURATION'
WHEN REGEXP_LIKE(scheme_name,'MEDIUM DURATION|MEDIUM TERM','i') THEN 'DEBT_MEDIUM_DURATION'
WHEN REGEXP_LIKE(scheme_name,'CORPORATE DEBT|CORPORATE BOND','i') THEN 'DEBT_CORPORATE_BOND'
WHEN REGEXP_LIKE(scheme_name,'CREDIT RISK|ACCRUAL|DYNAMIC ACCRUAL','i') THEN 'DEBT_CREDIT_RISK'
WHEN REGEXP_LIKE(scheme_name,'BANKING.{0,3}PSU|BANKING AND PSU','i') THEN 'DEBT_BANKING_PSU'
WHEN REGEXP_LIKE(scheme_name,
'GILT|G-?SEC|SDL|IBX|GOVERNMENT SECURITIES|GOVT SEC','i') THEN 'DEBT_GILT'
WHEN REGEXP_LIKE(scheme_name,
'DYNAMIC BOND|DYNAMIC DEBT|DYNAMIC TERM|STRATEGIC BOND','i') THEN 'DEBT_DYNAMIC'
WHEN REGEXP_LIKE(scheme_name,
'FIXED (TERM|MATURITY|HORIZON)|FMP|INTERVAL|CAPITAL PROTECTION|'||
'DUAL ADVANTAGE|MULTIPLE YIELD|FTIF|CAPITAL BUILDER','i') THEN 'DEBT_FIXED_TERM'
WHEN REGEXP_LIKE(scheme_name,'\bBOND\b|\bDEBT\b|INCOME','i') THEN 'DEBT_OTHER'
WHEN REGEXP_LIKE(scheme_name,
'RETIREMENT|BHAVISHYA|CHILD|\bULIS\b|WEALTH ENHANCEMENT','i') THEN 'SOLUTION_ORIENTED'
ELSE 'UNCLASSIFIED'
END;
-- Straggler patch for remaining unclassified investable schemes
UPDATE wksp_stockdata.mf_schemes
SET category_d = CASE
WHEN REGEXP_LIKE(scheme_name,'ULIS','i') THEN 'SOLUTION_ORIENTED'
WHEN REGEXP_LIKE(scheme_name,'(^| )MNC( |$|-)','i') THEN 'EQ_THEMATIC'
WHEN REGEXP_LIKE(scheme_name,'ASIAN EQUITY|ASIAN FUND','i') THEN 'FOF_OVERSEAS'
WHEN REGEXP_LIKE(scheme_name,'ELSS FUND|ELSS ','i') THEN 'EQ_ELSS'
WHEN REGEXP_LIKE(scheme_name,'BOND.DEPOSIT|BOND FUND','i') THEN 'DEBT_OTHER'
WHEN REGEXP_LIKE(scheme_name,'VALUE FUND|VALUE PLAN','i')
AND NOT REGEXP_LIKE(scheme_name,'VALUE RESEARCH','i') THEN 'EQ_VALUE_CONTRA'
ELSE category_d
END
WHERE category_d='UNCLASSIFIED' AND is_investable='Y';
COMMIT;
-- Final result: 2.3% unclassified (317 investable schemes — left deliberately)
Step 5: Plan/option types and base fund key
SET DEFINE OFF
UPDATE wksp_stockdata.mf_schemes
SET plan_type_d = CASE
WHEN REGEXP_LIKE(scheme_name,'DIRECT','i') THEN 'DIRECT'
WHEN REGEXP_LIKE(scheme_name,'REGULAR','i') THEN 'REGULAR'
ELSE 'UNKNOWN' END,
option_type_d = CASE
WHEN (isin_div_payout_growth IS NULL OR isin_div_payout_growth='-')
AND isin_div_reinvestment IS NOT NULL AND isin_div_reinvestment!='-' THEN 'IDCW_REINVEST'
WHEN REGEXP_LIKE(scheme_name,'GROWTH','i') THEN 'GROWTH'
WHEN REGEXP_LIKE(scheme_name,'IDCW|DIVIDEND|PAYOUT|INCOME DISTRIBUTION','i') THEN 'IDCW'
WHEN REGEXP_LIKE(scheme_name,'BONUS','i') THEN 'BONUS'
ELSE 'UNKNOWN' END;
-- Base fund key: strip plan/option tokens to group all variants of one portfolio
-- BUG FOUND AND FIXED: first version left GROWTH in the key when at end of name
-- Must strip GROWTH explicitly in the option-stripping regex pass
UPDATE wksp_stockdata.mf_schemes
SET base_fund_key =
amc_code || ':' ||
TRIM(REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(UPPER(scheme_name),
'[-(]?\s*(DIRECT|REGULAR)\s*(PLAN)?\s*[-)]?', ' '),
'[-(]?\s*(GROWTH|IDCW|DIVIDEND|PAYOUT|REINVESTMENT|BONUS|'||
'MONTHLY|QUARTERLY|ANNUAL|DAILY|WEEKLY|OPTION|'||
'INCOME DISTRIBUTION CUM CAPITAL WITHDRAWAL|PLAN [AB])\s*[-)]?', ' '),
'\s{2,}', ' '));
UPDATE wksp_stockdata.mf_schemes
SET is_investable='N'
WHERE category_d='NON_INVESTABLE' OR REGEXP_LIKE(scheme_name,'UNCLAIMED|SEGREGATED','i');
COMMIT;
-- Spot-check: all 4 Parag Parikh variants must share one base_fund_key
SELECT scheme_code, plan_type_d, option_type_d, category_d, base_fund_key
FROM wksp_stockdata.mf_schemes
WHERE UPPER(scheme_name) LIKE '%PARAG PARIKH FLEXI%' ORDER BY scheme_name;
-- All four rows: '879:PARAG PARIKH FLEXI CAP FUND'
Step 6: Eligibility flag
UPDATE wksp_stockdata.mf_schemes SET is_eligible='N';
MERGE INTO wksp_stockdata.mf_schemes s
USING (
SELECT scheme_code FROM (
SELECT scheme_code, COUNT(*) nav_days, MAX(gap) worst_gap
FROM (
SELECT scheme_code, nav_date,
nav_date - LAG(nav_date) OVER (PARTITION BY scheme_code ORDER BY nav_date) gap
FROM wksp_stockdata.mf_nav_history
WHERE nav_date >= ADD_MONTHS(SYSDATE, -60))
GROUP BY scheme_code)
WHERE nav_days >= 1000 AND worst_gap <= 7
) elig ON (s.scheme_code = elig.scheme_code)
WHEN MATCHED THEN UPDATE SET s.is_eligible='Y';
COMMIT;
SELECT is_eligible, COUNT(*) FROM wksp_stockdata.mf_schemes GROUP BY is_eligible;
-- Y=5,553 N=8,664
is_eligible ≠ is_investable. Different questions:
is_investable: is this a real current product? (N for wound-up, unclaimed)is_eligible: does it have clean 5-year history for statistics? (N for new funds)
Step 7: Promote to real columns
UPDATE wksp_stockdata.mf_schemes
SET amc_name = amc_name_d,
category = category_d,
plan_type = plan_type_d
WHERE amc_name_d IS NOT NULL;
COMMIT;
The enrichment persistence problem — fixed permanently
The problem: MF_SCHEMES is fully deleted and reloaded on every daily loader run. All enrichment was silently wiped every night.
Root cause: the loader does DELETE FROM mf_schemes followed by INSERT from the AMFI file, which contains only base columns. Derived columns are always NULL after reload.
Permanent fix: enrich_mf_schemes() added as Step 6 inside the loader transaction. The full modified mf_nav_loader_pkg body is at the end of this document.
Semantic views (run as ADMIN, objects in WKSP_STOCKTRADE)
-- NSE daily price history
-- Real column names confirmed from DDL: SECURITY, PREV_CL_PR, NET_TRDQTY, HI_52_WK
CREATE OR REPLACE VIEW wksp_stocktrade.ai_nse_price_daily AS
SELECT symbol, security AS company_name, bhav_date AS price_date,
open_price, high_price, low_price, close_price,
net_trdqty AS volume, net_trdval AS traded_value,
hi_52_wk AS week_52_high, lo_52_wk AS week_52_low
FROM wksp_stockdata.nse_pd_bhavcopy_history WHERE series='EQ';
-- NSE corporate actions
CREATE OR REPLACE VIEW wksp_stocktrade.ai_nse_corporate_action AS
SELECT symbol, series, ex_date, record_date, purpose,
action_type, action_value, adj_factor
FROM wksp_stockdata.nse_corporate_actions_history WHERE series='EQ';
-- MF NAV daily with enriched dimension
CREATE OR REPLACE VIEW wksp_stocktrade.ai_mf_nav_daily AS
SELECT n.scheme_code, s.scheme_name,
NVL(s.amc_name,s.amc_name_d) AS amc_name,
NVL(s.category,s.category_d) AS category,
NVL(s.plan_type,s.plan_type_d) AS plan_type,
s.option_type_d AS option_type, s.base_fund_key, s.is_eligible,
n.nav_date, n.nav_value
FROM wksp_stockdata.mf_nav_history n
JOIN wksp_stockdata.mf_schemes s ON s.scheme_code=n.scheme_code
WHERE s.is_investable='Y';
-- MF scheme master dimension
CREATE OR REPLACE VIEW wksp_stocktrade.ai_mf_scheme AS
SELECT scheme_code, scheme_name,
NVL(amc_name,amc_name_d) AS amc_name,
NVL(category,category_d) AS category,
NVL(plan_type,plan_type_d) AS plan_type,
option_type_d AS option_type, base_fund_key, is_investable, is_eligible
FROM wksp_stockdata.mf_schemes;
-- Current NSE price snapshot — most recent trading day only
CREATE OR REPLACE VIEW wksp_stocktrade.ai_stock_snapshot AS
SELECT p.symbol, p.security AS company_name, p.close_price AS current_price,
p.bhav_date AS price_date, p.prev_cl_pr AS prev_close,
ROUND((p.close_price-p.prev_cl_pr)/NULLIF(p.prev_cl_pr,0)*100,2) AS day_change_pct,
p.open_price, p.high_price AS day_high, p.low_price AS day_low,
p.net_trdqty AS volume, p.net_trdval AS traded_value,
p.hi_52_wk AS week_52_high, p.lo_52_wk AS week_52_low
FROM wksp_stockdata.nse_pd_bhavcopy_history p
WHERE p.series='EQ'
AND p.bhav_date=(SELECT MAX(b.bhav_date)
FROM wksp_stockdata.nse_pd_bhavcopy_history b WHERE b.series='EQ');
-- Current MF NAV snapshot — most recent nav_date per scheme
CREATE OR REPLACE VIEW wksp_stocktrade.ai_mf_snapshot AS
SELECT s.scheme_code, s.base_fund_key, s.scheme_name,
NVL(s.amc_name,s.amc_name_d) AS amc_name,
NVL(s.category,s.category_d) AS category,
NVL(s.plan_type,s.plan_type_d) AS plan_type,
s.option_type_d AS option_type, s.is_eligible,
h.nav_value AS current_nav, h.nav_date
FROM wksp_stockdata.mf_schemes s
JOIN wksp_stockdata.mf_nav_history h ON h.scheme_code=s.scheme_code
WHERE s.is_investable='Y'
AND h.nav_date=(SELECT MAX(h2.nav_date) FROM wksp_stockdata.mf_nav_history h2
WHERE h2.scheme_code=s.scheme_code);
-- Canonical variant: one Direct+Growth scheme per base fund
-- ROW_NUMBER picks Direct over Regular, Growth over IDCW
CREATE OR REPLACE VIEW wksp_stocktrade.ai_mf_canonical AS
SELECT base_fund_key, scheme_code, scheme_name,
NVL(amc_name,amc_name_d) AS amc_name,
NVL(category,category_d) AS category,
plan_type_d AS plan_type, option_type_d AS option_type
FROM (
SELECT s.*, ROW_NUMBER() OVER (
PARTITION BY s.base_fund_key
ORDER BY CASE s.plan_type_d WHEN 'DIRECT' THEN 1 WHEN 'REGULAR' THEN 2 ELSE 3 END,
CASE s.option_type_d WHEN 'GROWTH' THEN 1 WHEN 'UNKNOWN' THEN 2
WHEN 'IDCW' THEN 3 WHEN 'IDCW_REINVEST' THEN 4
ELSE 5 END) AS rn
FROM wksp_stockdata.mf_schemes s WHERE s.is_eligible='Y' AND s.is_investable='Y'
) WHERE rn=1;
WEEK 3: METRICS LAYER, GLOSSARY, AND OBJECT CATALOG
Why the metrics layer exists
The LLM knows the CAGR formula. It does not know that your CAGR must use EXP(SUM(LN(adj_factor))), that MF NAVs need no adjustment, or that ai_nse_price_daily has close_price but ai_stock_cagr has the pre-computed correct answer. If the model computes CAGR from raw prices it produces plausible- looking incorrect numbers — confident and wrong. The fix: pre-compute metrics in views the model can only SELECT FROM. The view embeds the correct logic.
Supporting tables
-- Object catalog: the retrieval corpus for Week 7 RAG
-- Week 7 will embed these descriptions and retrieve the most relevant ones
-- for each user question — avoiding sending all 82 tables to the LLM every time
CREATE TABLE wksp_stocktrade.ai_object_catalog (
object_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
object_name VARCHAR2(128) NOT NULL,
object_type VARCHAR2(30) DEFAULT 'VIEW',
description VARCHAR2(2000) NOT NULL,
grain VARCHAR2(300),
column_doc CLOB,
sample_question VARCHAR2(500),
usage_notes VARCHAR2(2000),
do_not_use_for VARCHAR2(1000), -- prevents wrong view selection
embedding VECTOR(384, FLOAT32)
);
-- Business glossary: what terms mean IN YOUR APP specifically
CREATE TABLE wksp_stocktrade.ai_glossary (
term VARCHAR2(60) PRIMARY KEY,
synonyms VARCHAR2(500),
definition VARCHAR2(2000),
canonical_sql VARCHAR2(4000),
source_view VARCHAR2(100),
notes VARCHAR2(1000)
);
Stock CAGR materialized view
Extends STOCK_CAGR_DIVIDEND to full NSE universe. Original had hardcoded 120-month window and was joined to your holdings — couldn’t answer for stocks you don’t own.
Floating-point tail fix: EXP(LN(0.5)) does not recover exactly 0.5 in IEEE 754. Add ROUND(..., 6) to eliminate the 0.4999...976 tail. Zero effect on CAGR at 2dp.
DROP MATERIALIZED VIEW wksp_stocktrade.ai_stock_cagr_mv;
CREATE MATERIALIZED VIEW wksp_stocktrade.ai_stock_cagr_mv
BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS
WITH horizons AS (
SELECT COLUMN_VALUE AS years FROM TABLE(sys.odcinumberlist(1,3,5,10))
),
price_bounds AS (
SELECT p.symbol, h.years,
MIN(p.bhav_date) AS start_date,
MAX(p.bhav_date) AS end_date,
MIN(p.close_price) KEEP (DENSE_RANK FIRST ORDER BY p.bhav_date) AS start_price,
MAX(p.close_price) KEEP (DENSE_RANK LAST ORDER BY p.bhav_date) AS end_price,
MAX(p.security) KEEP (DENSE_RANK LAST ORDER BY p.bhav_date) AS company_name
FROM wksp_stockdata.nse_pd_bhavcopy_history p CROSS JOIN horizons h
WHERE p.series='EQ'
AND p.bhav_date >= ADD_MONTHS(TRUNC(SYSDATE),-12*h.years)
GROUP BY p.symbol, h.years HAVING COUNT(DISTINCT p.bhav_date)>=20
),
adjustments AS (
SELECT pb.symbol, pb.years,
ROUND(NVL(
(SELECT EXP(SUM(LN(NULLIF(c.adj_factor,0))))
FROM wksp_stockdata.nse_corporate_actions_history c
WHERE c.symbol=pb.symbol AND c.series='EQ'
AND c.action_type IN ('BONUS','SPLIT')
AND c.ex_date>pb.start_date AND c.ex_date<=pb.end_date),1),6)
AS total_adj_factor -- ROUND(,6) eliminates floating-point tail
FROM price_bounds pb
)
SELECT pb.symbol, pb.company_name, pb.years, pb.start_date, pb.end_date,
(pb.end_date-pb.start_date) AS days_held,
pb.start_price, pb.end_price, adj.total_adj_factor,
ROUND(pb.start_price*adj.total_adj_factor,4) AS adj_start_price,
ROUND((POWER(pb.end_price/NULLIF(pb.start_price*adj.total_adj_factor,0),
1/NULLIF((pb.end_date-pb.start_date)/365.25,0))-1)*100,2) AS cagr_pct
FROM price_bounds pb JOIN adjustments adj ON adj.symbol=pb.symbol AND adj.years=pb.years;
CREATE OR REPLACE VIEW wksp_stocktrade.ai_stock_cagr AS SELECT * FROM wksp_stocktrade.ai_stock_cagr_mv;
Verified results:
- TCS 5-year CAGR: -5.98% (IT sector correction — real, not a bug)
- HDFCBANK:
total_adj_factor=0.5(Aug 2025 bonus),0.25for 10yr (2019 split × bonus) - RELIANCE:
total_adj_factor=0.5(Oct 2024 bonus)
MF CAGR materialized view
MF NAVs are already total-return — they incorporate every dividend and split through the NAV value itself. No corporate action adjustment needed.
DROP MATERIALIZED VIEW wksp_stocktrade.ai_mf_cagr_mv;
CREATE MATERIALIZED VIEW wksp_stocktrade.ai_mf_cagr_mv
BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS
WITH horizons AS (SELECT COLUMN_VALUE AS years FROM TABLE(sys.odcinumberlist(1,3,5,10))),
nav_bounds AS (
SELECT n.scheme_code, h.years,
MIN(n.nav_date) AS start_date, MAX(n.nav_date) AS end_date,
MIN(n.nav_value) KEEP (DENSE_RANK FIRST ORDER BY n.nav_date) AS start_nav,
MAX(n.nav_value) KEEP (DENSE_RANK LAST ORDER BY n.nav_date) AS end_nav
FROM wksp_stockdata.mf_nav_history n
JOIN wksp_stocktrade.ai_mf_canonical c ON c.scheme_code=n.scheme_code
CROSS JOIN horizons h
WHERE n.nav_date>=ADD_MONTHS(TRUNC(SYSDATE),-12*h.years)
GROUP BY n.scheme_code, h.years HAVING COUNT(DISTINCT n.nav_date)>=20
)
SELECT nb.scheme_code, c.base_fund_key, c.scheme_name, c.amc_name, c.category,
nb.years, nb.start_date, nb.end_date, (nb.end_date-nb.start_date) AS days_held,
nb.start_nav, nb.end_nav,
-- No corporate action adjustment: MF NAV is already total-return
ROUND((POWER(nb.end_nav/NULLIF(nb.start_nav,0),
1/NULLIF((nb.end_date-nb.start_date)/365.25,0))-1)*100,2) AS cagr_pct
FROM nav_bounds nb JOIN wksp_stocktrade.ai_mf_canonical c ON c.scheme_code=nb.scheme_code;
CREATE OR REPLACE VIEW wksp_stocktrade.ai_mf_cagr AS SELECT * FROM wksp_stocktrade.ai_mf_cagr_mv;
Stock drawdown materialized view
Data quality finding on first pass: LICMFGOLD at -99.25% and IVZINGOLD at -99.22%. Gold ETFs technically trade as series=’EQ’ but are not equity stocks. Fix: quality filters exclude ETFs, penny stocks, and pathological instruments.
DROP MATERIALIZED VIEW wksp_stocktrade.ai_stock_drawdown_mv;
CREATE MATERIALIZED VIEW wksp_stocktrade.ai_stock_drawdown_mv
BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS
WITH horizons AS (SELECT COLUMN_VALUE AS years FROM TABLE(sys.odcinumberlist(1,3,5))),
daily AS (
SELECT p.symbol, h.years, p.bhav_date, p.close_price,
MAX(p.close_price) OVER (PARTITION BY p.symbol,h.years ORDER BY p.bhav_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_peak
FROM wksp_stockdata.nse_pd_bhavcopy_history p CROSS JOIN horizons h
WHERE p.series='EQ' AND p.bhav_date>=ADD_MONTHS(TRUNC(SYSDATE),-12*h.years)
),
rets AS (
SELECT symbol, years, bhav_date, close_price, running_peak,
close_price/NULLIF(LAG(close_price) OVER (PARTITION BY symbol,years ORDER BY bhav_date),0)-1
AS daily_ret
FROM daily
),
agg AS (
SELECT symbol, years,
ROUND(MIN((close_price-running_peak)/NULLIF(running_peak,0)*100),2) AS max_drawdown_pct,
ROUND(STDDEV(daily_ret)*SQRT(252)*100,2) AS volatility_ann_pct,
COUNT(DISTINCT bhav_date) AS trading_days, AVG(close_price) AS avg_price
FROM rets GROUP BY symbol, years
)
SELECT symbol, years, max_drawdown_pct, volatility_ann_pct, trading_days
FROM agg
WHERE trading_days>=200 -- at least ~10 months
AND avg_price>=10 -- excludes penny stocks and commodity ETFs
AND volatility_ann_pct<=200 -- excludes pathological instruments
AND max_drawdown_pct>=-95; -- excludes near-zero wind-ups
CREATE OR REPLACE VIEW wksp_stocktrade.ai_stock_drawdown AS
SELECT * FROM wksp_stocktrade.ai_stock_drawdown_mv;
Scheduler jobs
BEGIN
FOR j IN (SELECT job_name FROM all_scheduler_jobs WHERE owner='WKSP_STOCKTRADE'
AND job_name IN ('REFRESH_STOCK_CAGR','REFRESH_MF_CAGR','REFRESH_STOCK_DRAWDOWN'))
LOOP DBMS_SCHEDULER.DROP_JOB('WKSP_STOCKTRADE.'||j.job_name, force=>TRUE); END LOOP;
DBMS_SCHEDULER.CREATE_JOB(job_name=>'WKSP_STOCKTRADE.REFRESH_STOCK_CAGR',
job_type=>'PLSQL_BLOCK',
job_action=>'DBMS_MVIEW.REFRESH(''WKSP_STOCKTRADE.AI_STOCK_CAGR_MV'',''C'');',
start_date=>TRUNC(SYSDATE+1)+6/24, repeat_interval=>'FREQ=DAILY;BYHOUR=6;BYMINUTE=0',
enabled=>TRUE, comments=>'After NSE bhavcopy loader');
DBMS_SCHEDULER.CREATE_JOB(job_name=>'WKSP_STOCKTRADE.REFRESH_MF_CAGR',
job_type=>'PLSQL_BLOCK',
job_action=>'DBMS_MVIEW.REFRESH(''WKSP_STOCKTRADE.AI_MF_CAGR_MV'',''C'');',
start_date=>TRUNC(SYSDATE+1)+7/24, repeat_interval=>'FREQ=DAILY;BYHOUR=7;BYMINUTE=0',
enabled=>TRUE, comments=>'After AMFI NAV loader');
DBMS_SCHEDULER.CREATE_JOB(job_name=>'WKSP_STOCKTRADE.REFRESH_STOCK_DRAWDOWN',
job_type=>'PLSQL_BLOCK',
job_action=>'DBMS_MVIEW.REFRESH(''WKSP_STOCKTRADE.AI_STOCK_DRAWDOWN_MV'',''C'');',
start_date=>TRUNC(SYSDATE+1)+7.5/24, repeat_interval=>'FREQ=DAILY;BYHOUR=7;BYMINUTE=30',
enabled=>TRUE, comments=>'Heaviest job, runs last');
END;
/
Comments — the Database Actions pipe issue
COMMENT ON TABLE ... IS 'text1'||'text2' fails in Database Actions because || is misread as a SQL operator before Oracle sees the statement. Fix: wrap in BEGIN EXECUTE IMMEDIATE. Note doubled single quotes '' inside the string.
BEGIN
EXECUTE IMMEDIATE 'COMMENT ON TABLE wksp_stocktrade.ai_stock_cagr IS
''CAGR for all NSE EQ stocks, adjusted for splits and bonuses.
One row per symbol per horizon: years IN (1,3,5,10).
NEVER recompute CAGR from raw prices. ALWAYS use this view.
Do NOT use for: current price (ai_stock_snapshot), MF returns (ai_mf_cagr).''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN wksp_stocktrade.ai_stock_cagr.cagr_pct IS
''Annualised return in percent. Adjusted for all BONUS and SPLIT corporate actions.
Negative = stock is below where it was years ago.''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN wksp_stocktrade.ai_stock_cagr.years IS
''Measurement horizon. Values: 1, 3, 5, or 10. Filter: WHERE years = 5''';
EXECUTE IMMEDIATE 'COMMENT ON COLUMN wksp_stocktrade.ai_stock_cagr.total_adj_factor IS
''Geometric product of split/bonus adj_factors in the window.
1=no adjustments. 0.5=one 1:1 bonus or 1:2 split. 0.25=two such events.
Rounded to 6 decimal places to eliminate floating-point noise.''';
EXECUTE IMMEDIATE 'COMMENT ON TABLE wksp_stocktrade.ai_mf_cagr IS
''CAGR for eligible MF schemes, Direct+Growth canonical variant only.
MF NAVs are already total-return: no corporate-action adjustment needed.
One row per base_fund_key per horizon: years IN (1,3,5,10).
NEVER compute MF CAGR from raw NAV. ALWAYS use this view.''';
EXECUTE IMMEDIATE 'COMMENT ON TABLE wksp_stocktrade.ai_stock_snapshot IS
''Current price for all NSE EQ stocks, most recent trading day.
Use for: current price, day change, 52-week range.
Do NOT use for: historical prices (ai_nse_price_daily), CAGR (ai_stock_cagr).''';
EXECUTE IMMEDIATE 'COMMENT ON TABLE wksp_stocktrade.ai_stock_drawdown IS
''Max drawdown and annualised volatility for liquid NSE EQ stocks.
Filtered: avg_price>=10, trading_days>=200, drawdown>=-95%.
Do NOT use for: CAGR (ai_stock_cagr), current price (ai_stock_snapshot).''';
DBMS_OUTPUT.PUT_LINE('Comments created successfully.');
END;
/
Business glossary
BEGIN
DELETE FROM wksp_stocktrade.ai_glossary;
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('CAGR',
'compound annual growth rate,annualized return,annual return,returns,performance,growth rate',
'Compound Annual Growth Rate. For stocks: adjusted for splits/bonuses using '||
'EXP(SUM(LN(adj_factor))). For MF: no adjustment needed, NAV is total-return. '||
'Formula: (end/adj_start)^(1/years) - 1.',
'SELECT cagr_pct FROM wksp_stocktrade.ai_stock_cagr WHERE symbol=:s AND years=:n',
'AI_STOCK_CAGR',
'NEVER compute from raw prices. ALWAYS use ai_stock_cagr or ai_mf_cagr.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('MAX_DRAWDOWN',
'worst loss,maximum loss,drawdown,peak to trough,biggest drop',
'Largest percentage decline from a peak to a subsequent trough. Always negative. '||
'-30 means the stock fell 30% from its highest point. Computed on daily closes.',
'SELECT max_drawdown_pct FROM wksp_stocktrade.ai_stock_drawdown WHERE symbol=:s AND years=:n',
'AI_STOCK_DRAWDOWN', 'Always negative or zero. Compare within same asset class only.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('VOLATILITY',
'risk,standard deviation,fluctuation,how risky,stability,consistency',
'Annualised standard deviation of daily returns: STDDEV(daily_return)*SQRT(252). '||
'Higher = more risky. 25% means daily std dev is roughly 1.6%.',
'SELECT volatility_ann_pct FROM wksp_stocktrade.ai_stock_drawdown WHERE symbol=:s AND years=:n',
'AI_STOCK_DRAWDOWN', 'Uses 252 trading days per year. Compare within same asset class.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('NAV',
'net asset value,fund price,mutual fund price,unit price,fund nav',
'Net Asset Value of a MF unit. Declared daily after market close. '||
'Already total-return: incorporates all dividends reinvested. '||
'Never compare NAV levels across funds; compare CAGR instead.',
'SELECT current_nav, nav_date FROM wksp_stocktrade.ai_mf_snapshot WHERE scheme_code=:c',
'AI_MF_SNAPSHOT', 'Never compare NAV levels. Use CAGR for performance comparison.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('ELIGIBLE_FUND',
'comparable fund,fund with enough history,similarity universe,eligible scheme',
'MF scheme with is_eligible=Y: at least 1000 NAV observations AND no gap >7 days '||
'within the trailing 5 years. ~5,553 eligible schemes of 14,200 total.',
'SELECT * FROM wksp_stocktrade.ai_mf_scheme WHERE is_eligible=''Y''',
'AI_MF_SCHEME', 'Use is_eligible=Y whenever computing statistics or similarity.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('ADJ_FACTOR',
'split factor,bonus factor,corporate action factor,price adjustment,adjustment',
'Multiplier applied to historical prices for splits and bonus issues. '||
'1:1 bonus gives adj_factor=0.5 (price halves). '||
'Geometric product EXP(SUM(LN(adj_factor))) gives cumulative adjustment.',
'SELECT total_adj_factor FROM wksp_stocktrade.ai_stock_cagr WHERE symbol=:s AND years=:n',
'AI_STOCK_CAGR', 'adj_factor=1 means no corporate action. Values <1 are splits/bonuses.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('BASE_FUND',
'fund family,fund variants,canonical fund,underlying fund',
'A mutual fund ignoring plan and option variants. '||
'base_fund_key groups all Direct/Regular x Growth/IDCW variants of the same portfolio. '||
'Statistics computed on the Direct+Growth canonical variant only.',
'SELECT DISTINCT base_fund_key FROM wksp_stocktrade.ai_mf_scheme WHERE is_eligible=''Y''',
'AI_MF_SCHEME', 'Never compare Direct and Regular variants directly.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('CURRENT_PRICE',
'latest price,today price,price now,market price,close,last traded price',
'Most recent closing price from NSE bhavcopy. Reflects previous trading day close. '||
'price_date column tells you which day it is from.',
'SELECT current_price, price_date FROM wksp_stocktrade.ai_stock_snapshot WHERE symbol=:s',
'AI_STOCK_SNAPSHOT', 'Markets closed weekends/holidays so price_date may not be today.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('CAGR_MF',
'mutual fund returns,fund performance,MF cagr,fund cagr,fund returns',
'CAGR for mutual funds from ai_mf_cagr. Direct+Growth canonical variant only. '||
'No corporate action adjustment: NAV is already total-return.',
'SELECT cagr_pct FROM wksp_stocktrade.ai_mf_cagr WHERE UPPER(scheme_name) LIKE :name AND years=:n',
'AI_MF_CAGR', 'Always use Direct+Growth for fair comparison across funds.');
INSERT INTO wksp_stocktrade.ai_glossary VALUES ('SHARPE_RATIO',
'risk adjusted return,sharpe,return per unit risk',
'Risk-adjusted return: (CAGR_5y - 6.5%) / volatility_ann. '||
'Higher Sharpe = better return per unit risk. NULL for overnight/liquid funds.',
'SELECT sharpe_ratio FROM wksp_stocktrade.mf_scheme_stats WHERE scheme_code=:c AND as_of_date=TRUNC(SYSDATE)',
'MF_SCHEME_STATS', 'Compare only within same asset class.');
COMMIT;
DBMS_OUTPUT.PUT_LINE('Glossary: 10 terms populated.');
END;
/
Week 3 verification
-- V1: TCS CAGR and adj_factor
SELECT symbol, company_name, years, cagr_pct, total_adj_factor
FROM wksp_stocktrade.ai_stock_cagr WHERE symbol='TCS' AND years=5;
-- Expected: cagr_pct=-5.98, total_adj_factor=1
-- V2: HDFCBANK all horizons — confirms adj_factor logic
SELECT symbol, years, cagr_pct, total_adj_factor
FROM wksp_stocktrade.ai_stock_cagr WHERE symbol='HDFCBANK' ORDER BY years;
-- Expected: 0.5 for 1/3/5yr, 0.25 for 10yr
-- V3: Top 5 large-cap MFs
SELECT scheme_name, amc_name, cagr_pct FROM wksp_stocktrade.ai_mf_cagr
WHERE category='EQ_LARGE_CAP' AND years=5 ORDER BY cagr_pct DESC FETCH FIRST 5 ROWS ONLY;
-- Expected: Nippon India Large Cap ~15.78%, ICICI Pru Large Cap ~13.68%
-- V4: Worst drawdown (must NOT show gold ETFs)
SELECT symbol, max_drawdown_pct, volatility_ann_pct
FROM wksp_stocktrade.ai_stock_drawdown WHERE years=1
ORDER BY max_drawdown_pct FETCH FIRST 10 ROWS ONLY;
-- V5: Scheduler jobs
SELECT job_name, enabled, next_run_date FROM all_scheduler_jobs
WHERE owner='WKSP_STOCKTRADE'
AND job_name IN ('REFRESH_STOCK_CAGR','REFRESH_MF_CAGR','REFRESH_STOCK_DRAWDOWN');
WEEK 4: FUND FEATURE VECTORS AND ENTITY RESOLUTION
Two vector types — completely different purposes
Feature vectors VECTOR(15, FLOAT32) — Euclidean distance: Answers “which funds behave like this fund?” Input: scheme_code. The 15 standardised financial statistics tell you how similar two funds’ risk-return profiles are.
Text embeddings VECTOR(384, FLOAT32) — cosine distance: Answers “which fund did the user mean?” Input: fuzzy text like “parag parikh flexi”. Semantic meaning determines closeness, not character similarity.
Without feature vectors: cannot find similar funds. Without text embeddings: user must type exact scheme names — no one does.
Why Euclidean, not cosine, for feature vectors
Cosine measures only the angle between vectors — ignores magnitude. A fund with CAGR=20%, vol=30% (aggressive) points in the same direction as CAGR=4%, vol=6% (conservative) if their ratios match. Cosine calls them similar. They are not. Euclidean sees them as far apart. Rule: use Euclidean when magnitude carries information. Use cosine when only semantic direction matters.
Why standardisation (z-scoring) is mandatory
Raw features have wildly different scales. Without standardisation, a 10-point drawdown difference (100 in squared distance) completely dominates a 1.5-point Sharpe difference (2.25). Drawdown would determine all similarity results.
Z-score: z = (raw - universe_mean) / universe_stddev
After standardisation, 1-unit = “1 standard deviation from average” in every feature.
The train/serve-skew trap — the most critical concept of Week 4
The query vector for “funds similar to X” must be standardised with the exact same mean and stddev used to build the stored vectors. Recomputing scaling parameters at query time on current data produces silently wrong distances — no error, no warning.
This is why mf_feature_scaling stores mean/stddev keyed by as_of_date. Never recompute at query time. Always join to the stored scaling parameters.
Daily returns table
CREATE TABLE wksp_stocktrade.mf_daily_return (
scheme_code NUMBER NOT NULL,
nav_date DATE NOT NULL,
nav_value NUMBER NOT NULL,
daily_return NUMBER(12,8), -- NULL on first obs, gap >7d, or |return|>50%
gap_days NUMBER,
CONSTRAINT mf_daily_return_pk PRIMARY KEY (scheme_code, nav_date)
) ORGANIZATION INDEX; -- IOT: fast range scans by (scheme_code, nav_date)
INSERT /*+ APPEND */ INTO wksp_stocktrade.mf_daily_return
(scheme_code, nav_date, nav_value, daily_return, gap_days)
WITH src AS (
SELECT n.scheme_code, n.nav_date, n.nav_value,
n.nav_date - LAG(n.nav_date) OVER (PARTITION BY n.scheme_code ORDER BY n.nav_date) AS gap_days,
n.nav_value / NULLIF(LAG(n.nav_value) OVER (PARTITION BY n.scheme_code ORDER BY n.nav_date),0) - 1
AS raw_return
FROM wksp_stockdata.mf_nav_history n
JOIN wksp_stocktrade.ai_mf_canonical c ON c.scheme_code=n.scheme_code
)
SELECT scheme_code, nav_date, nav_value,
CASE WHEN gap_days IS NULL OR gap_days > 7 THEN NULL -- missing data
WHEN raw_return > 0.5 THEN NULL -- NFO launch artefact
WHEN raw_return < -0.5 THEN NULL -- wind-up artefact
ELSE raw_return END AS daily_return,
gap_days
FROM src;
COMMIT;
-- Clean any artefacts that slipped through
UPDATE wksp_stocktrade.mf_daily_return SET daily_return=NULL WHERE daily_return > 0.5;
UPDATE wksp_stocktrade.mf_daily_return SET daily_return=NULL WHERE daily_return < -0.5;
COMMIT;
NFO artefact finding: 10 schemes showed returns of 99x. Overnight/liquid funds launched with face-value NAV of ₹10 but operating NAV of ₹1,000+. The formula sees 1000/10 - 1 = 99. Not a data error — a known AMFI reporting artefact. Filter |return| > 0.5 removes them cleanly.
Test statistics on one fund before full run
Always test on a known fund first. This query also demonstrates the ORA-00978 fix.
-- Expected: vol ~11.65%, drawdown ~-17.87%, positive days ~56.52%
WITH ret5 AS (
SELECT scheme_code, nav_date, daily_return,
MAX(nav_value) OVER (PARTITION BY scheme_code ORDER BY nav_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_peak,
nav_value
FROM wksp_stocktrade.mf_daily_return
WHERE scheme_code=122639 AND nav_date>=ADD_MONTHS(TRUNC(SYSDATE),-60)
AND daily_return IS NOT NULL
),
-- ORA-00978 fix: pre-compute mean/stddev to avoid nesting aggregates
-- Cannot write SUM(POWER(x - AVG(x), 3)) — AVG() inside SUM() is ORA-00978
pre AS (
SELECT AVG(daily_return) AS mean_ret, STDDEV(daily_return) AS std_ret, COUNT(*) AS n
FROM ret5
)
SELECT p.n AS obs_count,
ROUND(p.std_ret*SQRT(252)*100,2) AS volatility_ann,
ROUND((SELECT STDDEV(CASE WHEN r.daily_return<0 THEN r.daily_return END)
FROM ret5 r)*SQRT(252)*100,2) AS downside_dev,
ROUND((SELECT MIN((r.nav_value-r.running_peak)/NULLIF(r.running_peak,0)*100)
FROM ret5 r),2) AS max_drawdown_pct,
ROUND((SELECT SUM(CASE WHEN r.daily_return>0 THEN 1 ELSE 0 END)/NULLIF(COUNT(*),0)*100
FROM ret5 r),2) AS pct_positive_days,
ROUND((SELECT SUM(POWER(r.daily_return-p.mean_ret,3)) FROM ret5 r)
/NULLIF(p.n*POWER(p.std_ret,3),0),4) AS return_skewness,
ROUND((SELECT SUM(POWER(r.daily_return-p.mean_ret,4)) FROM ret5 r)
/NULLIF(p.n*POWER(p.std_ret,4),0)-3,4) AS return_kurtosis
FROM pre p;
Feature statistics table and full population
CREATE TABLE wksp_stocktrade.mf_scheme_stats (
scheme_code NUMBER NOT NULL, as_of_date DATE NOT NULL,
cagr_1y NUMBER, cagr_3y NUMBER, cagr_5y NUMBER,
volatility_ann NUMBER, downside_dev NUMBER,
max_drawdown_pct NUMBER, drawdown_days NUMBER,
sharpe_ratio NUMBER, sortino_ratio NUMBER, calmar_ratio NUMBER,
pct_positive_days NUMBER, roll12_mean NUMBER, roll12_stdev NUMBER,
return_skewness NUMBER, return_kurtosis NUMBER, obs_count NUMBER,
beta NUMBER, alpha NUMBER, r_squared NUMBER,
up_capture NUMBER, down_capture NUMBER,
CONSTRAINT mf_scheme_stats_pk PRIMARY KEY (scheme_code, as_of_date)
);
-- Full population (10-20 min on 1 OCPU)
INSERT INTO wksp_stocktrade.mf_scheme_stats
(scheme_code,as_of_date,cagr_1y,cagr_3y,cagr_5y,volatility_ann,downside_dev,
max_drawdown_pct,sharpe_ratio,sortino_ratio,calmar_ratio,pct_positive_days,
roll12_mean,roll12_stdev,return_skewness,return_kurtosis,obs_count)
WITH
cagr AS (
SELECT scheme_code,
MAX(CASE WHEN years=1 THEN cagr_pct END) cagr_1y,
MAX(CASE WHEN years=3 THEN cagr_pct END) cagr_3y,
MAX(CASE WHEN years=5 THEN cagr_pct END) cagr_5y
FROM wksp_stocktrade.ai_mf_cagr_mv GROUP BY scheme_code
),
ret5 AS (
SELECT scheme_code, nav_date, daily_return,
MAX(nav_value) OVER (PARTITION BY scheme_code ORDER BY nav_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_peak,
nav_value
FROM wksp_stocktrade.mf_daily_return
WHERE nav_date>=ADD_MONTHS(TRUNC(SYSDATE),-60) AND daily_return IS NOT NULL
),
moments AS (
-- Pre-compute per scheme to avoid ORA-00978 (nested group function)
SELECT scheme_code, COUNT(*) n, AVG(daily_return) mean_ret, STDDEV(daily_return) std_ret
FROM ret5 GROUP BY scheme_code
),
stats2 AS (
SELECT r.scheme_code, m.n AS obs_count,
ROUND(m.std_ret*SQRT(252)*100,4) AS volatility_ann,
ROUND(STDDEV(CASE WHEN r.daily_return<0 THEN r.daily_return END)*SQRT(252)*100,4) AS downside_dev,
ROUND(MIN((r.nav_value-r.running_peak)/NULLIF(r.running_peak,0)*100),4) AS max_drawdown_pct,
ROUND(SUM(CASE WHEN r.daily_return>0 THEN 1 ELSE 0 END)/NULLIF(m.n,0)*100,4) AS pct_positive_days,
ROUND(SUM(POWER(r.daily_return-m.mean_ret,3))/NULLIF(m.n*POWER(m.std_ret,3),0),4) AS return_skewness,
ROUND(SUM(POWER(r.daily_return-m.mean_ret,4))/NULLIF(m.n*POWER(m.std_ret,4),0)-3,4) AS return_kurtosis
FROM ret5 r JOIN moments m ON m.scheme_code=r.scheme_code
GROUP BY r.scheme_code, m.n, m.mean_ret, m.std_ret
),
roll12 AS (
SELECT scheme_code,
ROUND(AVG(r12),4) roll12_mean, ROUND(STDDEV(r12),4) roll12_stdev
FROM (
SELECT scheme_code,
EXP(SUM(LN(1+daily_return)) OVER (PARTITION BY scheme_code ORDER BY nav_date
ROWS BETWEEN 251 PRECEDING AND CURRENT ROW))-1 AS r12,
COUNT(*) OVER (PARTITION BY scheme_code ORDER BY nav_date
ROWS BETWEEN 251 PRECEDING AND CURRENT ROW) AS window_cnt
FROM wksp_stocktrade.mf_daily_return
WHERE nav_date>=ADD_MONTHS(TRUNC(SYSDATE),-60) AND daily_return IS NOT NULL
)
WHERE window_cnt=252 GROUP BY scheme_code
)
SELECT s.scheme_code, TRUNC(SYSDATE),
c.cagr_1y, c.cagr_3y, c.cagr_5y,
s.volatility_ann, s.downside_dev, s.max_drawdown_pct,
ROUND((c.cagr_5y-6.5)/NULLIF(s.volatility_ann,0),4) AS sharpe_ratio,
ROUND((c.cagr_5y-6.5)/NULLIF(s.downside_dev,0),4) AS sortino_ratio,
ROUND(c.cagr_5y/NULLIF(ABS(s.max_drawdown_pct),0),4) AS calmar_ratio,
s.pct_positive_days, r.roll12_mean, r.roll12_stdev,
s.return_skewness, s.return_kurtosis, s.obs_count
FROM stats2 s JOIN cagr c ON c.scheme_code=s.scheme_code
LEFT JOIN roll12 r ON r.scheme_code=s.scheme_code;
COMMIT;
-- Clean extreme values from overnight/liquid funds (near-zero volatility)
UPDATE wksp_stocktrade.mf_scheme_stats
SET sharpe_ratio=NULL, sortino_ratio=NULL
WHERE volatility_ann<0.5 OR ABS(sharpe_ratio)>20 OR ABS(sortino_ratio)>20;
UPDATE wksp_stocktrade.mf_scheme_stats
SET volatility_ann=NULL, downside_dev=NULL,
sharpe_ratio=NULL, sortino_ratio=NULL, calmar_ratio=NULL
WHERE volatility_ann<0.5 OR volatility_ann IS NULL;
COMMIT;
-- Result: 1,777 rows inserted, 1,527 with valid volatility, 1,522 with valid Sharpe
Feature scaling table
CREATE TABLE wksp_stocktrade.mf_feature_scaling (
as_of_date DATE NOT NULL, feature_name VARCHAR2(40) NOT NULL,
feature_pos NUMBER NOT NULL, mean_val NUMBER, stddev_val NUMBER,
weight NUMBER DEFAULT 1.0,
CONSTRAINT mf_feature_scaling_pk PRIMARY KEY (as_of_date, feature_name)
);
-- Run as WKSP_STOCKTRADE (simpler without schema prefix)
-- Weights: 1.5=highest priority, 0.5=lowest. Tuned further in Week 12.
DECLARE
m01 NUMBER; s01 NUMBER; m02 NUMBER; s02 NUMBER; m03 NUMBER; s03 NUMBER;
m04 NUMBER; s04 NUMBER; m05 NUMBER; s05 NUMBER; m06 NUMBER; s06 NUMBER;
m07 NUMBER; s07 NUMBER; m08 NUMBER; s08 NUMBER; m09 NUMBER; s09 NUMBER;
m10 NUMBER; s10 NUMBER; m11 NUMBER; s11 NUMBER; m12 NUMBER; s12 NUMBER;
m13 NUMBER; s13 NUMBER; m14 NUMBER; s14 NUMBER; m15 NUMBER; s15 NUMBER;
v_date DATE := TRUNC(SYSDATE);
BEGIN
SELECT AVG(cagr_1y),STDDEV(cagr_1y),AVG(cagr_3y),STDDEV(cagr_3y),
AVG(cagr_5y),STDDEV(cagr_5y),AVG(volatility_ann),STDDEV(volatility_ann),
AVG(downside_dev),STDDEV(downside_dev),AVG(max_drawdown_pct),STDDEV(max_drawdown_pct),
AVG(sharpe_ratio),STDDEV(sharpe_ratio),AVG(sortino_ratio),STDDEV(sortino_ratio),
AVG(calmar_ratio),STDDEV(calmar_ratio),AVG(pct_positive_days),STDDEV(pct_positive_days),
AVG(roll12_mean),STDDEV(roll12_mean),AVG(roll12_stdev),STDDEV(roll12_stdev),
AVG(return_skewness),STDDEV(return_skewness),AVG(return_kurtosis),STDDEV(return_kurtosis),
AVG(obs_count),STDDEV(obs_count)
INTO m01,s01,m02,s02,m03,s03,m04,s04,m05,s05,m06,s06,m07,s07,m08,s08,
m09,s09,m10,s10,m11,s11,m12,s12,m13,s13,m14,s14,m15,s15
FROM mf_scheme_stats WHERE as_of_date=v_date;
DELETE FROM mf_feature_scaling WHERE as_of_date=v_date;
INSERT INTO mf_feature_scaling VALUES (v_date,'CAGR_1Y', 1, m01,s01,1.0);
INSERT INTO mf_feature_scaling VALUES (v_date,'CAGR_3Y', 2, m02,s02,1.2);
INSERT INTO mf_feature_scaling VALUES (v_date,'CAGR_5Y', 3, m03,s03,1.5);
INSERT INTO mf_feature_scaling VALUES (v_date,'VOLATILITY_ANN', 4, m04,s04,1.5);
INSERT INTO mf_feature_scaling VALUES (v_date,'DOWNSIDE_DEV', 5, m05,s05,1.2);
INSERT INTO mf_feature_scaling VALUES (v_date,'MAX_DRAWDOWN', 6, m06,s06,1.5);
INSERT INTO mf_feature_scaling VALUES (v_date,'SHARPE_RATIO', 7, m07,s07,1.2);
INSERT INTO mf_feature_scaling VALUES (v_date,'SORTINO_RATIO', 8, m08,s08,1.0);
INSERT INTO mf_feature_scaling VALUES (v_date,'CALMAR_RATIO', 9, m09,s09,1.0);
INSERT INTO mf_feature_scaling VALUES (v_date,'PCT_POSITIVE_DAYS',10,m10,s10,0.8);
INSERT INTO mf_feature_scaling VALUES (v_date,'ROLL12_MEAN', 11,m11,s11,1.0);
INSERT INTO mf_feature_scaling VALUES (v_date,'ROLL12_STDEV', 12,m12,s12,0.8);
INSERT INTO mf_feature_scaling VALUES (v_date,'RETURN_SKEWNESS', 13,m13,s13,0.7);
INSERT INTO mf_feature_scaling VALUES (v_date,'RETURN_KURTOSIS', 14,m14,s14,0.7);
INSERT INTO mf_feature_scaling VALUES (v_date,'OBS_COUNT', 15,m15,s15,0.5);
COMMIT;
END;
/
Feature vector table and population
CREATE TABLE wksp_stocktrade.mf_scheme_feature (
scheme_code NUMBER NOT NULL, as_of_date DATE NOT NULL,
feature_vec VECTOR(15, FLOAT32),
CONSTRAINT mf_scheme_feature_pk PRIMARY KEY (scheme_code, as_of_date)
);
-- Pivot scaling params, z-score each feature, clamp to [-5,5], pack into vector
-- GREATEST/LEAST: prevents catastrophic outliers from dominating distance
-- NVL(...,0): NULL features (e.g. missing Sharpe) contribute zero
INSERT INTO wksp_stocktrade.mf_scheme_feature (scheme_code, as_of_date, feature_vec)
WITH sc AS (
SELECT MAX(CASE WHEN feature_name='CAGR_1Y' THEN mean_val END) m01,
MAX(CASE WHEN feature_name='CAGR_1Y' THEN stddev_val END) s01,
MAX(CASE WHEN feature_name='CAGR_1Y' THEN weight END) w01,
MAX(CASE WHEN feature_name='CAGR_3Y' THEN mean_val END) m02,
MAX(CASE WHEN feature_name='CAGR_3Y' THEN stddev_val END) s02,
MAX(CASE WHEN feature_name='CAGR_3Y' THEN weight END) w02,
MAX(CASE WHEN feature_name='CAGR_5Y' THEN mean_val END) m03,
MAX(CASE WHEN feature_name='CAGR_5Y' THEN stddev_val END) s03,
MAX(CASE WHEN feature_name='CAGR_5Y' THEN weight END) w03,
MAX(CASE WHEN feature_name='VOLATILITY_ANN' THEN mean_val END) m04,
MAX(CASE WHEN feature_name='VOLATILITY_ANN' THEN stddev_val END) s04,
MAX(CASE WHEN feature_name='VOLATILITY_ANN' THEN weight END) w04,
MAX(CASE WHEN feature_name='DOWNSIDE_DEV' THEN mean_val END) m05,
MAX(CASE WHEN feature_name='DOWNSIDE_DEV' THEN stddev_val END) s05,
MAX(CASE WHEN feature_name='DOWNSIDE_DEV' THEN weight END) w05,
MAX(CASE WHEN feature_name='MAX_DRAWDOWN' THEN mean_val END) m06,
MAX(CASE WHEN feature_name='MAX_DRAWDOWN' THEN stddev_val END) s06,
MAX(CASE WHEN feature_name='MAX_DRAWDOWN' THEN weight END) w06,
MAX(CASE WHEN feature_name='SHARPE_RATIO' THEN mean_val END) m07,
MAX(CASE WHEN feature_name='SHARPE_RATIO' THEN stddev_val END) s07,
MAX(CASE WHEN feature_name='SHARPE_RATIO' THEN weight END) w07,
MAX(CASE WHEN feature_name='SORTINO_RATIO' THEN mean_val END) m08,
MAX(CASE WHEN feature_name='SORTINO_RATIO' THEN stddev_val END) s08,
MAX(CASE WHEN feature_name='SORTINO_RATIO' THEN weight END) w08,
MAX(CASE WHEN feature_name='CALMAR_RATIO' THEN mean_val END) m09,
MAX(CASE WHEN feature_name='CALMAR_RATIO' THEN stddev_val END) s09,
MAX(CASE WHEN feature_name='CALMAR_RATIO' THEN weight END) w09,
MAX(CASE WHEN feature_name='PCT_POSITIVE_DAYS' THEN mean_val END) m10,
MAX(CASE WHEN feature_name='PCT_POSITIVE_DAYS' THEN stddev_val END) s10,
MAX(CASE WHEN feature_name='PCT_POSITIVE_DAYS' THEN weight END) w10,
MAX(CASE WHEN feature_name='ROLL12_MEAN' THEN mean_val END) m11,
MAX(CASE WHEN feature_name='ROLL12_MEAN' THEN stddev_val END) s11,
MAX(CASE WHEN feature_name='ROLL12_MEAN' THEN weight END) w11,
MAX(CASE WHEN feature_name='ROLL12_STDEV' THEN mean_val END) m12,
MAX(CASE WHEN feature_name='ROLL12_STDEV' THEN stddev_val END) s12,
MAX(CASE WHEN feature_name='ROLL12_STDEV' THEN weight END) w12,
MAX(CASE WHEN feature_name='RETURN_SKEWNESS' THEN mean_val END) m13,
MAX(CASE WHEN feature_name='RETURN_SKEWNESS' THEN stddev_val END) s13,
MAX(CASE WHEN feature_name='RETURN_SKEWNESS' THEN weight END) w13,
MAX(CASE WHEN feature_name='RETURN_KURTOSIS' THEN mean_val END) m14,
MAX(CASE WHEN feature_name='RETURN_KURTOSIS' THEN stddev_val END) s14,
MAX(CASE WHEN feature_name='RETURN_KURTOSIS' THEN weight END) w14,
MAX(CASE WHEN feature_name='OBS_COUNT' THEN mean_val END) m15,
MAX(CASE WHEN feature_name='OBS_COUNT' THEN stddev_val END) s15,
MAX(CASE WHEN feature_name='OBS_COUNT' THEN weight END) w15
FROM wksp_stocktrade.mf_feature_scaling WHERE as_of_date=TRUNC(SYSDATE)
),
zscores AS (
SELECT s.scheme_code, s.as_of_date,
GREATEST(-5,LEAST(5,NVL((s.cagr_1y -sc.m01)/NULLIF(sc.s01,0),0)*sc.w01)) f01,
GREATEST(-5,LEAST(5,NVL((s.cagr_3y -sc.m02)/NULLIF(sc.s02,0),0)*sc.w02)) f02,
GREATEST(-5,LEAST(5,NVL((s.cagr_5y -sc.m03)/NULLIF(sc.s03,0),0)*sc.w03)) f03,
GREATEST(-5,LEAST(5,NVL((s.volatility_ann -sc.m04)/NULLIF(sc.s04,0),0)*sc.w04)) f04,
GREATEST(-5,LEAST(5,NVL((s.downside_dev -sc.m05)/NULLIF(sc.s05,0),0)*sc.w05)) f05,
GREATEST(-5,LEAST(5,NVL((s.max_drawdown_pct-sc.m06)/NULLIF(sc.s06,0),0)*sc.w06)) f06,
GREATEST(-5,LEAST(5,NVL((s.sharpe_ratio -sc.m07)/NULLIF(sc.s07,0),0)*sc.w07)) f07,
GREATEST(-5,LEAST(5,NVL((s.sortino_ratio -sc.m08)/NULLIF(sc.s08,0),0)*sc.w08)) f08,
GREATEST(-5,LEAST(5,NVL((s.calmar_ratio -sc.m09)/NULLIF(sc.s09,0),0)*sc.w09)) f09,
GREATEST(-5,LEAST(5,NVL((s.pct_positive_days-sc.m10)/NULLIF(sc.s10,0),0)*sc.w10)) f10,
GREATEST(-5,LEAST(5,NVL((s.roll12_mean -sc.m11)/NULLIF(sc.s11,0),0)*sc.w11)) f11,
GREATEST(-5,LEAST(5,NVL((s.roll12_stdev -sc.m12)/NULLIF(sc.s12,0),0)*sc.w12)) f12,
GREATEST(-5,LEAST(5,NVL((s.return_skewness -sc.m13)/NULLIF(sc.s13,0),0)*sc.w13)) f13,
GREATEST(-5,LEAST(5,NVL((s.return_kurtosis -sc.m14)/NULLIF(sc.s14,0),0)*sc.w14)) f14,
GREATEST(-5,LEAST(5,NVL((s.obs_count -sc.m15)/NULLIF(sc.s15,0),0)*sc.w15)) f15
FROM wksp_stocktrade.mf_scheme_stats s CROSS JOIN sc
WHERE s.as_of_date=TRUNC(SYSDATE)
)
SELECT scheme_code, as_of_date,
TO_VECTOR('['||f01||','||f02||','||f03||','||f04||','||f05||','||
f06||','||f07||','||f08||','||f09||','||f10||','||
f11||','||f12||','||f13||','||f14||','||f15||']',
15, FLOAT32) AS feature_vec
FROM zscores;
COMMIT;
-- Critical verification: self-distance must be exactly 0
SELECT ROUND(VECTOR_DISTANCE(a.feature_vec,b.feature_vec,EUCLIDEAN),6) self_dist
FROM wksp_stocktrade.mf_scheme_feature a
JOIN wksp_stocktrade.mf_scheme_feature b ON b.scheme_code=a.scheme_code AND b.as_of_date=a.as_of_date
WHERE a.scheme_code=122639 AND a.as_of_date=TRUNC(SYSDATE);
-- Must return: 0
Similarity query
-- Top 10 similar funds to Parag Parikh Flexi Cap, equity categories only
SELECT c.scheme_name, c.amc_name, c.category,
ROUND(VECTOR_DISTANCE(f.feature_vec,q.feature_vec,EUCLIDEAN),4) AS dist,
ROUND(s.cagr_5y,2) cagr_5y, ROUND(s.volatility_ann,2) vol,
ROUND(s.max_drawdown_pct,2) max_dd, ROUND(s.sharpe_ratio,4) sharpe
FROM wksp_stocktrade.mf_scheme_feature q
JOIN wksp_stocktrade.mf_scheme_feature f ON f.as_of_date=q.as_of_date
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=f.as_of_date
WHERE q.scheme_code=122639 AND q.as_of_date=TRUNC(SYSDATE)
AND f.scheme_code!=122639
AND c.category IN ('EQ_FLEXI_CAP','EQ_LARGE_CAP','EQ_LARGE_MID_CAP','EQ_MULTI_CAP')
ORDER BY dist FETCH FIRST 10 ROWS ONLY;
Results: ICICI Prudential Large Cap (dist 0.50), SBI Multicap (dist 0.66). Parag Parikh’s closest peers are large-cap funds — correct, since PPFAS holds 60–70% domestic large-cap + international, making it behave like a large-cap. The vectors discovered this from behaviour alone without being told.
ONNX model loading
-- Step 1: Download into DATA_PUMP_DIR (URL may rotate — try this first)
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'DATA_PUMP_DIR',
object_uri => 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/'||
'p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/'||
'n/adwc4pm/b/OML-Resources/o/all_MiniLM_L12_v2.onnx');
END;
/
-- Confirm file downloaded (check bytes > 0)
SELECT object_name, bytes FROM TABLE(DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR'))
WHERE object_name LIKE '%onnx%' OR object_name LIKE '%MiniLM%';
-- Step 2: Load (run as WKSP_STOCKTRADE)
BEGIN
BEGIN DBMS_VECTOR.DROP_ONNX_MODEL('ALL_MINILM_L12_V2',force=>TRUE);
EXCEPTION WHEN OTHERS THEN NULL; END;
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'DATA_PUMP_DIR',
file_name => 'all_MiniLM_L12_v2.onnx',
model_name => 'ALL_MINILM_L12_V2',
-- This metadata JSON is CRITICAL
-- Without it the model loads but VECTOR_EMBEDDING fails silently
metadata => JSON('{"function":"embedding",
"embeddingOutput":"embedding",
"input":{"input":["DATA"]}}'));
END;
/
-- Step 3: Confirm schema and function
SELECT owner, model_name, algorithm, mining_function
FROM all_mining_models WHERE model_name='ALL_MINILM_L12_V2';
-- Must show: OWNER=WKSP_STOCKTRADE
-- Step 4: Semantic test
SELECT
ROUND(VECTOR_DISTANCE(
VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2 USING 'Parag Parikh Flexi Cap' AS data),
VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2 USING 'PPFAS Flexi Cap Fund' AS data),
COSINE),4) AS similar_pair, -- expect ~0.45
ROUND(VECTOR_DISTANCE(
VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2 USING 'Parag Parikh Flexi Cap' AS data),
VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2 USING 'overnight liquid debt fund' AS data),
COSINE),4) AS dissimilar_pair -- expect ~0.83
FROM dual;
ONNX loading saga: Pre-authenticated URL expired on first attempt (ORA-20404). Model appeared to load (no error) but all_mining_models returned no rows — the file was 0 bytes because the URL rotated. Fix: use newer URL, confirm file size first. VECTOR_DIMENSION() function does not exist in this 26ai build — confirmed 384 dimensions by counting elements in the vector string output.
Entity resolution tables
CREATE TABLE wksp_stocktrade.ai_entity (
entity_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
entity_type VARCHAR2(20) NOT NULL,
entity_key VARCHAR2(100) NOT NULL,
display_text VARCHAR2(1000),
embed_text VARCHAR2(1000),
embedding VECTOR(384, FLOAT32),
CONSTRAINT ai_entity_uk UNIQUE (entity_type, entity_key)
);
-- MF embeddings: richer text = better resolution
-- scheme_name + amc_name + category gives more context than name alone
INSERT /*+ APPEND */ INTO wksp_stocktrade.ai_entity
(entity_type, entity_key, display_text, embed_text, embedding)
SELECT 'MF_SCHEME', TO_CHAR(c.scheme_code),
c.scheme_name || ' (' || c.amc_name || ')',
c.scheme_name || ' ' || c.amc_name || ' ' || NVL(c.category,''),
VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2
USING (c.scheme_name||' '||c.amc_name||' '||NVL(c.category,'')) AS data)
FROM wksp_stocktrade.ai_mf_canonical c;
COMMIT;
-- NSE embeddings: symbol + SECURITY (company name column in bhavcopy)
-- ORA-22848 fix: DISTINCT over VECTOR column fails — deduplicate BEFORE embedding
INSERT /*+ APPEND */ INTO wksp_stocktrade.ai_entity
(entity_type, entity_key, display_text, embed_text, embedding)
SELECT 'NSE_STOCK', p.symbol, p.symbol||' — '||p.security,
p.symbol||' '||p.security,
VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2
USING (p.symbol||' '||p.security) AS data)
FROM (
SELECT DISTINCT symbol, security -- deduplicate here, before VECTOR_EMBEDDING
FROM wksp_stockdata.nse_pd_bhavcopy_history
WHERE series='EQ'
AND bhav_date=(SELECT MAX(b.bhav_date)
FROM wksp_stockdata.nse_pd_bhavcopy_history b WHERE b.series='EQ')
) p;
COMMIT;
SELECT entity_type, COUNT(*) FROM wksp_stocktrade.ai_entity GROUP BY entity_type;
-- Result: MF_SCHEME=1,714 NSE_STOCK=2,412
-- Entity resolution tests
SELECT entity_key, display_text,
ROUND(VECTOR_DISTANCE(embedding,
VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2 USING 'parag parikh flexi' AS data),
COSINE),4) dist
FROM wksp_stocktrade.ai_entity WHERE entity_type='MF_SCHEME'
ORDER BY dist FETCH FIRST 5 ROWS ONLY;
-- Result: scheme 122639 at position 1, distance 0.4473 ✓
SELECT entity_key, display_text,
ROUND(VECTOR_DISTANCE(embedding,
VECTOR_EMBEDDING(wksp_stocktrade.ALL_MINILM_L12_V2 USING 'tata consultancy' AS data),
COSINE),4) dist
FROM wksp_stocktrade.ai_entity WHERE entity_type='NSE_STOCK'
ORDER BY dist FETCH FIRST 5 ROWS ONLY;
-- Result: TCS at position 1, distance 0.1923 ✓
-- Baseline hit rate: 3/4 at position 1, 4/4 in top 3
WEEK 5: SELECT AI BASELINE
What Select AI actually does
When you run SELECT AI runsql what is the 5 year cagr of tcs, Oracle:
- Reads metadata (names, types, COMMENT ON text) for views in the object list
- Assembles a prompt: schema metadata + your question
- Sends to the LLM
- Receives SQL back, executes it, returns rows
Select AI sends only schema metadata — no sample rows, no glossary, no example SQL. This is why your custom pipeline (Weeks 7–8) will beat it: you add glossary, few-shot examples, and retrieved context that Select AI lacks. The baseline shows the gap.
Provider selection — what actually worked
The journey to a working provider (documented for future reference):
OCI Generative AI — failed. ADB is in ca-toronto-1 (Canada Southeast). OCI Generative AI is not available in Toronto — available regions are us-chicago-1, us-ashburn-1, eu-frankfurt-1, ap-mumbai-1, ap-osaka-1, uk-london-1. Attempting cross-region via "region":"us-chicago-1" failed with ORA-20404 (object not found) because resource principal couldn’t resolve the tenancy domain cross-region without domain replication enabled. Domain replication is not available on Always Free tenancies.
OpenAI — failed on auth. Always Free ADB has a locked-down network ACL — you cannot add arbitrary external hosts. However OpenAI is pre-whitelisted by Oracle. The issue was ORA-20403 (authorization failed) because OpenAI now requires a payment method on file even to use free credits. Once a card was added the key worked — but by then Gemini was already set up.
Google Gemini — worked. Free tier, no credit card required, no ACL issues (Google APIs are pre-whitelisted on ADB), and the SQL quality from gemini-1.5-flash turned out to be excellent for structured output generation.
Key lesson: on Always Free ADB in Toronto, Google Gemini via aistudio.google.com is the easiest path to a working Select AI profile. Zero cost, no payment method, no regional constraints.
Setup: Gemini credential and profile
Step 1 — Get a free Gemini API key:
- Go to aistudio.google.com
- Sign in with Google account
- Click Get API key → Create API key
- Copy the key (starts with
AIza...) - Test it with curl before loading:
curl "https://generativelanguage.googleapis.com/v1beta/models?key=AIza..."
⚠️ Never paste the API key in any chat, document, or email. Load it directly into Database Actions only.
Step 2 — Create credential and profile (run as ADMIN):
-- Create Gemini credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GEMINI_CRED',
username => 'GEMINI', -- value ignored by Google, placeholder only
password => 'AIza...'); -- paste key directly in Database Actions
END;
/
-- Grant Select AI to WKSP_STOCKTRADE
GRANT EXECUTE ON DBMS_CLOUD_AI TO wksp_stocktrade;
-- Create profile
BEGIN
BEGIN DBMS_CLOUD_AI.DROP_PROFILE('PERFIN_GENAI');
EXCEPTION WHEN OTHERS THEN NULL; END;
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'PERFIN_GENAI',
attributes => '{
"provider": "google",
"credential_name": "GEMINI_CRED",
"model": "gemini-1.5-flash",
"comments": true,
"temperature": 0,
"object_list": [
{"owner":"WKSP_STOCKTRADE","name":"AI_STOCK_CAGR"},
{"owner":"WKSP_STOCKTRADE","name":"AI_MF_CAGR"},
{"owner":"WKSP_STOCKTRADE","name":"AI_STOCK_SNAPSHOT"},
{"owner":"WKSP_STOCKTRADE","name":"AI_MF_SNAPSHOT"},
{"owner":"WKSP_STOCKTRADE","name":"AI_NSE_PRICE_DAILY"},
{"owner":"WKSP_STOCKTRADE","name":"AI_NSE_CORPORATE_ACTION"},
{"owner":"WKSP_STOCKTRADE","name":"AI_MF_NAV_DAILY"},
{"owner":"WKSP_STOCKTRADE","name":"AI_MF_SCHEME"},
{"owner":"WKSP_STOCKTRADE","name":"AI_STOCK_DRAWDOWN"}
]
}'
);
DBMS_OUTPUT.PUT_LINE('Profile created');
END;
/
-- Verify
SELECT profile_name, status FROM user_cloud_ai_profiles;
temperature:0 — SQL generation needs no creativity. Always pick the most likely token. Higher temperature increases SQL syntax errors.
comments:true — the only prompt-engineering lever Select AI gives you. Every COMMENT ON TABLE and COMMENT ON COLUMN from Week 3 becomes part of the prompt. Your do_not_use_for comments are your only way to steer the model away from wrong views.
Step 3 — Set profile and smoke test:
-- Must run this at the start of every Database Actions session
EXEC DBMS_CLOUD_AI.SET_PROFILE('PERFIN_GENAI');
SELECT AI showsql what is the current price of TCS;
-- Expected: SELECT current_price FROM ai_stock_snapshot WHERE UPPER(symbol)='TCS'
SELECT AI runsql what is the current price of TCS;
-- Expected result: ₹2,452.7
Important — MV refresh before testing: The MVs (ai_mf_cagr_mv, ai_stock_cagr_mv) may have stale category/amc_name columns if they were built before enrichment ran. Refresh before running baseline questions:
EXEC DBMS_MVIEW.REFRESH('WKSP_STOCKTRADE.AI_MF_CAGR_MV','C');
EXEC DBMS_MVIEW.REFRESH('WKSP_STOCKTRADE.AI_STOCK_CAGR_MV','C');
EXEC DBMS_MVIEW.REFRESH('WKSP_STOCKTRADE.AI_STOCK_DRAWDOWN_MV','C');
-- Verify category is populated
SELECT category, COUNT(*) FROM wksp_stocktrade.ai_mf_cagr
WHERE years=5 AND category LIKE 'EQ%'
GROUP BY category ORDER BY 2 DESC;
20 baseline questions with reference SQL
Write reference SQL before running Select AI. Never write it to match what Select AI produced or you measure nothing.
-- Q1 LOOKUP: What is the current price of Reliance?
SELECT current_price, price_date, day_change_pct
FROM wksp_stocktrade.ai_stock_snapshot WHERE symbol='RELIANCE';
-- Q2 METRIC: What is the 5-year CAGR of TCS?
SELECT cagr_pct FROM wksp_stocktrade.ai_stock_cagr WHERE symbol='TCS' AND years=5;
-- Q3 AGGREGATE: Which large-cap MFs have the best 5-year returns?
SELECT scheme_name, amc_name, cagr_pct FROM wksp_stocktrade.ai_mf_cagr
WHERE category='EQ_LARGE_CAP' AND years=5
ORDER BY cagr_pct DESC FETCH FIRST 5 ROWS ONLY;
-- Q4 LOOKUP: Current NAV of Parag Parikh Flexi Cap Direct Growth?
SELECT scheme_name, current_nav, nav_date FROM wksp_stocktrade.ai_mf_snapshot
WHERE UPPER(scheme_name) LIKE '%PARAG PARIKH FLEXI%'
AND plan_type='DIRECT' AND option_type='GROWTH';
-- Q5 TIME: TCS closing price on 15-Jul-2023?
SELECT close_price FROM wksp_stocktrade.ai_nse_price_daily
WHERE symbol='TCS' AND price_date=DATE'2023-07-15';
-- Q6 METRIC: HDFC Bank 1-year drawdown?
SELECT max_drawdown_pct FROM wksp_stocktrade.ai_stock_drawdown
WHERE symbol='HDFCBANK' AND years=1;
-- Q7 LOOKUP: When did Infosys last give a bonus?
SELECT ex_date, action_type, action_value FROM wksp_stocktrade.ai_nse_corporate_action
WHERE symbol='INFY' AND action_type='BONUS' ORDER BY ex_date DESC FETCH FIRST 1 ROW ONLY;
-- Q8 AGGREGATE: How many eligible flexi-cap funds?
SELECT COUNT(DISTINCT base_fund_key) FROM wksp_stocktrade.ai_mf_scheme
WHERE category='EQ_FLEXI_CAP' AND is_eligible='Y';
-- Q9 METRIC: Reliance 5-year volatility?
SELECT volatility_ann_pct FROM wksp_stocktrade.ai_stock_drawdown
WHERE symbol='RELIANCE' AND years=5;
-- Q10 AGGREGATE: Lowest volatility flexi-cap fund?
SELECT c.scheme_name, s.volatility_ann FROM wksp_stocktrade.ai_mf_cagr c
JOIN wksp_stocktrade.mf_scheme_stats s ON s.scheme_code=c.scheme_code
WHERE c.category='EQ_FLEXI_CAP' AND c.years=5 AND s.as_of_date=TRUNC(SYSDATE)
ORDER BY s.volatility_ann FETCH FIRST 5 ROWS ONLY;
-- Q11 LOOKUP: HDFC Bank 52-week range?
SELECT symbol, week_52_high, week_52_low FROM wksp_stocktrade.ai_stock_snapshot
WHERE symbol='HDFCBANK';
-- Q12 TIME: Parag Parikh NAV on 23-Mar-2020 (COVID crash)?
SELECT nav_value FROM wksp_stocktrade.ai_mf_nav_daily
WHERE scheme_code=122639 AND nav_date=DATE'2020-03-23';
-- Q13 AGGREGATE: Best 1-year CAGR all equity funds?
SELECT scheme_name, amc_name, category, cagr_pct FROM wksp_stocktrade.ai_mf_cagr
WHERE category LIKE 'EQ%' AND years=1 ORDER BY cagr_pct DESC FETCH FIRST 10 ROWS ONLY;
-- Q14 METRIC: Parag Parikh Sharpe ratio?
SELECT cagr_5y, volatility_ann, sharpe_ratio FROM wksp_stocktrade.mf_scheme_stats
WHERE scheme_code=122639 AND as_of_date=TRUNC(SYSDATE);
-- Q15 LOOKUP: Total eligible MF schemes?
SELECT COUNT(*) FROM wksp_stocktrade.ai_mf_scheme WHERE is_eligible='Y';
-- Q16 AGGREGATE: Top HDFC equity funds 5-year CAGR?
SELECT scheme_name, cagr_pct FROM wksp_stocktrade.ai_mf_cagr
WHERE UPPER(amc_name) LIKE '%HDFC%' AND years=5 AND category LIKE 'EQ%'
ORDER BY cagr_pct DESC FETCH FIRST 5 ROWS ONLY;
-- Q17 TIME: Reliance price range last month?
SELECT MIN(close_price) min_price, MAX(close_price) max_price
FROM wksp_stocktrade.ai_nse_price_daily
WHERE symbol='RELIANCE' AND price_date>=ADD_MONTHS(TRUNC(SYSDATE),-1);
-- Q18 COMPARE: TCS vs Infosys 3-year CAGR?
SELECT symbol, years, cagr_pct FROM wksp_stocktrade.ai_stock_cagr
WHERE symbol IN ('TCS','INFY') AND years=3 ORDER BY symbol;
-- Q19 GENERAL: What is a flexi-cap mutual fund?
-- Expected: SELECT AI chat — prose response, no table access
SELECT AI chat what is a flexi cap mutual fund;
-- Q20 METRIC: HDFC Bank Sharpe ratio?
-- Note: stock Sharpe not stored — this tests whether model knows mf_scheme_stats
-- is for MF only. Correct answer: explain the data doesn't exist for stocks.
SELECT cagr_5y, volatility_ann, sharpe_ratio FROM wksp_stocktrade.mf_scheme_stats
WHERE scheme_code = (SELECT scheme_code FROM wksp_stocktrade.ai_mf_canonical
WHERE UPPER(scheme_name) LIKE '%HDFC BANK%'
FETCH FIRST 1 ROW ONLY);
Actual baseline results — Gemini 1.5 Flash, 07-Aug-2026
Model: gemini-1.5-flash via Google AI Studio API Profile: PERFIN_GENAI, comments:true, temperature:0 9 AI views in object_list
| Q | Question | Generated SQL summary | Score |
|---|---|---|---|
| 1 | TCS current price | ai_stock_snapshot WHERE UPPER(symbol)='TCS' | ✅ |
| 2 | TCS 5-year CAGR | ai_stock_cagr WHERE symbol='TCS' AND years=5 | ✅ |
| 3 | Large-cap MF returns | ai_mf_cagr WHERE UPPER(category) LIKE '%LARGE CAP%' | ⚠️ |
| 4 | Parag Parikh current NAV | ai_mf_snapshot WHERE UPPER(scheme_name)='Parag Parikh Flexi Cap Direct Growth' | ⚠️ |
| 5 | TCS price on date | ai_nse_price_daily WHERE symbol='TCS' AND price_date=TO_DATE('2023-07-15') | ✅ |
| 6 | HDFC Bank drawdown | ai_stock_drawdown WHERE UPPER(symbol)=UPPER('HDFC Bank') AND years=1 | ⚠️ |
| 7 | Infosys last bonus | ai_nse_corporate_action WHERE UPPER(symbol)='INFOSYS' AND action_type='BONUS' | ⚠️ |
| 8 | Eligible flexi-cap count | ai_mf_scheme WHERE UPPER(category)='FLEXI CAP' AND is_eligible='Y' | ❌ |
| 9 | Reliance volatility | ai_stock_drawdown WHERE UPPER(symbol)='RELIANCE' AND years=5 | ✅ |
| 10 | Lowest vol flexi-cap | ai_mf_cagr ORDER BY cagr_pct ASC (wrong view, wrong column, wrong category) | ❌ |
| 11 | HDFC Bank 52-week range | ai_stock_snapshot WHERE UPPER(company_name)=UPPER('HDFC Bank') | ⚠️ |
| 12 | Parag Parikh NAV Mar-2020 | ai_mf_nav_daily WHERE UPPER(scheme_name) LIKE '%Parag Parikh%' AND nav_date=... | ✅ |
| 13 | Best 1-year equity funds | ai_mf_cagr WHERE years=1 ORDER BY cagr_pct DESC (no EQ% filter) | ⚠️ |
| 14 | Parag Parikh Sharpe | ai_mf_cagr JOIN ai_stock_drawdown (recomputed, wrong join, wrong view) | ❌ |
| 15 | Total eligible schemes | ai_mf_scheme WHERE is_eligible='Y' | ✅ |
| 16 | HDFC equity funds | ai_mf_cagr WHERE UPPER(amc_name) LIKE '%HDFC%' AND years=5 (no EQ filter) | ⚠️ |
| 17 | Reliance price range | ai_nse_price_daily MIN/MAX on low_price/high_price (intraday not close) | ⚠️ |
| 18 | TCS vs Infosys CAGR | ai_stock_cagr WHERE UPPER(company_name) IN ('TCS','Infosys') | ⚠️ |
| 19 | What is flexi-cap fund | Generated SQL against category table instead of chat response | ❌ |
| 20 | HDFC Bank Sharpe | ai_stock_cagr JOIN ai_stock_drawdown (recomputed, company name not ticker) | ❌ |
Final score: 6 correct + 9 partial + 5 wrong = 45% baseline (Strict: 6/20 = 30% | Partial credit: 10.5/20 = 52.5%)
Failure analysis — what weeks 6–8 fix
| Failure type | Count | Affected Qs | Fix |
|---|---|---|---|
| Entity resolution — company name vs ticker | 5 | 4,6,7,11,18,20 | Week 7 entity resolver maps “HDFC Bank”→HDFCBANK, “Infosys”→INFY |
Category literal — FLEXI CAP vs EQ_FLEXI_CAP | 3 | 3,8,10 | Week 7 glossary teaches exact category taxonomy |
| Recomputing pre-computed metrics | 2 | 14,20 | Week 7 object catalog teaches to read mf_scheme_stats |
| Intent classification — chat vs SQL | 1 | 19 | Week 8 intent router |
| Cross-view join missing | 1 | 10 | Week 7 few-shot example for volatility+category query |
The pattern is clean and fixable. The model’s SQL structure is excellent — it picks the right view most of the time and generates syntactically correct SQL. The failures are all semantic: wrong literals, wrong entity keys, wrong intent. These are exactly the gaps that RAG retrieval + glossary + entity resolver close.
Target after Week 7–8 improvements: 70–85%
MV stale data — lesson learned
The ai_mf_cagr_mv returned NULL for category in early tests because it was built before the enrichment promotion ran. The MV snapshot captured NULL category values and a subsequent REFRESH didn’t help because it re-ran the same query against ai_mf_canonical which was correct — the issue was the promotion step (UPDATE mf_schemes SET category=category_d) ran after the MV was first built.
Fix: always run MV refresh after any enrichment promotion:
-- After any enrichment run, refresh all three MVs
EXEC DBMS_MVIEW.REFRESH('WKSP_STOCKTRADE.AI_MF_CAGR_MV','C');
EXEC DBMS_MVIEW.REFRESH('WKSP_STOCKTRADE.AI_STOCK_CAGR_MV','C');
EXEC DBMS_MVIEW.REFRESH('WKSP_STOCKTRADE.AI_STOCK_DRAWDOWN_MV','C');
The DROP + RECREATE also works and is more reliable when in doubt:
DROP MATERIALIZED VIEW wksp_stocktrade.ai_mf_cagr_mv;
-- then re-run the CREATE MATERIALIZED VIEW statement from Week 3
MODIFIED LOADER PACKAGE: mf_nav_loader_pkg
Enrichment as Step 6, inside every load transaction. Wipes never happen again.
CREATE OR REPLACE PACKAGE BODY WKSP_STOCKDATA.mf_nav_loader_pkg AS
c_par_base CONSTANT VARCHAR2(4000) :=
'https://objectstorage.ca-toronto-1.oraclecloud.com/p/UAHuqZCQIOZ3cSRwaidB21WeAsJ4KqOqLkq8wCHg2xzTj6y2XJAwC-sG-YsSo3m6/n/yztzhpsyjedf/b/nse_stock/o/cm_bhavcopy/';
c_max_back_days CONSTANT PLS_INTEGER := 7;
PROCEDURE log_msg(p_msg IN VARCHAR2) IS
BEGIN
dbms_output.put_line(p_msg); apex_debug.message(p_msg);
EXCEPTION WHEN OTHERS THEN NULL;
END log_msg;
-- Step 6: Re-enrich after every MF_SCHEMES reload
PROCEDURE enrich_mf_schemes IS
l_cnt PLS_INTEGER;
BEGIN
log_msg('Step 6a: AMC codes...');
UPDATE mf_schemes SET amc_code = COALESCE(
CASE WHEN isin_div_payout_growth IS NOT NULL AND isin_div_payout_growth!='-'
THEN SUBSTR(isin_div_payout_growth,4,3) END,
CASE WHEN isin_div_reinvestment IS NOT NULL AND isin_div_reinvestment!='-'
THEN SUBSTR(isin_div_reinvestment,4,3) END);
UPDATE mf_schemes s
SET amc_name_d=(SELECT m.amc_name FROM isin_amc_map m WHERE m.amc_code=s.amc_code)
WHERE s.amc_code IS NOT NULL;
UPDATE mf_schemes SET amc_name=amc_name_d WHERE amc_name_d IS NOT NULL;
log_msg('Step 6b: Categories...');
UPDATE mf_schemes SET category_d = CASE
WHEN REGEXP_LIKE(scheme_name,'UNCLAIMED|SEGREGATED PORTFOLIO','i') THEN 'NON_INVESTABLE'
WHEN REGEXP_LIKE(scheme_name,'(GOLD|SILVER).*ETF|ETF.*(GOLD|SILVER)','i') THEN 'COMMODITY_ETF'
WHEN REGEXP_LIKE(scheme_name,'BHARAT BOND.*FOF','i') THEN 'FOF_OVERSEAS'
WHEN REGEXP_LIKE(scheme_name,'BHARAT BOND','i') THEN 'ETF'
WHEN REGEXP_LIKE(scheme_name,'\bETF\b|EXCHANGE TRADED','i') THEN 'ETF'
WHEN REGEXP_LIKE(scheme_name,'FUND OF FUND|\bFOF\b|OVERSEAS|\bGLOBAL\b','i') THEN 'FOF_OVERSEAS'
WHEN REGEXP_LIKE(scheme_name,'NIFTY|SENSEX|\bINDEX\b|EQUAL WEIGHT','i') THEN 'INDEX'
WHEN REGEXP_LIKE(scheme_name,'AGGRESSIVE HYBRID','i') THEN 'HYBRID_AGGRESSIVE'
WHEN REGEXP_LIKE(scheme_name,'CONSERVATIVE HYBRID|MIP\b|MONTHLY INCOME','i') THEN 'HYBRID_CONSERVATIVE'
WHEN REGEXP_LIKE(scheme_name,'BALANCED ADVANTAGE|DYNAMIC ASSET','i') THEN 'HYBRID_BALANCED_ADV'
WHEN REGEXP_LIKE(scheme_name,'MULTI.ASSET','i') THEN 'HYBRID_MULTI_ASSET'
WHEN REGEXP_LIKE(scheme_name,'ARBITRAGE','i') THEN 'HYBRID_ARBITRAGE'
WHEN REGEXP_LIKE(scheme_name,'\bHYBRID\b|BALANCED','i') THEN 'HYBRID_OTHER'
WHEN REGEXP_LIKE(scheme_name,'FLEXI.?CAP','i') THEN 'EQ_FLEXI_CAP'
WHEN REGEXP_LIKE(scheme_name,'LARGE.{0,3}MID','i') THEN 'EQ_LARGE_MID_CAP'
WHEN REGEXP_LIKE(scheme_name,'LARGE.?CAP','i') THEN 'EQ_LARGE_CAP'
WHEN REGEXP_LIKE(scheme_name,'MID.?CAP','i') THEN 'EQ_MID_CAP'
WHEN REGEXP_LIKE(scheme_name,'SMALL.?CAP','i') THEN 'EQ_SMALL_CAP'
WHEN REGEXP_LIKE(scheme_name,'MULTI.?CAP','i') THEN 'EQ_MULTI_CAP'
WHEN REGEXP_LIKE(scheme_name,'\bVALUE\b|CONTRA','i') THEN 'EQ_VALUE_CONTRA'
WHEN REGEXP_LIKE(scheme_name,'FOCUSS?ED','i') THEN 'EQ_FOCUSED'
WHEN REGEXP_LIKE(scheme_name,'DIVIDEND YIELD','i') THEN 'EQ_DIVIDEND_YIELD'
WHEN REGEXP_LIKE(scheme_name,'\bELSS\b|TAX SAVER|TAX SAVING','i') THEN 'EQ_ELSS'
WHEN REGEXP_LIKE(scheme_name,'ESG|MOMENTUM|\bMNC\b|BUSINESS CYCLE','i') THEN 'EQ_THEMATIC'
WHEN REGEXP_LIKE(scheme_name,
'INFRA|BANKING.{0,3}FINAN|TECHNOLOGY|PHARMA|(^| )PSU( |$)','i') THEN 'EQ_SECTORAL'
WHEN REGEXP_LIKE(scheme_name,'OVERNIGHT','i') THEN 'DEBT_OVERNIGHT'
WHEN REGEXP_LIKE(scheme_name,'LIQUID','i') THEN 'DEBT_LIQUID'
WHEN REGEXP_LIKE(scheme_name,'ULTRA SHORT|SAVINGS FUND','i') THEN 'DEBT_ULTRA_SHORT'
WHEN REGEXP_LIKE(scheme_name,'LOW DURATION','i') THEN 'DEBT_LOW_DURATION'
WHEN REGEXP_LIKE(scheme_name,'MONEY MARKET','i') THEN 'DEBT_MONEY_MARKET'
WHEN REGEXP_LIKE(scheme_name,'FLOATING RATE|FLOATER','i') THEN 'DEBT_FLOATING'
WHEN REGEXP_LIKE(scheme_name,'SHORT DURATION|SHORT TERM','i') THEN 'DEBT_SHORT_DURATION'
WHEN REGEXP_LIKE(scheme_name,'MEDIUM DURATION|MEDIUM TERM','i') THEN 'DEBT_MEDIUM_DURATION'
WHEN REGEXP_LIKE(scheme_name,'CORPORATE BOND|CORPORATE DEBT','i') THEN 'DEBT_CORPORATE_BOND'
WHEN REGEXP_LIKE(scheme_name,'CREDIT RISK|ACCRUAL','i') THEN 'DEBT_CREDIT_RISK'
WHEN REGEXP_LIKE(scheme_name,'BANKING.{0,3}PSU|BANKING AND PSU','i') THEN 'DEBT_BANKING_PSU'
WHEN REGEXP_LIKE(scheme_name,'GILT|G-?SEC|GOVERNMENT SECURITIES','i') THEN 'DEBT_GILT'
WHEN REGEXP_LIKE(scheme_name,'DYNAMIC BOND|DYNAMIC DEBT','i') THEN 'DEBT_DYNAMIC'
WHEN REGEXP_LIKE(scheme_name,'FIXED (TERM|MATURITY)|FMP|FTIF|CAPITAL BUILDER','i') THEN 'DEBT_FIXED_TERM'
WHEN REGEXP_LIKE(scheme_name,'\bBOND\b|\bDEBT\b|INCOME','i') THEN 'DEBT_OTHER'
WHEN REGEXP_LIKE(scheme_name,'RETIREMENT|CHILD|\bULIS\b','i') THEN 'SOLUTION_ORIENTED'
ELSE 'UNCLASSIFIED' END;
UPDATE mf_schemes SET category=category_d, plan_type=plan_type_d WHERE category_d IS NOT NULL;
log_msg('Step 6c: Plan/option/base_fund_key...');
UPDATE mf_schemes
SET plan_type_d=CASE WHEN REGEXP_LIKE(scheme_name,'DIRECT','i') THEN 'DIRECT'
WHEN REGEXP_LIKE(scheme_name,'REGULAR','i') THEN 'REGULAR'
ELSE 'UNKNOWN' END,
option_type_d=CASE
WHEN (isin_div_payout_growth IS NULL OR isin_div_payout_growth='-')
AND isin_div_reinvestment IS NOT NULL AND isin_div_reinvestment!='-' THEN 'IDCW_REINVEST'
WHEN REGEXP_LIKE(scheme_name,'GROWTH','i') THEN 'GROWTH'
WHEN REGEXP_LIKE(scheme_name,'IDCW|DIVIDEND|PAYOUT','i') THEN 'IDCW'
WHEN REGEXP_LIKE(scheme_name,'BONUS','i') THEN 'BONUS'
ELSE 'UNKNOWN' END;
UPDATE mf_schemes SET base_fund_key=amc_code||':'||
TRIM(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(UPPER(scheme_name),
'[-(]?\s*(DIRECT|REGULAR)\s*(PLAN)?\s*[-)]?',' '),
'[-(]?\s*(GROWTH|IDCW|DIVIDEND|PAYOUT|REINVESTMENT|BONUS|MONTHLY|'||
'QUARTERLY|ANNUAL|DAILY|WEEKLY|OPTION|INCOME DISTRIBUTION CUM CAPITAL WITHDRAWAL|'||
'PLAN [AB])\s*[-)]?',' '),'\s{2,}',' '));
log_msg('Step 6d: Flags...');
UPDATE mf_schemes SET is_investable='N'
WHERE category_d='NON_INVESTABLE' OR REGEXP_LIKE(scheme_name,'UNCLAIMED|SEGREGATED','i');
UPDATE mf_schemes SET is_investable='Y' WHERE is_investable IS NULL OR is_investable!='N';
UPDATE mf_schemes SET is_eligible='N';
MERGE INTO mf_schemes s
USING (SELECT scheme_code FROM (
SELECT scheme_code, COUNT(*) nav_days, MAX(gap) worst_gap
FROM (SELECT scheme_code, nav_date,
nav_date-LAG(nav_date) OVER (PARTITION BY scheme_code ORDER BY nav_date) gap
FROM mf_nav_history WHERE nav_date>=ADD_MONTHS(SYSDATE,-60))
GROUP BY scheme_code) WHERE nav_days>=1000 AND worst_gap<=7)
elig ON (s.scheme_code=elig.scheme_code)
WHEN MATCHED THEN UPDATE SET s.is_eligible='Y';
SELECT COUNT(*) INTO l_cnt FROM mf_schemes WHERE is_eligible='Y';
log_msg(l_cnt||' schemes eligible. Enrichment complete.');
END enrich_mf_schemes;
PROCEDURE load_nav_from_bucket(p_nav_date DATE DEFAULT NULL) IS
l_requested_date DATE; l_file_date DATE; l_try_date DATE;
l_ddmmyy VARCHAR2(6); l_url VARCHAR2(4000);
l_blob BLOB; l_found BOOLEAN:=FALSE; l_nav_date DATE; l_cnt PLS_INTEGER;
BEGIN
IF p_nav_date IS NULL THEN
l_requested_date:=TRUNC(CAST(SYSTIMESTAMP AT TIME ZONE 'America/Los_Angeles' AS DATE));
ELSE l_requested_date:=TRUNC(p_nav_date); END IF;
l_try_date:=l_requested_date;
FOR i IN 0..c_max_back_days LOOP
l_ddmmyy:=TO_CHAR(l_try_date,'DDMMYY');
l_url:=c_par_base||l_ddmmyy||'/NAVAll.txt';
log_msg('Trying '||TO_CHAR(l_try_date,'YYYY-MM-DD'));
BEGIN
l_blob:=apex_web_service.make_rest_request_b(p_url=>l_url,p_http_method=>'GET');
IF apex_web_service.g_status_code BETWEEN 200 AND 299 THEN
l_found:=TRUE; l_file_date:=l_try_date; EXIT;
END IF;
EXCEPTION WHEN OTHERS THEN log_msg('Error: '||SQLERRM); END;
IF NOT l_found THEN l_try_date:=l_try_date-1; END IF;
END LOOP;
IF NOT l_found THEN
RAISE_APPLICATION_ERROR(-20002,'No NAVAll.txt found for '||
TO_CHAR(l_requested_date,'YYYY-MM-DD'));
END IF;
SELECT MAX(TO_DATE(TRIM(col006),'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH'))
INTO l_nav_date
FROM TABLE(apex_data_parser.parse(p_content=>l_blob,p_file_name=>'NAVAll.txt'))
WHERE TRIM(col006) IS NOT NULL AND REGEXP_LIKE(TRIM(col001),'^\d+$');
log_msg('NAV date: '||TO_CHAR(l_nav_date,'YYYY-MM-DD'));
-- Step 4: MF_NAV_HISTORY
DELETE FROM mf_nav_history WHERE nav_date=l_nav_date;
INSERT INTO mf_nav_history (scheme_code,nav_date,nav_value,load_ts)
SELECT TO_NUMBER(TRIM(t.col001)),l_nav_date,TO_NUMBER(TRIM(t.col005)),SYSTIMESTAMP
FROM TABLE(apex_data_parser.parse(p_content=>l_blob,p_file_name=>'NAVAll.txt')) t
WHERE TRIM(t.col001) IS NOT NULL AND REGEXP_LIKE(TRIM(t.col001),'^\d+$')
AND REGEXP_LIKE(TRIM(t.col005),'^\d+(\.\d+)?$') AND TRIM(t.col006) IS NOT NULL;
l_cnt:=SQL%ROWCOUNT;
log_msg(l_cnt||' MF_NAV_HISTORY rows inserted.');
-- Step 5: MF_SCHEMES full reload
DELETE FROM mf_schemes;
INSERT INTO mf_schemes (scheme_code,scheme_name,last_nav,last_nav_date,
created_on,isin_div_payout_growth,isin_div_reinvestment)
SELECT DISTINCT TO_NUMBER(TRIM(t.col001)),TRIM(t.col004),TO_NUMBER(TRIM(t.col005)),
TO_DATE(TRIM(t.col006),'DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH'),
SYSTIMESTAMP,TRIM(t.col002),TRIM(t.col003)
FROM TABLE(apex_data_parser.parse(p_content=>l_blob,p_file_name=>'NAVAll.txt')) t
WHERE TRIM(t.col001) IS NOT NULL AND REGEXP_LIKE(TRIM(t.col001),'^\d+$')
AND REGEXP_LIKE(TRIM(t.col005),'^\d+(\.\d+)?$') AND TRIM(t.col006) IS NOT NULL;
l_cnt:=SQL%ROWCOUNT;
log_msg(l_cnt||' MF_SCHEMES rows inserted.');
-- Step 6: Enrich (NEW — runs inside same transaction, survives the reload)
enrich_mf_schemes;
-- Step 7: Single commit after ALL steps complete
COMMIT;
log_msg('Load complete. NAV date='||TO_CHAR(l_nav_date,'YYYY-MM-DD'));
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- roll back entire load if any step fails
log_msg('Error: '||SQLERRM);
RAISE;
END load_nav_from_bucket;
END mf_nav_loader_pkg;
/
ERRORS AND FIXES QUICK REFERENCE
| Error | Cause | Fix |
|---|---|---|
ORA-03048: | not valid | Database Actions misparses || in COMMENT ON | Wrap in BEGIN EXECUTE IMMEDIATE '...' END |
| ORA-00978: nested group function | AVG() inside SUM(POWER(x-AVG(x))) | Pre-compute mean/stddev in a separate CTE |
| ORA-22848: cannot use VECTOR as comparison key | DISTINCT over result set with VECTOR column | Deduplicate on non-vector columns in subquery first |
| ORA-00903: invalid table name | Running as wrong user, no privilege | SELECT USER FROM dual; grant or switch schema |
| ORA-27475: unknown job | Job name has wrong schema prefix | Use WKSP_STOCKTRADE.JOB_NAME not STOCKTRADE. |
| ORA-40284: model does not exist | ONNX load silently failed or wrong schema | Check all_mining_models across all owners |
| ORA-12003: MV does not exist | Object never created | Wrap DROP in DECLARE/BEGIN with EXCEPTION WHEN OTHERS |
| ORA-20404: object not found | Pre-authenticated URL expired | Try newer URL; check file size before loading model |
| Substitution variable prompt | & in string, Database Actions treats as variable | SET DEFINE OFF at top of script |
\b word boundary fails | Oracle regex engine limitation | Use `(^ |
| Enrichment wiped nightly | Loader does DELETE+INSERT on MF_SCHEMES | Embed enrich_mf_schemes() as Step 6 in loader |
| adj_factor floating-point tail | EXP(LN(0.5)) ≠ exactly 0.5 in IEEE 754 | ROUND(adj_factor, 6) in the adjustments CTE |
VECTOR_DIMENSION() missing | Not in this 26ai build | Count elements in vector string instead |
| ORA-20000: network connection timed out on Select AI | OCI GenAI not available in ca-toronto-1 | Use Google Gemini (free, pre-whitelisted, no card needed) |
| ORA-24244: invalid host for ACL assignment | Always Free ADB locks ACL — cannot add arbitrary hosts | Only pre-whitelisted hosts work; use OCI services or Google/OpenAI |
| ORA-20403: Authorization failed (OpenAI) | OpenAI requires payment method on file even for free credits | Add card to platform.openai.com billing, or use Gemini instead |
| ORA-20404: object not found (OCI GenAI cross-region) | Resource principal can’t resolve tenancy cross-region without domain replication | Domain replication not available on Always Free — use Gemini |
| ORA-00904: ATTRIBUTES invalid identifier on user_cloud_ai_profiles | Column doesn’t exist in this 26ai build — profile config not exposed via SQL | Test profile by running SELECT AI directly; attributes are stored internally |
| MV category column NULL after REFRESH | MV was built when category was NULL; REFRESH re-runs same stale query | DROP and RECREATE the MV after enrichment promotion runs |
| API key exposed in error message | Oracle includes the key in the ORA-20404 URL for Gemini | Revoke the exposed key immediately at aistudio.google.com; generate new one |
OBJECT INVENTORY AFTER WEEK 5
WKSP_STOCKDATA (enriched): mf_schemes (14,200 rows + 8 new columns), isin_amc_map (65 rows)
WKSP_STOCKTRADE — plain views: ai_nse_price_daily, ai_nse_corporate_action, ai_mf_nav_daily, ai_mf_scheme, ai_stock_snapshot, ai_mf_snapshot, ai_mf_canonical, ai_stock_cagr (wraps MV), ai_mf_cagr (wraps MV), ai_stock_drawdown (wraps MV)
WKSP_STOCKTRADE — materialized views: ai_stock_cagr_mv (6am daily), ai_mf_cagr_mv (7am), ai_stock_drawdown_mv (7:30am)
WKSP_STOCKTRADE — tables: ai_object_catalog (10 rows), ai_glossary (10 rows), mf_daily_return (~3.97M rows, IOT), mf_scheme_stats (1,777 rows), mf_feature_scaling (15 rows), mf_scheme_feature (1,777 vectors VECTOR(15,FLOAT32)), ai_entity (4,126 rows: 1,714 MF + 2,412 NSE, VECTOR(384,FLOAT32))
WKSP_STOCKTRADE — model: ALL_MINILM_L12_V2 (ONNX embedding, 384 dimensions)
Scheduler jobs: REFRESH_STOCK_CAGR (6am), REFRESH_MF_CAGR (7am), REFRESH_STOCK_DRAWDOWN (7:30am)