Have you ever had a SQL*Plus window open, and did not remember what it’s current
directory was? This tip will show a method to set your SQL*Plus prompt to include
the current directory.
The SET SQLPROMPT command can be used to set the SQL*Plus prompt to any
text string you want. The trick is to get access to the current directory
from SQL*Plus, so you can use it in SET SQLPROMPT.
We have access to the current directory from the OS
(via ‘cd’ in DOS/NT and ‘pwd’ in Unix). We can also call an OS script by
using the SQL*Plus HOST command. Using these two capabilities, I wrote two
scripts that together performed the function I wanted:
A .bat script that writes a SQL script to do the actual SET SQLPROMPT,
using the ‘cd’ command to insert the current directory.
A sql script that executes the .bat script and runs the SQL script that it
has written.
Here are the two scripts:
setprmpt.bat
—————–
echo set define $ > tmp.sql
echo column curdir noprint new_value curdir >> tmp.sql
echo SELECT REPLACE(‘ >> tmp.sql
cd >> tmp.sql
echo ‘,CHR(10),”) curdir FROM DUAL; >> tmp.sql
echo set sqlprompt “($curdir) SQL> ” >> tmp.sql
setprmpt.sql
—————–
host setprmpt.bat
@tmp
set define &
Here is an example of using the scripts to set the prompt:
SQL> @setprmpt
(C:\d2k\working) SQL>
How these scripts work
The ‘host’ command in the .sql scriptexecutes the .bat script.
The .bat script then writes the following commands to a temporary sql script (called tmp.sql):
set define $
column curdir noprint new_value curdir
SELECT REPLACE(‘
C:\d2k\working
‘,CHR(10),”) curdir FROM DUAL;
set sqlprompt “($curdir) SQL> ”
The .sql script then executes this temporary script file. The TMP.SQL script
had to be so complicated because I was only using the DOS output redirection
capabilities (‘>’ and ‘>>’) and I could only get the current directory into
the file on it’s own line. The REPLACE(..,CHR(10),”) command removes the
carriage return before and after the directory line.
The ‘column .. new_value’ command is a SQL*PLUS command that allows the
value of a selected column to be placed into a SQL*Plus variable (in this
case ‘curdir’). Therefore, when the following SELECT.. is run, the text
string of the current directory is placed in the ‘curdir’ SQL*Plus
variable. Then the SET SQLPROMPT uses this variable to set the prompt.
Another feature of this technique, is that you now have the SQL*Plus
variable with the current directory available for other uses in this
SQL*Plus session. For example, it can be used in a select like:
(C:\fmpt\sql) SQL> select ‘&curdir’ from dual;
old 1: select ‘&curdir’ from dual
new 1: select ‘C:\fmpt\sql’ from dual
‘C:\FMPT\SQ
———–
C:\fmpt\sql