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 l_filename varchar2(200);
4 begin
5 select val
6 into l_filename
7 from (
8 select substr(value,1+instr(value,'\',-1)) val
9 from v$diag_info
10 where name = 'Default Trace File'
11 and value like '%\%'
12 union all
13 select substr(value,1+instr(value,'/',-1))
14 from v$diag_info
15 where name = 'Default Trace File'
16 and value like '%/%'
17 )
18 where rownum = 1;
19
20 return replace(q'{
21 select col
22 from external (
23 ( col varchar2(4000) )
24 type oracle_loader
25 default directory tracedir
26 access parameters
27 ( records delimited by newline
28 nobadfile
29 nologfile
30 nodiscardfile
31 )
32 location ( '@@@' )
33 reject limit unlimited ) ext
34 }', '@@@', l_filename);
35 end;
36 /
Function created.
SQL> select * from current_tracefile();
COL
--------------------------------------------------------------------------------------------------------------
Trace file C:\ORACLE\diag\rdbms\db21\db21\trace\db21_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:\oracle\product\21
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
...
...
...
Learning is not a spectator sport 
Got some thoughts? Leave a comment