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 a query file and then save it:

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 Notepad

If you know you want to save a query for future use, for example, to hand-in for your coursework, you can use Notepage to create the query, which will then save it also.

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.

First create a new file called personnel (remember, you do not need to include the .sql file extension)

ed personnel

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.

Save and exit the file

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.

SPOOL saves the results of a query; ed creates a SQL query file. The SQL query file could include a SPOOL command so that the results of the query can also be saved.

To run the command file, enter: START PERSONNEL

 Use Notepad or another text editor to look at the file U:\PERSONNEL.sql  1.2 Retrieving and executing a query START filename 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 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