Oracle APEX Interactive Report Based on a PL/SQL Function: Trace SQL and Session State

Select the code and use your copy shortcut. Changes in this preview are temporary.

<!doctype html>
<html lang="en-US"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Oracle APEX Interactive Report Based on a PL/SQL Function: Trace SQL and Session State</title><style>:root{--bg:#edf1f5;--paper:#fff;--ink:#193449;--accent:#245d86;--tint:#dfeaf2;--line:#c4d0da;--muted:#40586a;--button:#fff}*{box-sizing:border-box}body{margin:0;background:var(--bg);color:var(--ink);font:17px/1.55 system-ui,sans-serif}main{max-width:1000px;margin:auto;padding:22px clamp(18px,4vw,42px) 38px}header{border-bottom:2px solid var(--accent);padding-bottom:14px;margin-bottom:18px}h1{font-size:clamp(26px,4vw,36px);line-height:1.15;margin:4px 0 10px}h2{font-size:22px;line-height:1.3;margin:0 0 14px}h3{font-size:19px}p{margin:0 0 14px}.eyebrow{font-size:16px;font-weight:750;color:var(--accent)}.lede{max-width:800px}.panel{background:var(--paper);border:1px solid var(--line);border-radius:9px;padding:20px;margin:18px 0}.workbench{border-top:4px solid var(--accent)}.grid{display:grid;grid-template-columns:repeat(2,minmax(0,1fr));gap:16px}.grid>*{min-width:0}label{display:block;font-size:16px;font-weight:650;margin:12px 0 6px}input,textarea,select{font:inherit;color:var(--ink);background:var(--paper);border:1px solid #7b8893;border-radius:5px;padding:10px;width:100%;min-width:0}textarea{resize:vertical;line-height:1.45}input[type=checkbox],input[type=radio]{width:21px;height:21px;vertical-align:middle;margin-right:9px}button{font:650 16px/1.4 system-ui,sans-serif;padding:11px 15px;min-height:46px;background:var(--accent);border:2px solid var(--accent);color:var(--button);border-radius:5px;cursor:pointer}button:disabled{opacity:.55;cursor:default}.buttons{display:flex;gap:9px;flex-wrap:wrap;margin:14px 0}.buttons>*{flex:1 1 auto}button:focus-visible,input:focus-visible,textarea:focus-visible,select:focus-visible,summary:focus-visible{outline:3px solid #c05b00;outline-offset:3px}.result{font:600 17px/1.6 ui-monospace,monospace;white-space:pre-wrap;overflow-wrap:anywhere;background:var(--tint);padding:16px;border-left:4px solid var(--accent);min-height:65px;margin:16px 0}.sample{padding:14px 16px;border-left:3px solid var(--accent);background:var(--tint);margin:12px 0}.muted{color:var(--muted)}pre,code{font:16px/1.6 ui-monospace,monospace;white-space:pre-wrap;overflow-wrap:anywhere}pre{margin:12px 0}table{border-collapse:collapse;width:100%;table-layout:fixed;font-size:16px}th,td{text-align:left;vertical-align:top;padding:10px 8px;border-bottom:1px solid var(--line);overflow-wrap:anywhere}th{background:var(--tint)}svg{max-width:100%;height:auto;display:block}details{margin:20px 0}summary{font-weight:700;cursor:pointer}footer{margin-top:28px;border-top:1px solid var(--line);padding-top:18px;font-size:16px}a{color:var(--accent)}.reading{font-family:Georgia,serif}.codeview{border-radius:0;box-shadow:7px 7px 0 var(--tint)}.token{display:inline-block;padding:8px 12px;margin:4px;border:1px solid var(--line);border-radius:4px;background:var(--paper)}.swatch{min-height:30px}.split{display:grid;grid-template-columns:1.1fr 1fr;gap:22px}ol{padding-left:25px}li{margin:9px 0}@media(max-width:600px){main{padding:16px}.grid,.split{grid-template-columns:1fr}.panel{padding:16px;margin:14px 0}h1{font-size:27px}header{padding-bottom:10px}.buttons{gap:8px}.result{padding:13px}th,td{padding:8px 5px}}</style></head><body><main><header><div class="eyebrow">Original practice fixture · local workbench</div><h1>Which status reached the query?</h1><p class="lede">The task fixture contains TASK_ID 1 with STATUS OPEN and TASK_ID 2 with STATUS CLOSED. Allowed P1_MODE values are ALL and FILTERED; P1_STATUS values are OPEN and CLOSED. ALL expects IDs 1 and 2; FILTERED/OPEN expects ID 1; FILTERED/CLOSED expects ID 2. The fixed SQL retains the bind placeholder :P1_STATUS instead of concatenating a status value.</p></header><pre id="result" class="result" role="status" aria-live="polite" aria-atomic="true">Entered server FILTERED/OPEN → expected row 1, even when visible status says CLOSED.</pre><label for="mode">Entered server P1_MODE</label><input id="mode" value="FILTERED" ><label for="server">Entered server P1_STATUS</label><input id="server" value="OPEN" ><label for="visible">Visible-only status · not a server read</label><input id="visible" value="CLOSED" ><div class="buttons"><button type="button" id="inspect">Build SQL/snapshot trace</button></div><section class="panel"><h2>Returned SQL text</h2><pre id="sql"></pre></section><section class="panel"><h2>Original fixture</h2><p>TASK_ID 1 / STATUS OPEN</p><p>TASK_ID 2 / STATUS CLOSED</p></section><footer><p>This is a local fixed-fixture trace, not APEX, PL/SQL execution, server session inspection, or a remote Oracle connection.</p><p>Use a prepared HTML explanation in <a href="https://nos-workspace.com/">NOS</a> when reviewing this SQL-branch and snapshot trace builder. Eligible static instructions can be revised in visual editing mode; dynamic outputs follow their controls.</p></footer></main><script>
(()=>{'use strict';const $=s=>document.querySelector(s);const out=s=>{$('#result').textContent=s;};const val=id=>$('#'+id).value;const num=id=>{const s=val(id).trim();return s===''?NaN:Number(s);};const on=(id,fn)=>$('#'+id).addEventListener('click',fn);const watch=ids=>{for(const id of ids)for(const event of ['input','change'])$('#'+id).addEventListener(event,()=>out('Inputs changed. Run the action to update this result.'));};const fmt=(n,d=4)=>Math.abs(n)<1e-10?(0).toFixed(d):n.toFixed(d);
(function(){watch(['mode','server','visible']);for(const id of ['mode','server','visible'])for(const event of ['input','change'])$('#'+id).addEventListener(event,()=>{$('#sql').textContent='';});on('inspect',()=>{const mode=val('mode').trim(),status=val('server').trim();if(!['ALL','FILTERED'].includes(mode)||!['OPEN','CLOSED'].includes(status)){$('#sql').textContent='';out('Held: mode ALL/FILTERED and snapshot status OPEN/CLOSED required.');return;}$('#sql').textContent='WITH tasks AS (SELECT CAST(1 AS NUMBER) AS TASK_ID, CAST(\'OPEN\' AS VARCHAR2(6)) AS STATUS FROM dual UNION ALL SELECT CAST(2 AS NUMBER), CAST(\'CLOSED\' AS VARCHAR2(6)) FROM dual) SELECT TASK_ID, STATUS FROM tasks'+(mode==='FILTERED'?' WHERE status = :P1_STATUS':'');out('Local expected trace | mode '+mode+' | server snapshot '+status+' | visible-only '+val('visible')+' | Expected row IDs '+(mode==='ALL'?'1, 2':status==='OPEN'?'1':'2')+'. No SQL executed.');});})();
})();
</script></body></html>