2.7. Scripts

You can always save the contents of the SQL*Plus buffer to a file for reuse—just type the SAVE command and a filename. If the file already exists, SQL*Plus tells you. You can do a “SAVE filename REPLACE,” which will replace the contents of an existing file. You also have the option to do a “SAVE filename APPEND,” which adds your SQL*Plus buffer statement to the end of the filename specified.

Where does the saved file go? In UNIX the save file goes into the directory from which you launched SQL*Plus. In Windows, it goes to the Oracle “bin” directory, but, if you set up a shortcut and changed the start-in directory, as recommended in Section 2.1, the file is saved to a dedicated directory. The following illustrates how we save a select statement into a script file.

					SQL> SELECT * FROM students;

STUDENT_ID STUDENT COLLEGE_MAJOR        STATUS
					---------- ------- -------------------- ------
					A101       John    Biology              Degree
					A102       Mary    Math/Science         Degree
					SQL> save students_select
Created file students_select
SQL>

We can also save a script using a full pathname.

					SQL> save D:sqlplus_scriptsstudentsstudents_select
Created file D:sqlplus_scriptsstudentsstudents_select
					SQL>
				

The file extension of a SQL*Plus saved file is always “.SQL.”

The file text will be identical to what you type during your session—it is the exact text from the SQL*Plus buffer. The semicolon is dropped and a forward slash is added as a last and final line in the saved file. To illustrate, type a select statement, but do not type a semicolon. Then type forward slash to execute the SQL*Plus buffer.

					SQL> SELECT * FROM students
  2  /

STUDENT_ID STUDENT COLLEGE_MAJOR        STATUS
					---------- ------- -------------------- ------
					A101       John       Biology              Degree
					A102       Mary       Math/Science         Degree
					SQL>
				

The aforementioned is identical to typing “SELECT * FROM students” and adding a semicolon. Either way, the actual text in a script file, from a SAVE command, will be two lines: the select statement minus the semicolon and a second line with a forward slash.

SELECT * FROM students    ".SQL" file line 1
/                         ".SQL" file line 2
				

The three options with the SQL*Plus SAVE command are:

• SAVE filename

• SAVE filename REPLACE

• SAVE filename APPEND

You execute a SQL script from SQL*Plus with the syntax: @filename. For example:

					SQL> @students_select

STUDENT_ID STUDENT COLLEGE_MAJOR        STATUS
					---------- ------- -------------------- ------
					A101       John    Biology              Degree
					A102       Mary    Math/Science         Degree
					SQL>
				

During development, SQL*Plus scripts are used to drop and recreate all the tables of an application. Also for development, scripts are used to load test data. Scripts can be embedded within UNIX Korn Shell scripts to perform daily Oracle backups. They are used for ad hoc troubleshooting, (i.e., to show existing locks on database objects, to show performance of memory usage, and many other database metrics).

You can create a SQL script from SQL*Plus with a SAVE command, but scripts of length are usually created with a host editor. The contents of a SQL script can include one or more SQL statements, plus SQL*Plus commands. You can comment within a SQL*Plus script with a double dash. The following is a SQL*Plus script that contains two SQL SELECT statements plus two comment lines—the file is five lines.

					-- Filename: SELECT_STUDENTS.SQL
-- Script to select STUDENTS data.
SELECT student_name, college_major FROM students;
SELECT student_id, student_name, college_major FROM students
WHERE status = 'Degree';

The aforementioned script would execute from SQL*Plus with:

					SQL> @select_students

SQL*Plus scripts can contain SQL*Plus commands. This includes SQL*Plus formatting commands. A common SQL*Plus command often embedded in a SQL*Plus script is the COLUMN command. The SQL*Plus COLUMN command is used to restrict the number of characters, for a single column, used in the SQL output as well as designate a specific heading.

					-- Filename: SELECT_STUDENTS.SQL
-- Script to select STUDENTS data.
COLUMN college_major FORMAT A12 HEADING 'College|Major'
COLUMN student_name  FORMAT A7  HEADING 'Student|Name'
COLUMN student_id    FORMAT A7  HEADING 'Student|ID'
SELECT student_name, college_major FROM students;
SELECT student_id, student_name, college_major FROM STUDENTS
WHERE status = 'Degree';

SQL> @select_students

Student Student College
					ID      Name    Major
					------- ------- ------------
					A101    John    Biology
					A102    Mary    Math/Science
				

A SQL statement always ends with a semicolon. A SQL*Plus command does not need to end with a semicolon. The next section illustrates other SQL*Plus commands: HEADING, PAGESIZE, TERM, and FEEDBACK.