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