SQL Macros make so many things easier

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:\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
...
...
...

3 responses to “SQL Macros make so many things easier”

Volodymyr

February 19, 2026 at 4:15 pm

The magic of SQL macros or PL/SQL stored procedures is abstraction.
They help to hide the messy details of your database environment, so the exact same query works whether your db is on a local server. dev db container, Exadata, ExaCS or Autonomous DB in the cloud.

Loading…

Mustafa KALAYCI

February 19, 2026 at 6:54 pm

Thanks Connor, really useful and teeny tiny contribution just to prevent questions for “ORA-06564: Object TRACEDIR does not exist or is not accessible to the user.” error.

declare
x_dir_script varchar2(1000);
begin
select ‘create directory tracedir as ”’||value || ”” into x_dir_script from v$diag_info where name = ‘Diag Trace’;
execute immediate x_dir_Script;
end;
/

Loading…

1. Connor McDonald
  
  March 2, 2026 at 10:20 pm
  
  Good point but I rejigged the code to avoid it entirely
  
  Loading…

Learning is not a spectator sport