SQL*Plus Scripting

I have been doing a lot of work for implementations with SQL scripts recently.

SQL scripts are extremely useful for automation purposes. I also like to output the contents of my scripts to a file so I have a permanent record.

When I first started looking at the scripts, they typically looked like the following:

 DROP TABLE t;  
 CREATE TABLE t  
 (  
  t_id NUMBER,  
  t_txt VARCHAR2(50)  
 );  
 CREATE UNIQUE INDEX pk_t ON t(t_id);  
 ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY t(t_id) USING INDEX;  
 exit  

Running this script through SQL*Plus produces the following output.

 top@ORCL11G> @sql_scripts.sql  
 Table dropped.  
 Table created.  
 Index created.  
 Table altered.  

Bare bones, isn't it? The problems at first sight are
- No output to a file, so no permanent record
- No record on the screen of which command is currently executing - imagine a situation with a large script containing long running updates - how can progress be assessed?

Lets amend the script to be the following:

 spool s2_log.txt  
 set echo on  
 DROP TABLE t;  
 CREATE TABLE t  
 (  
  t_id NUMBER,  
  t_txt VARCHAR2(50)  
 );  
 CREATE UNIQUE INDEX pk_t ON t(t_id);  
 ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY t(t_id) USING INDEX;  
 exit  

The "spool" command tells Oracle to direct output to a file. The "set echo on" command is SQL*Plus specific and tells Oracle to send the SQL statement it is executing to the screen.

Please see the sample output below:

 top@ORCL11G> DROP TABLE t;  
 Table dropped.  
 top@ORCL11G> CREATE TABLE t  
  2 (  
  3  t_id NUMBER,  
  4  t_txt VARCHAR2(50)  
  5 );  
 Table created.  
 top@ORCL11G>  
 top@ORCL11G> CREATE UNIQUE INDEX pk_t ON t(t_id);  
 Index created.  
 top@ORCL11G>  
 top@ORCL11G> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (t_id) USING INDEX;  
 Table altered.  

Far more user friendly! As can be seen, SQL*Plus outputs exactly the commands and the results.

Some of the other useful commands I use are:
host - host command is also very useful - it executes and Operating System command without leaving SQL*Plus. For example, if you forget the name of the script you need to run, you can enter "host ls" at the prompt and it will list the contents of the current directory.

set timing on - prints the execution time per statement to the screen

set serveroutput on - Shows output from PL/SQL blocks when dbms_output is used

connect user/password - Allows the script to log on as a different user to execute commands in the other schema

There are far more options that I have covered here. The Oracle documentation shows all of the possibilities.

That's it for the moment - I will write another post regarding bind variables in scripts.