Ora2:Saving and Retrieving Queries

Main Page >> Oracle and SQL >> Workbook >> Saving and Retrieving Queries

Saving a query

If you wish to keep a query that you have just typed in:

SAVE filename

saves the query in filename.sql

Note:

• that this instruction only saves the SQL command and not the associated formatting commands (such as headers or footers). To save both SQL and format commands you have to use the editor.
• You do not need to include the .sql file extension, this will be added automatically.

For example, type in the following to create and save a query file:

select * from emp;
save empquery

It should save the query to a file called empquery.sql in your Documents folder (u:\ drive) by default. You can save the file somewhere else, such as a memory stick, or to a subdirectory.

Assuming you have a memory stick and it is mapped to the e: drive, type the following:

select * from dept;
save e:\deptquery

Or create a folder called oracle in your Documents and save a new query there:

select * from customer;
save u:\oracle\customerquery

It is advisable not to include spaces in your folders or filenames.

Creating a query via the Editor

If you know you want to save a query for future use, for example, to hand-in for your coursework, you can use the default editor to create (or edit) the query.

For example, to create a query file that produces a report on the salaries of employees in department 30. The file will also include some formatting commands.

1. Create a file

Create a file called personnel (remember, you do not need to include the .sql file extension):

ed personnel

You can also use this method to edit a file previously created.

1. Editor

This will bring up a Notepad window (Windows) or vi (Linux). Then type in the following:

TTITLE 'ACME WIDGET SALES DEPT PERSONNEL REPORT'
BTITLE 'COMPANY CONFIDENTIAL'
SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = 30;
BTITLE OFF
TTITLE OFF

TTITLE and BTITLE are SQL*Plus commands to add header/footers to a report. They need to be switched off at the end; otherwise they will appear with any future queries. Further formatting commands will be introduced later.

1. Save and exit

Finally save and exit the editor:

File>Save (windows)
File>Exit

or

: wq (Linux)

Note:

• if using Linux, filenames are case sensitive, so if a file is saved in upper case then it must be retrieved in upper case. The file suffixes that Oracle appends are always in lowercase.
• You can not access Oracle whilst you are using the editor.

Retrieving and executing a query

The syntax to run a query file is:

START filename

This retrieves the query stored in filename.sql and runs it.

Alternatively the @ symbol can be used instead of start:

@ filename

To just retrieve the query: GET filename

To show the last command typed in: list

For example, to run the file created previously:

start personnel 

Create a query that lists all the employees in department 20 and save the results in a file called EMP.txt: SPOOL EMP.TXT

	SELECT * FROM EMP
 	WHERE DEPTNO = 20;
 	SPOOL OFF

 Create a query that lists all the clerks and save the results in a file called CLERKS.txt:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

---------- --------- ---------- --------- ---------- ---------- ----------

7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
7369 SMITH      CLERK           7902 17-DEC-80        800                    20
7900 JAMES      CLERK           7698 03-DEC-81        950                    30
7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

The editor can be used to create a file from scratch. To create a command file which produces a report on Salesmen earning more than 2500, start Notepad by typing: ed query2 Then type in the following: REM List salespeople who earn more than 2500 per month SELECT DEPTNO, ENAME, SAL FROM EMP WHERE JOB = 'SALESMAN' AND SAL + COMM > 2500; REM at the start of the line indicates a comment, alternatively, if the documentation spans several lines, use /* at the start and */ at the end of the comment. Save as before and to execute it: start query2  Use Notepad to create a file called QUERY3, which displays the name, salary and commission of all salesmen.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

---------- --------- ---------- --------- ---------- ---------- ----------

7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

SPOOL and Save

The SQL query file could include a SPOOL command so that the results of the query can also be saved:

ed emplist

In the editor type:

spool emplist.txt
select * from emp;
spool off

Save and exit as before.

Summary of the commands

• SPOOL saves the results of a query
• ed creates, or edits, a SQL query file
• start runs the query file