-- xplanx.sql
-- William Robertson - www.williamrobertson.net
-- Transparent "dbms_xplan.display_cursor" utility for SQL*Plus.
--
-- Usage:
-- @xplanx
-- Executes the contents of the current SQL buffer (i.e. the most recent SQL statement to be run, edited, fetched etc,
-- invokes dbms_xplan.display_cursor() and then places the statement back in the buffer, as far as possible leaving everything
-- the way it was, including any serveroutput setting (which it disables before executing the statement).

set term off
store set sqlplus_settings.tmp replace
set define `

-- Capture the current SQL buffer (use generic filename because we can't look up the session ID without losing the current SQL):
save xplanx.buf repl

-- Now we can capture session ID and use it to rename xplan.buf and sqlplus_settings.tmp to xplanx_<sid>.buf and sqlplus_settings_<sid>.tmp,
-- so that other sessions can call xplanx without overwriting this session's saved details:
col sid new_value sid
select sys_context('userenv','sid') as sid from dual;
-- Delete any old tempfiles:
ho prompt > xplanx_`sid..buf
ho prompt > sqlplus_settings_`sid..tmp
ho del xplanx_`sid..buf
ho del sqlplus_settings_`sid..tmp
-- Give generic tempfiles session-specific names to allow this to be used by multiple sessions:
ho ren xplanx.buf xplanx_`sid..buf
ho ren sqlplus_settings.tmp sqlplus_settings_`sid..tmp

-- Adjust terminal window size to match linesize below
define terminal_width = 210

set linesize `terminal_width
set pagesize 0 trimspool on heading off pause off feed off verify off timing off autotrace off flagger off tab off trimout on serverout off arraysize 200 flush off appinfo off

ttitle off

-- To do: detect the current setting before altering, then restore this at the end:
alter session set statistics_level = all;

timing start "Query execution time"
-- Execute the SQL buffer that was captured earlier:
-- set feed on timi on head on autotrace off
-- spool xplanx.log

prompt Calling xplanx_`sid..buf
@xplanx_`sid..buf
-- ho type xplan.buf
-- spool off
-- set feed off timi off head on autotrace off
col plan_table_output clear
col plan_table_output format a`terminal_width
set term on

timing stop

prompt All rows fetched. Calling dbms_xplan.display_cursor:
select p.plan_table_output
from   table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +OUTLINE +ADAPTIVE +PARTITION +HINT_REPORT +NOTE')) p;

get xplanx_`sid..buf nolist

@sqlplus_settings_`sid..tmp
set term on
-- Note "del" command assumes Windows. Change to "rm" for Unix/Linux/Mac or see xplan.sql for elaborate dynamic approach:
host del sqlplus_settings_&sid..tmp xplanx_&sid..buf
undef terminal_width

-- host type xplanx.log