Script Files in SQL * Plus

SQL*Plus provides commands to save the SQL buffer to a file, as well as to run SQL statements from a file. SQL statements saved in a file are called a script file.

SAVE Command

SQL buffer is saved to an operating system file using the command SAVE .

REPLACE Command

By default, SAVE will not overwrite an existing file; you need to use the keyword REPLACE to overwrite an existing file. If you do not provide an extension, the saved file will have an extension of .sql.

EDIT Command

You can edit the saved file using the EDIT command.

GET Command

You can bring the contents of a script file to the SQL buffer using the GET
filename command.

Runnig Script files

If you wish to run a script file, you may use the command START . You can also run a script file using @.
An @@ used inside a script file looks for the filename in the directory where the parent is saved and executes it.

Examples of using the file commands:

/* List the SQL buffer */
SQL> L
1 SELECT EMPNO, ENAME
2* FROM EMP
/* Save the buffer to a file named myfile. The default
extension will be .SQL */

SQL> save myfile
Created file myfile

/* Edit the file, add a line to the SQL statement, editing
window not shown here */
SQL> edit myfile

/* List the buffer, the buffer listed is the old buffer,
edited changes not reflected, that is because we edited
the file */
SQL> L
1 SELECT EMPNO, ENAME
2* FROM EMP

/* Bring the file contents to the buffer */
SQL> GET MYFILE
1 SELECT EMPNO, ENAME
2 FROM EMP
3* WHERE EMPNO = 1234

/* List the buffer to verify */
SQL> L
1 SELECT EMPNO, ENAME
2 FROM EMP
3* WHERE EMPNO = 1234
/* Save the buffer again to the same file */

SQL> save myfile

File “myfile.SQL” already exists.
Use “SAVE filename REPLACE”.

/* Error returned, save using REPLACE keyword */

SQL> save myfile repl
Wrote file myfile

/* Run a file */
SQL> start myfile
1 SELECT EMPNO, ENAME
2 FROM EMP
3* WHERE EMPNO = 1234
no rows selected
SQL>