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:

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

host setprmpt.bat
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
‘,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


How to set the SQL prompt to the current directory

Leave a Reply

Your email address will not be published. Required fields are marked *

fifty one + = sixty