Back to the Basics: SQL *PLUS – Working with Files

In one of our previous blog post, we talked about basic SQL*Plus editing commands. In this blog post, we will see how can we deal with files in SQL*Plus.

There are four basic commands to deal with files.

1. Spool – to store the data into file
2. Save – to save the file with default extension of .sql
3. Get – get the contents of the saved file into buffer.
4. Start – to load the file into buffer and execute it.

First we will start with ‘save’ command.

save: Using ‘save’ command, we can save the sql statement from buffer into file. With ‘save’ command, we can create new file, replace existing file or we can even append the new contents to the file. Let us start with example.

SQL> select * from dual;

We will save above SQL statement into file called a1. Usually file is saved in current working directory from which SQL*Plus is invoked unless full path is specified while saving file.

SQL> save a1;
Created file a1.sql

Let us try to save other sql statement.

SQL> select table_name
2 from user_tables
3 where rownum < 10;

If we try to save file again as a1, oracle will give an error.

SQL> save a1
SP2-0540: File “a1.sql” already exists.
Use “SAVE filename[.ext] REPLACE”.

As error message indicates, we need to use save command with replace option to replace the file.

SQL> save a1 replace;
Wrote file a1.sql

Now let us append following SQL statement, to the same file.

SQL> select object_name
2 from user_objects
3 where rownum < 5;

SQL> save a1 append;
Appended file to a1.sql

GET: Using this command, we can load the contents of the file into SQL*Plus buffer. We will check our changes using this command. Again default extension for ‘get’ command is also .sql and it will look for the file in current working directory from which SQL*Plus is invoked.

SQL> get a1
1 select table_name
2 from user_tables
3 where rownum < 10
4 /
5 select object_name
6 from user_objects
7* where rownum < 5;

START: Other command to load the contents of the file into SQL*Plus buffer is ‘start’ command. When ‘start’ command is used, it loads the file into buffer and also executes it. This is how it differs from ‘get’ command. Instead of start command, we can also use @ or @@ to load and execute the file.

SQL> start a1

Above command will load the file into buffer and execute it.

SPOOL: We can save the data returned by sql statement into file using spool command. If file extension is not specified, default extension of the spool file is .lst. It also creates the file in the local directory from which SQL*Plus is invoked, unless full path is specified. We can turn off the spooling by using ‘spool off’ command.

SQL> spool test
SQL> @a1

SQL> spool off

One can open up the test.lst file to check the data returned by the a1.sql.