Addenda Feb 20: I had one colleague point out a mistake, then another colleague point out something else, then I thought about it a little and found other problems…sigh. So if you grabbed the original version of this function, please chuck it out and use the fresh one 
The more I use SQL Macros, the more I think they are a great feature for building simply little utilities to make life easier for the DBA (or developer).
For example, ever wanted to access your current session trace file from within the session? It is relatively straightforward to do:
- query v$diag_info for the name of the file
- use an external table to query it.
For years I’ve had a little SQL script that did this work for me. But now, I can just as easily embed that into the database with a macro
SQL> create or replace
2 function current_tracefile return varchar2 sql_macro is
3 begin
4 return q'{
5 select payload
6 from v$diag_trace_file_contents c
7 where c.trace_filename = (
8 select substr(replace(t.value,i.value),2)
9 from v$diag_info t,
10 v$diag_info i
11 where i.name = 'Diag Trace'
12 and t.name = 'Default Trace File'
13 )
14 }';
15 end;
16 /
Function created.
SQL> select * from current_tracefile();
COL
--------------------------------------------------------------------------------------------------------------
Trace file C:ORACLEdiagrdbmsdb21db21tracedb21_ora_29468.trc
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.16.0.0.0
Build label: RDBMS_21.16.0.0.0DBRU_WINDOWS.X64_241005.1
Windows NT Version V10.0 OS Build 26200
ORACLE_HOME = C:oracleproduct21
Node name : BAILEY
CPU : 28 - type 866420 physical cores
Process Affinity : 0x0000000000000000
Memory (Avail/Total): Ph:33748M/65366M
Instance name: db21
Redo thread mounted by this instance: 1
Oracle process number: 0
Windows thread id: 29468
*** 2026-02-19T13:40:23.027947+08:00 (CDB$ROOT(1))
2026-02-19 13:40:23.018 : fsd_notify_cb: FsDirect not implemented
*** 2026-02-19T13:41:23.409822+08:00 (PDB21A(3))
*** SESSION ID:(162.52639) 2026-02-19T13:41:23.410804+08:00
*** SERVICE NAME:(pdb21a) 2026-02-19T13:41:23.410804+08:00
*** MODULE NAME:(SQL*Plus) 2026-02-19T13:41:23.410804+08:00
*** ACTION NAME:() 2026-02-19T13:41:23.410804+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2026-02-19T13:41:23.410804+08:00
*** CONTAINER ID:(3) 2026-02-19T13:41:23.410804+08:00
*** CLIENT IP:(127.0.0.1) 2026-02-19T13:41:23.410804+08:00
...
...
...