Oracle SQL

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

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 3 mins read

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)

CommandDescription
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 filenameRetrieve the contents of the SQL file specified by filename into the SQL*Plus buffer.
STA[RT] filenameRun the contents of the SQL file specified by filename.
@ filenameSame 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] filenameSame as the EDIT command, but you can specify a file to start editing using the filename parameter.
SPO[OL] filenameCopies the output from SQL*Plus to the file specified by filename.
SPO[OL] OFFStops 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: