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.

Creating a query via NotePad

For example, to create a new command file that produces a report on salaries of employees in department 30 and includes some formatting commands, first create a new file called personnel (remember, you do not need to include the .sql file extension):

ed personnel

Then type in the following into this file: 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.  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.  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