Home » SQL*PLUS » Saving, Editing, and Running SQL Files in SQL*Plus

Saving, Editing, and Running SQL Files in SQL*Plus

By

In this tutorial, you'll learn how to save, edit, and run an SQL file in SQL*Plus. Some of the SQL*Plus file commands are listed in the following table:

SQL*Plus Commands for File (letters and words in brackets are optional)

Command Description
SAV[E] filename
[{REPLACE} |
APPEND}]		 Saves the SQL*Plus buffer to a file specified by filename. You can append the content of the buffer to an existing file using the APPEND command.
GET filename Retrieve the contents of the SQL file specified by filename into the SQL*Plus buffer.
STA[RT] filename Run the contents of the SQL file specified by filename.
@ filename Same as START command.
ED[IT] Copies the contents of the SQL*Plus buffer to a temporary file and then starts the default text editor.
ED[IT] filename Same as the EDIT command, but you can specify a file to start editing using the filename parameter.
SPO[OL] filename Copies the output from SQL*Plus to the file specified by filename.
SPO[OL] OFF Stops the copying of output from SQL*Plus to the file and then closes the file.

Let's take a look at some examples of using these SQL*Plus commands.

select empno, ename, job from emp
  where deptno = 30;

SAVE Command

Now the above command is in the SQL*Plus buffer. You can save the buffer contents to a file using the SAVE command as shown below:

SAVE F:\code\sqlplus\emp_query.sql

For Linux/Unix:

SAVE usr1/work/sqlplus/emp_query.sql

Output

Created file F:\code\sqlplus\emp_query.sql

GET Command

The next example uses the GET command to retrieve the contents of the emp_query.sql file.

 GET F:\code\sqlplus\emp_query.sql

Output

SQL> GET F:\code\sqlplus\emp_query.sql
  1  select empno, ename, job from emp
  2* where deptno = 30

Now you can run the above command by using the forward slash (/) as shown below:

/

Output

     EMPNO ENAME                          JOB
---------- ------------------------------ ---------
      7521 WARDs                          SALESMAN
      7654 MARTIN                         SALESMAN
      7698 BLAKE                          MANAGER
      7844 TURNER                         SALESMAN
      7900 JAMES                          CLERK

EDIT Command

You can use the EDIT command to edit the contents of the file as shown in the below example:

EDIT F:\code\sqlplus\emp_query.sql

To edit the contents of the SQL*Plus buffer, use the EDIT command without filename parameter.

EDIT

START/@ Command

The following examples use the START and @ command to load and run the contents of the F:\code\sqlplus\emp_query.sql file:

START F:\code\sqlplus\emp_query.sql

Output

     EMPNO ENAME                          JOB
---------- ------------------------------ ---------
      7521 WARDs                          SALESMAN
      7654 MARTIN                         SALESMAN
      7698 BLAKE                          MANAGER
      7844 TURNER                         SALESMAN
      7900 JAMES                          CLERK

You can also use the @ command to run the SQL file. Below is an example:

 @ F:\code\sqlplus\emp_query.sql

See also: