You know that if we want to execute another script from the current script directory, we can call it through @@, but sometimes we want to know the current path exactly, for example if we want to spool something into the file in the same directory.
Unfortunately we cannot use “spool @spoolfile”, but it is easy to find this path, because we know that SQL*Plus shows this path in the error when it can’t to find @@filename.
So we can simply get this path from the error text:
rem Simple example how to get path (@@) of the current script. rem This script will set "cur_path" variable, so we can use &cur_path later. set termout off spool _cur_path.remove @@notfound spool off; var cur_path varchar2(100); declare v varchar2(100); m varchar2(100):='SP2-0310: unable to open file "'; begin v :=rtrim(ltrim( q'[ @_cur_path.remove ]',' '||chr(10)),' '||chr(10)); v:=substr(v,instr(v,m)+length(m)); v:=substr(v,1,instr(v,'notfound.')-1); :cur_path:=v; end; / set scan off; ho (rm _cur_path.remove 2>&1 | echo .) ho (del _cur_path.remove 2>&1 | echo .) col cur_path new_val cur_path noprint; select :cur_path cur_path from dual; set scan on; set termout on; prompt Current path: &cur_path
I used here the reading file content into variable, that I already showed in the “SQL*Plus tips. #1″.
UPDATE: I’ve replaced this script with a cross platform version.
Also I did it with SED and rtrim+ltrim, because 1) I have sed even on windows; and 2) I’m too lazy to write big PL/SQL script that will support 9i-12c, i.e. without regexp_substr/regexp_replace, etc.
But of course you can rewrite it without depending on sed, if you use windows without cygwin.
PS. Note that “host pwd” returns only directory where SQL*Plus was started, but not executed script directory.