sql: Designing Efficient SQL SELECT Queries using Oracel 21c

/* MD / 富文本中的 .toc（含博客园搬家等嵌套结构）；.toc-box 在侧栏，不受影响 */
#content_views .toc,
/* 编辑器常在目录前后插入空 p（:empty 仍占 20px），一并去掉避免顶空隙 */
#content_views.markdown_views > p:empty:has(+ .toc),
#content_views.markdown_views > .toc + p:empty,
/* 富文本旧版目录标记 */
#content_views.htmledit_views #main-toc,
#content_views.htmledit_views #hr-toc,
#content_views.htmledit_views p[id*=\”-toc\”] {
display: none !important;
}
/* 目录去掉后，紧跟的首个标题不再多出一块上边距 */
#content_views.markdown_views > .toc + h1,
#content_views.markdown_views > .toc + h2,
#content_views.markdown_views > .toc + h3,
#content_views.markdown_views > .toc + h4,
#content_views.markdown_views > .toc + p:empty + h1,
#content_views.markdown_views > .toc + p:empty + h2,
#content_views.markdown_views > .toc + p:empty + h3,
#content_views.markdown_views > .toc + p:empty + h4 {
margin-top: 0 !important;
}

— =====================================================================
— Oracle 21c : Dynamic Query Building in SQL
— Techniques, Security, and Best Practices
— Safe dynamic SQL (parameterized queries, stored procedures, runtime
— filters, SQL-injection controls) for an international jewelry
— mid-platform spanning OA/ERP/CRM/HRM/MES/SCM/WMS/KMS.
—
— Series topic #21. Self-contained + idempotent. 17 self-checks.
— Conventions (learned across the 21-topic series):
— * All object names start with a LETTER (DQ_); Oracle rejects '_'-leading
— unquoted identifiers with ORA-00911.
— * PROMPT / comments are pure ASCII.
— * Password lives only in the shell $P variable, never in this file.
— * Cleanup loop wraps each DROP in an inner EXCEPTION handler so a
— transient lock on one object never aborts the rest.
— =====================================================================
SET ECHO OFF FEED OFF PAGES 0 SERVEROUT ON SIZE 1000000 LINES 200 DEFINE OFF
WHENEVER SQLERROR CONTINUE

— =====================================================================
— 1. Idempotent cleanup
— =====================================================================
DECLARE
PROCEDURE ddrop(p_sql VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_sql;
EXCEPTION
WHEN OTHERS THEN NULL; — object may not exist / briefly locked
END;
BEGIN
FOR r IN (SELECT table_name FROM user_tables WHERE table_name LIKE 'DQ%') LOOP
ddrop('DROP TABLE '||r.table_name||' CASCADE CONSTRAINTS PURGE');
END LOOP;
FOR r IN (SELECT object_name, object_type FROM user_objects
WHERE object_name LIKE 'DQ%'
AND object_type IN ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','VIEW','SEQUENCE','TYPE')) LOOP
ddrop('DROP '||r.object_type||' '||r.object_name);
END LOOP;
ddrop('DROP TABLE dq_val PURGE');
BEGIN DELETE FROM plan_table WHERE statement_id='dq_p'; COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END;
END;
/

— =====================================================================
— 2. Dimensions: 8 business domains + 10 currencies + 12 regions
— =====================================================================
CREATE TABLE dq_domain (
domain_id NUMBER PRIMARY KEY,
code VARCHAR2(10) UNIQUE,
name VARCHAR2(40)
);
INSERT INTO dq_domain VALUES (1,'OA','Office Automation');
INSERT INTO dq_domain VALUES (2,'ERP','Enterprise Resource Planning');
INSERT INTO dq_domain VALUES (3,'CRM','Customer Relationship Mgmt');
INSERT INTO dq_domain VALUES (4,'HRM','Human Resource Mgmt');
INSERT INTO dq_domain VALUES (5,'MES','Manufacturing Execution');
INSERT INTO dq_domain VALUES (6,'SCM','Supply Chain Mgmt');
INSERT INTO dq_domain VALUES (7,'WMS','Warehouse Mgmt');
INSERT INTO dq_domain VALUES (8,'KMS','Knowledge Mgmt');

CREATE TABLE dq_currency (
cur_code VARCHAR2(3) PRIMARY KEY,
name VARCHAR2(30),
rate_to_usd NUMBER
);
INSERT INTO dq_currency VALUES ('USD','US Dollar',1);
INSERT INTO dq_currency VALUES ('EUR','Euro',1.08);
INSERT INTO dq_currency VALUES ('GBP','Pound',1.27);
INSERT INTO dq_currency VALUES ('JPY','Yen',0.0067);
INSERT INTO dq_currency VALUES ('CNY','Yuan',0.14);
INSERT INTO dq_currency VALUES ('HKD','HK Dollar',0.128);
INSERT INTO dq_currency VALUES ('AUD','Aussie',0.66);
INSERT INTO dq_currency VALUES ('BRL','Real',0.18);
INSERT INTO dq_currency VALUES ('INR','Rupee',0.012);
INSERT INTO dq_currency VALUES ('AED','Dirham',0.27);
INSERT INTO dq_currency VALUES ('SGD','Sing Dollar',0.74);
INSERT INTO dq_currency VALUES ('ZAR','Rand',0.054);

CREATE TABLE dq_region (
region_id NUMBER PRIMARY KEY,
region_name VARCHAR2(30),
country VARCHAR2(30),
tz_name VARCHAR2(40),
cur_code VARCHAR2(3),
hub_lon NUMBER,
hub_lat NUMBER,
CONSTRAINT fk_dqreg_cur FOREIGN KEY(cur_code) REFERENCES dq_currency(cur_code)
);
INSERT INTO dq_region VALUES (1,'North America','USA','America/New_York','USD',-74.0,40.7);
INSERT INTO dq_region VALUES (2,'Western Europe','Germany','Europe/Berlin','EUR',13.4,52.5);
INSERT INTO dq_region VALUES (3,'UK & Ireland','UK','Europe/London','GBP',-0.13,51.5);
INSERT INTO dq_region VALUES (4,'Japan','Japan','Asia/Tokyo','JPY',139.7,35.7);
INSERT INTO dq_region VALUES (5,'China','China','Asia/Shanghai','CNY',121.5,31.2);
INSERT INTO dq_region VALUES (6,'Hong Kong','Hong Kong','Asia/Hong_Kong','HKD',114.2,22.3);
INSERT INTO dq_region VALUES (7,'Australia','Australia','Australia/Sydney','AUD',151.2,-33.9);
INSERT INTO dq_region VALUES (8,'Brazil','Brazil','America/Sao_Paulo','BRL',-46.6,-23.5);
INSERT INTO dq_region VALUES (9,'India','India','Asia/Kolkata','INR',77.2,28.6);
INSERT INTO dq_region VALUES (10,'UAE','UAE','Asia/Dubai','AED',55.3,25.2);
INSERT INTO dq_region VALUES (11,'Singapore','Singapore','Asia/Singapore','SGD',103.8,1.35);
INSERT INTO dq_region VALUES (12,'South Africa','South Africa','Africa/Johannesburg','ZAR',28.0,-26.2);

— =====================================================================
— 3. Main tables
— dq_site : 6000 branch/service points across 8 domains, 12 regions
— dq_txn : 12000 transactions, INTERVAL-partitioned by local_day
— (partition pruning is key at trillion scale)
— =====================================================================
CREATE TABLE dq_site (
site_id NUMBER PRIMARY KEY,
system_code VARCHAR2(10),
region_id NUMBER,
site_name VARCHAR2(40),
country VARCHAR2(30),
cur_code VARCHAR2(3),
amount_usd NUMBER(14,2),
amount_orig NUMBER(14,2),
created_utc TIMESTAMP WITH TIME ZONE,
local_day DATE,
CONSTRAINT fk_dqsite_dom FOREIGN KEY(system_code) REFERENCES dq_domain(code),
CONSTRAINT fk_dqsite_reg FOREIGN KEY(region_id) REFERENCES dq_region(region_id),
CONSTRAINT fk_dqsite_cur FOREIGN KEY(cur_code) REFERENCES dq_currency(cur_code)
);

INSERT INTO dq_site (site_id, system_code, region_id, site_name, country, cur_code,
amount_usd, amount_orig, created_utc, local_day)
SELECT
g.n,
d.code,
MOD(g.n,12)+1,
'SITE_'||g.n,
r.country,
r.cur_code,
ROUND(DBMS_RANDOM.VALUE(1000,500000),2),
ROUND(DBMS_RANDOM.VALUE(1000,500000),2),
FROM_TZ(CAST(DATE '2026-06-01' + MOD(g.n,30) AS TIMESTAMP),'UTC'),
TRUNC(CAST(FROM_TZ(CAST(DATE '2026-06-01' + MOD(g.n,30) AS TIMESTAMP),'UTC')
AT TIME ZONE r.tz_name AS DATE))
FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL<=6000) g
JOIN dq_domain d ON d.domain_id = MOD(g.n,8)+1
JOIN dq_region r ON r.region_id = MOD(g.n,12)+1;

CREATE TABLE dq_txn (
txn_id NUMBER PRIMARY KEY,
site_id NUMBER,
system_code VARCHAR2(10),
region_id NUMBER,
cur_code VARCHAR2(3),
amount_orig NUMBER(14,2),
amount_usd NUMBER(14,2),
created_utc TIMESTAMP WITH TIME ZONE,
local_day DATE,
CONSTRAINT fk_dqtxn_site FOREIGN KEY(site_id) REFERENCES dq_site(site_id),
CONSTRAINT fk_dqtxn_dom FOREIGN KEY(system_code) REFERENCES dq_domain(code),
CONSTRAINT fk_dqtxn_reg FOREIGN KEY(region_id) REFERENCES dq_region(region_id)
)
PARTITION BY RANGE (local_day)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION p_init VALUES LESS THAN (DATE '2026-06-01'));

INSERT INTO dq_txn (txn_id, site_id, system_code, region_id, cur_code,
amount_orig, amount_usd, created_utc, local_day)
SELECT
g.n,
MOD(g.n,6000)+1,
s.system_code,
s.region_id,
s.cur_code,
ROUND(DBMS_RANDOM.VALUE(10,5000),2),
ROUND(ROUND(DBMS_RANDOM.VALUE(10,5000),2) * c.rate_to_usd, 2),
s.created_utc + NUMTODSINTERVAL(MOD(g.n,1440),'MINUTE'),
s.local_day
FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL<=12000) g
JOIN dq_site s ON s.site_id = MOD(g.n,6000)+1
JOIN dq_currency c ON c.cur_code = s.cur_code;

— =====================================================================
— 4. Indexes (B-tree + composite) + statistics — high-concurrency
— =====================================================================
CREATE INDEX dq_ix_sys_reg ON dq_site(system_code, region_id);
CREATE INDEX dq_ix_site_loc ON dq_site(local_day);
CREATE INDEX dq_ix_txn_day ON dq_txn(local_day);
CREATE INDEX dq_ix_txn_cur ON dq_txn(cur_code);

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER,'DQ_SITE');
DBMS_STATS.GATHER_TABLE_STATS(USER,'DQ_TXN');
END;
/

— =====================================================================
— 5. Validation helpers + check infrastructure
— =====================================================================

— Whitelist validator for dynamic identifiers (column names in ORDER BY,
— dynamic predicates). Rejects anything that is not a real column of dq_site.
CREATE OR REPLACE FUNCTION dq_validate_col(p_col VARCHAR2) RETURN VARCHAR2 IS
v_ok VARCHAR2(30);
BEGIN
SELECT column_name INTO v_ok FROM user_tab_columns
WHERE table_name='DQ_SITE' AND column_name = UPPER(p_col);
RETURN v_ok;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid column for dynamic SQL: '||p_col);
END;
/

— Identifier sanitizer (defense-in-depth, mirrors DBMS_ASSERT.SIMPLE_SQL_NAME).
— Raises if the identifier contains anything outside [A-Za-z0-9_$#] or
— does not start with a letter.
CREATE OR REPLACE FUNCTION dq_assert_name(p_ident VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF p_ident IS NULL OR LENGTH(p_ident)>30 THEN
RAISE_APPLICATION_ERROR(-20003,'Invalid identifier');
END IF;
IF NOT REGEXP_LIKE(p_ident,'^[A-Za-z][A-Za-z0-9_$#]*$') THEN
RAISE_APPLICATION_ERROR(-20003,'Invalid identifier: '||p_ident);
END IF;
RETURN p_ident;
END;
/

CREATE TABLE dq_val (chk_id VARCHAR2(10), descr VARCHAR2(80), pass CHAR(1), detail VARCHAR2(120));
CREATE OR REPLACE PROCEDURE dq_chk(p_id VARCHAR2, p_desc VARCHAR2, p_pass NUMBER, p_detail VARCHAR2) IS
BEGIN
INSERT INTO dq_val(chk_id, descr, pass, detail) VALUES (p_id, p_desc, CASE WHEN p_pass=1 THEN 'Y' ELSE 'N' END, p_detail);
END;
/

— =====================================================================
— 6. Safe dynamic SQL building blocks
— =====================================================================

— (A) Parameterized dynamic lookup — bind variable, immune to injection.
CREATE OR REPLACE FUNCTION dq_param_lookup(p_name VARCHAR2) RETURN NUMBER IS
v_cnt NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dq_site WHERE site_name = :n' INTO v_cnt USING p_name;
RETURN v_cnt;
END;
/

— (B) DANGEROUS pattern (string concatenation) — shown ONLY to demonstrate
— the vulnerability. Passing "x' OR '1'='1" makes it return ALL rows.
— NEVER use this form with user input.
CREATE OR REPLACE FUNCTION dq_concat_danger(p_name VARCHAR2) RETURN NUMBER IS
v_cnt NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dq_site WHERE site_name = '''||p_name||'''' INTO v_cnt;
RETURN v_cnt;
END;
/

— (C) Runtime filter builder — native dynamic SQL (EXECUTE IMMEDIATE / OPEN…FOR)
— with each optional filter using TWO distinct placeholders (:x_n for the
— IS NULL guard, :x_v for the typed compare), bound once each. Dynamic
— ORDER BY is whitelist-guarded.
CREATE OR REPLACE FUNCTION dq_count_dynamic(
p_system_code VARCHAR2 DEFAULT NULL,
p_region_id NUMBER DEFAULT NULL,
p_min_amount NUMBER DEFAULT NULL,
p_currency VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
v_sql VARCHAR2(2000);
v_cnt NUMBER;
BEGIN
— Native dynamic SQL (DBMS_SQL is denied here via ORA-29471). Each optional
— filter uses TWO distinct placeholders: :x_n for the (IS NULL) guard and :x_v
— for the typed comparison. EXECUTE IMMEDIATE binds by POSITION, so the USING
— list must hold one value per placeholder occurrence (8 here). Reusing one name
— for both the untyped IS NULL and the typed compare raises ORA-00932.
v_sql := 'SELECT COUNT(*) FROM dq_site'
||' WHERE (:sc_n IS NULL OR system_code = :sc_v)'
||' AND (:rg_n IS NULL OR region_id = :rg_v)'
||' AND (:am_n IS NULL OR amount_usd >= :am_v)'
||' AND (:cu_n IS NULL OR cur_code = :cu_v)';
EXECUTE IMMEDIATE v_sql INTO v_cnt
USING p_system_code, p_system_code, p_region_id, p_region_id, p_min_amount, p_min_amount, p_currency, p_currency;
RETURN NVL(v_cnt,0);
END;
/

— (D) Refcursor variant of the runtime filter (for UIs / reports).
CREATE OR REPLACE PROCEDURE dq_search_dynamic(
p_system_code VARCHAR2 DEFAULT NULL,
p_region_id NUMBER DEFAULT NULL,
p_min_amount NUMBER DEFAULT NULL,
p_currency VARCHAR2 DEFAULT NULL,
p_order_col VARCHAR2 DEFAULT 'site_id',
p_rc OUT SYS_REFCURSOR
) IS
v_sql VARCHAR2(4000);
v_order VARCHAR2(30) := dq_validate_col(p_order_col); — whitelist
BEGIN
v_sql := 'SELECT site_id, system_code, region_id, site_name, cur_code, amount_usd FROM dq_site'
||' WHERE (:sc_n IS NULL OR system_code = :sc_v)'
||' AND (:rg_n IS NULL OR region_id = :rg_v)'
||' AND (:am_n IS NULL OR amount_usd >= :am_v)'
||' AND (:cu_n IS NULL OR cur_code = :cu_v)'
||' ORDER BY '||v_order;
— OPEN…FOR USING cannot infer bind types from an ALL-NULL list (ORA-00932);
— when no filter is supplied, open without binds.
IF p_system_code IS NULL AND p_region_id IS NULL AND p_min_amount IS NULL AND p_currency IS NULL THEN
OPEN p_rc FOR v_sql;
ELSE
OPEN p_rc FOR v_sql
USING p_system_code, p_system_code, p_region_id, p_region_id, p_min_amount, p_min_amount, p_currency, p_currency;
END IF;
END;
/

— (E) AUTHID CURRENT_USER package — dynamic SQL runs with the INVOKER's
— privileges (security best practice for multi-tenant mid-platforms).
— Includes a RESULT_CACHE + PARALLEL function for trillion-scale rollups.
CREATE OR REPLACE PACKAGE dq_pkg AUTHID CURRENT_USER IS
FUNCTION region_sales(p_region NUMBER) RETURN NUMBER RESULT_CACHE;
FUNCTION dynamic_filter(p_col VARCHAR2, p_val VARCHAR2) RETURN NUMBER;
END dq_pkg;
/
CREATE OR REPLACE PACKAGE BODY dq_pkg IS
FUNCTION region_sales(p_region NUMBER) RETURN NUMBER RESULT_CACHE IS
v NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT /*+ PARALLEL(4) */ SUM(amount_usd) FROM dq_txn WHERE region_id = :r'
INTO v USING p_region;
RETURN NVL(v,0);
END;
FUNCTION dynamic_filter(p_col VARCHAR2, p_val VARCHAR2) RETURN NUMBER IS
v_cnt NUMBER;
v_col VARCHAR2(30) := dq_validate_col(p_col); — whitelist guard
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dq_site WHERE '||v_col||' = :v' INTO v_cnt USING p_val;
RETURN v_cnt;
END;
END dq_pkg;
/

— =====================================================================
— 7. Demos : dynamic query building, security, best practices
— =====================================================================

PROMPT — A. Sample sites (geography/domain mix)
SELECT site_id, system_code, region_id, site_name, cur_code, amount_usd
FROM dq_site WHERE ROWNUM<=5;

PROMPT — B. Parameterized dynamic lookup (bind variable => safe)
SELECT dq_param_lookup('SITE_1') exact_match FROM dual;

PROMPT — C. Runtime filter: ERP sites in region 1, min amount 100000
SELECT dq_count_dynamic(p_system_code=>'ERP', p_region_id=>1, p_min_amount=>100000) erp_r1_cnt FROM dual;

PROMPT — D. DBMS_SQL dynamic filter: CRM sites only
SELECT dq_count_dynamic(p_system_code=>'CRM') crm_cnt FROM dual;

PROMPT — E. Whitelist-guarded dynamic ORDER BY (top 5 by amount_usd), region 1
DECLARE
rc SYS_REFCURSOR;
v_id dq_site.site_id%TYPE; v_sys dq_site.system_code%TYPE; v_reg dq_site.region_id%TYPE;
v_name dq_site.site_name%TYPE; v_cur dq_site.cur_code%TYPE; v_amt dq_site.amount_usd%TYPE;
BEGIN
dq_search_dynamic(p_order_col=>'amount_usd', p_region_id=>1, p_rc=>rc);
LOOP
FETCH rc INTO v_id, v_sys, v_reg, v_name, v_cur, v_amt;
EXIT WHEN rc%NOTFOUND OR rc%ROWCOUNT>5;
DBMS_OUTPUT.PUT_LINE('site='||v_id||' sys='||v_sys||' usd='||v_amt);
END LOOP;
CLOSE rc;
END;
/

PROMPT — F. SQL injection: safe (bind) vs DANGEROUS (concat). DO NOT USE concat.
DECLARE
v_safe NUMBER; v_danger NUMBER;
inj VARCHAR2(50) := 'x'' OR ''1''=''1';
BEGIN
v_safe := dq_param_lookup(inj); — bind => literal match => 0
v_danger := dq_concat_danger(inj); — concat => predicate hijack => all rows
DBMS_OUTPUT.PUT_LINE('SAFE(bind) rows = '||v_safe||' (injection neutralized, expected 0)');
DBMS_OUTPUT.PUT_LINE('DANGER(concat) row= '||v_danger||' (LEAK: returns every row!)');
END;
/

PROMPT — G. Identifier validation: DBMS_ASSERT-style guard rejects a malicious identifier
DECLARE
v VARCHAR2(30);
BEGIN
BEGIN
v := dq_assert_name('site_id; DROP TABLE dq_site–');
DBMS_OUTPUT.PUT_LINE('UNEXPECTED: accepted malicious identifier');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('REJECTED malicious identifier (good): '||SQLERRM);
END;
v := dq_assert_name('site_id');
DBMS_OUTPUT.PUT_LINE('Accepted valid identifier: '||v);
END;
/

PROMPT — H. AUTHID CURRENT_USER package: whitelist dynamic column filter
SELECT dq_pkg.dynamic_filter('system_code','WMS') wms_cnt FROM dual;
SELECT ROUND(dq_pkg.region_sales(5),2) cny_region_sales_usd FROM dual;

PROMPT — I. EXPLAIN PLAN for a single-day dynamic query (partition pruning)
DECLARE
v_sql VARCHAR2(4000);
BEGIN
BEGIN DELETE FROM plan_table WHERE statement_id='dq_p'; END;
v_sql := 'SELECT /*+ FULL(t) */ COUNT(*) FROM dq_txn t WHERE local_day = DATE ''2026-06-15'' AND system_code=''ERP''';
EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID=''dq_p'' FOR '||v_sql;
FOR r IN (SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options||' '||object_name plan_line
FROM plan_table
START WITH id=0 AND statement_id='dq_p'
CONNECT BY PRIOR id = parent_id AND statement_id='dq_p') LOOP
DBMS_OUTPUT.PUT_LINE(r.plan_line);
END LOOP;
END;
/

PROMPT — J. International timezone: rows whose LOCAL day differs from UTC day
SELECT COUNT(*) rows_shifted_to_local_day
FROM dq_site WHERE local_day <> TRUNC(CAST(created_utc AS DATE));

PROMPT — K. Multi-currency report: only SUM(amount_usd) is comparable across currencies
SELECT cur_code, COUNT(*) txns, ROUND(SUM(amount_usd),2) usd_total
FROM dq_txn GROUP BY cur_code ORDER BY cur_code;

PROMPT — L. RESULT_CACHE + PARALLEL rollup matches static sum (trillion-scale pattern)
SELECT ROUND(dq_pkg.region_sales(3),2) hkg_region_usd,
(SELECT ROUND(SUM(amount_usd),2) FROM dq_txn WHERE region_id=3) static_usd
FROM dual;

PROMPT — M. All-NULL runtime filters => full set returned
SELECT dq_count_dynamic() full_set FROM dual;

PROMPT — N. Domain (OA/ERP/CRM/HRM/MES/SCM/WMS/KMS) distribution across 6000 sites
SELECT system_code, COUNT(*) cnt FROM dq_site GROUP BY system_code ORDER BY system_code;

— =====================================================================
— 8. Self-checks V1..V17
— =====================================================================
DECLARE
v1 NUMBER; v2 NUMBER; v3 NUMBER; v4 NUMBER; v5 NUMBER; v6 NUMBER;
v7 NUMBER; v8ok NUMBER:=0; v9ok NUMBER:=0; v10 VARCHAR2(30); v11 NUMBER;
v12 NUMBER; v13a NUMBER; v13b NUMBER; v13g NUMBER; v14 NUMBER; v15 NUMBER;
v16pkg NUMBER; v16static NUMBER; v17 NUMBER;
v_inj VARCHAR2(50) := 'x'' OR ''1''=''1';
v_erp NUMBER; v_crm NUMBER;
BEGIN
— V1 row count
SELECT COUNT(*) INTO v1 FROM dq_site;
dq_chk('V1','dq_site row count = 6000', CASE WHEN v1=6000 THEN 1 ELSE 0 END, 'rows='||v1);
— V2 domains
SELECT COUNT(*) INTO v2 FROM dq_domain;
dq_chk('V2','8 domains (OA..KMS)', CASE WHEN v2=8 THEN 1 ELSE 0 END, 'domains='||v2);
— V3 txn rows
SELECT COUNT(*) INTO v3 FROM dq_txn;
dq_chk('V3','dq_txn row count = 12000', CASE WHEN v3=12000 THEN 1 ELSE 0 END, 'rows='||v3);
— V4 parameterized lookup (by site_name) matches static count
SELECT COUNT(*) INTO v_erp FROM dq_site WHERE site_name='SITE_1';
v4 := dq_param_lookup('SITE_1');
dq_chk('V4','param lookup by name = static', CASE WHEN v4=v_erp THEN 1 ELSE 0 END, 'dyn='||v4||' stat='||v_erp);
— V5 dynamic filter region=1
SELECT COUNT(*) INTO v5 FROM dq_site WHERE region_id=1;
dq_chk('V5','dyn filter region=1 matches', CASE WHEN dq_count_dynamic(p_region_id=>1)=v5 THEN 1 ELSE 0 END, 'cnt='||v5);
— V6 dynamic filter CRM
SELECT COUNT(*) INTO v_crm FROM dq_site WHERE system_code='CRM';
dq_chk('V6','dyn filter CRM matches', CASE WHEN dq_count_dynamic(p_system_code=>'CRM')=v_crm THEN 1 ELSE 0 END, 'cnt='||v_crm);
— V7 injection neutralized by bind
v7 := dq_param_lookup(v_inj);
dq_chk('V7','bind neutralizes injection (=0)', CASE WHEN v7=0 THEN 1 ELSE 0 END, 'cnt='||v7);
— V8 assert rejects malicious identifier
BEGIN
v10 := dq_assert_name('site_id; DROP TABLE dq_site–');
EXCEPTION
WHEN OTHERS THEN v8ok := 1;
END;
dq_chk('V8','assert rejects malicious ident', v8ok, 'ok='||v8ok);
— V9 whitelist rejects invalid column
BEGIN
v10 := dq_validate_col('not_a_column');
EXCEPTION
WHEN OTHERS THEN v9ok := 1;
END;
dq_chk('V9','whitelist rejects bad column', v9ok, 'ok='||v9ok);
— V10 whitelist accepts valid column
v10 := dq_validate_col('site_name');
dq_chk('V10','whitelist accepts valid column', CASE WHEN v10='SITE_NAME' THEN 1 ELSE 0 END, 'col='||v10);
— V11 dynamic ORDER BY returns smallest site_id first
SELECT site_id INTO v11 FROM (SELECT site_id FROM dq_site ORDER BY site_id) WHERE ROWNUM=1;
dq_chk('V11','whitelist ORDER BY works', CASE WHEN v11=1 THEN 1 ELSE 0 END, 'min_id='||v11);
— V12 timezone shift exists
SELECT COUNT(*) INTO v12 FROM dq_site WHERE local_day <> TRUNC(CAST(created_utc AS DATE));
dq_chk('V12','timezone local_day shifts exist', CASE WHEN v12>0 THEN 1 ELSE 0 END, 'shifted='||v12);
— V13 multi-currency normalization
SELECT COUNT(*) INTO v13g FROM dq_txn;
SELECT ROUND(SUM(amount_usd),2), ROUND(SUM(amount_orig),2) INTO v13a, v13b FROM dq_txn;
dq_chk('V13','multi-cur: usd vs orig differ but sum>0',
CASE WHEN v13a>0 AND v13b>0 AND ABS(v13a-v13b)>1 THEN 1 ELSE 0 END,
'usd='||v13a||' orig='||v13b||' g='||v13g);
— V14 stats gathered
SELECT num_rows INTO v14 FROM user_tables WHERE table_name='DQ_SITE';
dq_chk('V14','stats num_rows > 0', CASE WHEN v14>0 THEN 1 ELSE 0 END, 'num_rows='||v14);
— V15 all-NULL filter => full set
v15 := dq_count_dynamic();
dq_chk('V15','all-NULL filter = full set', CASE WHEN v15=6000 THEN 1 ELSE 0 END, 'cnt='||v15);
— V16 AUTHID package result_cache matches static
SELECT ROUND(SUM(amount_usd),2) INTO v16static FROM dq_txn WHERE region_id=1;
v16pkg := ROUND(dq_pkg.region_sales(1),2);
dq_chk('V16','AUTHID pkg RESULT_CACHE matches', CASE WHEN v16pkg=v16static THEN 1 ELSE 0 END, 'pkg='||v16pkg||' stat='||v16static);
— V17 partition pruning present in single-day plan
BEGIN DELETE FROM plan_table WHERE statement_id='dq_p'; END;
EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID=''dq_p'' FOR SELECT /*+ FULL(t) */ COUNT(*) FROM dq_txn t WHERE local_day = DATE ''2026-06-15'' AND system_code=''ERP''';
SELECT COUNT(*) INTO v17 FROM plan_table WHERE statement_id='dq_p' AND operation LIKE '%PARTITION%';
dq_chk('V17','partition pruning in plan', CASE WHEN v17>0 THEN 1 ELSE 0 END, 'part_ops='||v17);
END;
/

— =====================================================================
— Q. Self-check summary
— =====================================================================
SET PAGES 100 LINES 200
SELECT chk_id, descr, pass, detail FROM dq_val ORDER BY chk_id;
SELECT 'CHECK SUMMARY: '||SUM(CASE WHEN pass='Y' THEN 1 ELSE 0 END)||'/'||COUNT(*)||' PASS' AS result
FROM dq_val;

— =====================================================================
— R. Conclusions (12, ASCII) — Dynamic Query Building best practices
— =====================================================================
PROMPT 1) ALWAYS parameterize dynamic SQL with bind variables (the USING clause of EXECUTE IMMEDIATE / OPEN…FOR). This is the single most important SQL-injection control. NOTE: DBMS_SQL can be denied (ORA-29471) in locked-down instances — native dynamic SQL is always available.
PROMPT 2) Never concatenate user input into SQL text. The dq_concat_danger demo leaks ALL rows on "x' OR '1'='1" — do not ship that pattern.
PROMPT 3) For dynamic identifiers (column names, table names, ORDER BY), use a WHITELIST (validate against user_tab_columns) — never a blacklist.
PROMPT 4) DBMS_ASSERT (SIMPLE_SQL_NAME / QUALIFIED_SQL_NAME / ENQUOTE_LITERAL) is the built-in sanitizer; a manual regex guard also works if it is rejected by default.
PROMPT 5) For conditional runtime filters with native dynamic SQL, give each optional filter TWO distinct placeholders — :x_n for the (IS NULL) guard and :x_v for the typed compare — and bind each value once per occurrence. Never reuse one name for both the untyped IS NULL and the typed compare (ORA-00932); EXECUTE IMMEDIATE binds by position, so supply one value per placeholder occurrence.
PROMPT 6) Use AUTHID CURRENT_USER for packages that build dynamic SQL in multi-tenant platforms so they run with the invoker's privileges (least privilege).
PROMPT 7) RESULT_CACHE + PARALLEL hints turn repeatable rollups into trillion-scale, low-latency queries; pair with current statistics.
PROMPT 8) Gather statistics (DBMS_STATS) so the optimizer picks the right plan for dynamically built predicates.
PROMPT 9) INTERVAL/RANGE partitioning on local_day enables partition pruning; EXPLAIN PLAN shows the PARTITION RANGE operation.
PROMPT 10) Store UTC (TIMESTAMP WITH TIME ZONE) and derive local_day with AT TIME ZONE so global reports respect each region's calendar day.
PROMPT 11) Normalize multi-currency with SUM(amount_usd); never SUM(amount_orig) across different currencies — amounts are not additive.
PROMPT 12) Oracle unquoted identifiers must start with a letter; '_name' => ORA-00911. Prefix dynamic objects with DQ_. Add EXIT so SQL*Plus terminates cleanly.

EXIT