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

First, go to your Documents library and create a folder called oracle in the top directory (you do not need to exit Oracle to do this). Then return to Oracle and type the following:

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.

Follow these steps to create a SQL query file that produces a report on the salaries of employees in department 30:

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.

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 formatting 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.

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.

For example, to run the personnel file:

start personnel 

Alternatively the @ symbol can be used instead of start:

@ empquery

To just retrieve the query:

GET filename

To show the last command typed in:

list

Further examples

To create a SQL query file called query2, which produces a report on Salesmen earning more than 2500:

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; 

Commments in the code:

• REM at the start of the line indicates a comment
• 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

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. Then run it:

start emplist

Create a query file called emp20.sql that lists all the employees in department 20 and save the results in a file called emp.txt:

ed emp20

then add:

spool emp.txt
select * from emp
where deptno = 20;
spool off

Exercise 2.2

2.2.1 What do you need to type to run the emp20.sql file?

2.2.2 Create a query that lists all the clerks and save the results in a file called clerk.txt. The output should be similar to:

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

2.2.3 Use the editor to create a file called query3, which displays the name, salary and commission of all salesmen.

When you type:

 start query3

the results should be similar to:

ENAME             SAL       COMM
---------- ---------- ----------
ALLEN            1600        300
WARD             1250        500
MARTIN           1250       1400
TURNER           1500          0

Summary of the commands

• SPOOL filename: saves the results of a query
• ED filename: creates, or edits, a SQL query file
• START filename: runs the SQL query file
• @ filename: runs the SQL query file
• LIST: shows the contents of the last query run
• GET filename: shows the contents of a query file (but does not run it)

Next Step

Creating a login file.