Sometimes people will ask why can’t Toad work more like SQL*Plus – and the funny thing is the answer is quite often it does. You really just need to know how SQL*Plus works – and then look for the same feature within Toad. Let’s take a very, very common scenario that gets asked almost every quarter. Look at my SQL*Plus SELECT command in the screen snapshot below. Suppose that I always like to define the six SET commands for a SQL*Plus session such that my output looks and behaves the way I personally like. It would be far too much manual work for me to type those six commands every time I start a new SQL*Plus session. So maybe I go one step further – and simply place those six SET commands in a script called BERT.SQL. Now if that script is in my SQL*Plus execution and/or working directory, or if it’s in my SQL_PATH environment variable, I can now simply start SQL*Plus, type @BERT, and then enter my SELECT command. Now thing will work the way I want. But that’s still a manual process – even if it is just one extra step. SQL*Plus actually has a mechanism in place to support this – and it just so happens that Toad fully supports it. Let’s look further into this idea.

 

 

SQL*Plus supports the concepts of automatically executed user and site profile scripts. These are simply SQL script files containing SQL*Plus commands that are executed automatically whenever SQL*Plus is launched. Think of tem like a DOS autoexec.bat file or a UNIX login.sh script. These user and site profiles scripts are named “login.sql” and “glogin.sql”, respectively. When SQL*Plus starts, it simply looks for and executes these scripts in the working directory or SQL_PATH. That’s all there is to it. So, let’s look at a simple example – where SQL*Plus will print “Welcome to SQL*Plus” and list both the current database SID and connected user name. Here’s the login.sql script and it’s effect:
 

 

 

So how do we use this concept in Toad? As with most things it Toad, it’s an option. And it works just the same as in SQL Plus. The screen capture below shows the options for both the site and user profile scripts. Note too that you have to choose the check box to enable these options in order for them to work properly. So let’s see what effect they have inside the Toad editor.

 

 

 

Here it is working in Toad as we would expect: