Useful Prompt in SQL*Plus

By Hervé Deschamps, Oracle Corporation.

Do you use SQL*Plus to connect to various instances? Do you loose track of which SQL*Plus window is connected to which Database? You can set the SQL*Plus prompt to remind you.

Do you like SQL*Plus to help you time the execution time of certain queries? Do you like the idea of being able to go get a cup of tea while the machine does the work and come back to your screen to see the result and how much time it took? You can set the SQL*Plus prompt  to help you with that too.

If you follow the instructions below you can get something like this when you connect to SQL*Plus:

Connected to:
Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.4.0.0 - Production

08:35:10 HCIT>

HCIT is the SID of the database where I am currently connected. Every prompt indicates the time. So when SQL*Plus is done with one command it will display the new time on the prompt for next command. This tells you exactly when the previous command terminated.

How do you set SQL*Plus to do this?

set lines 100
set pages 100
set termout off
col dbname new_value prompt_dbname
select substr(global_name,1,instr(global_name,'.')-1) dbname
from global_name;
set sqlprompt "&&prompt_dbname> "
set termout on
set time on
 


Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others.  He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at hdescham@us.oracle.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.