SQLPLUS saving to file

Question

I have to use SQLPLUS for my database class, and our first assignment is simple saving.

I followed the instructions.. (I'm using PuTTY to access sqlplus)

"Use the following SQL commands in this exercise and try the SAVE and SPOOL commands to save your SQL commands and output to external files.

select table_name from all_tables where owner='UNIVERSITY';
select * from university.faculty;
select * from university.clubs;

For this lab, do the following:

• At the SQL> prompt, type Save test.sql (or save test.sql replace if the file already exists) then hit enter; then type any SQL commands, the commands will be saved to the test.sql file. Later you can use the START command to run the saved SQL commands. E.g.: SQL> start test.sql

• At the SQL> prompt, type spool output.txt then enter; then type any SQL commands; when finished type 'spool off'; the commands and results will be saved to file output.txt. The file will be overwritten if used in the spool command again. Turn in file test.sql and output.txt in the dropbox on D2L by Monday before class."

(Obviously asking for help isn't against the rules, since the instructions are right there already.. i simply don't understand them or they're wrong)

When I type SAVE test.sql i yield => "Nothing to save"

When I type SAVE test.sql after a query, it saves only the last typed query.

How do I have it save ALL my queries instead of just the last one typed?

Krill

I'm struggling to see the reason why you would need to do/learn this... Why couldn't you just create a text file and type in all of your commands? What would the reason behind saving them from a command prompt? Perhaps the full directions speak to the reason and explain that this is just needed in certain circumstances. That may help figure out the proper order that the syntax needs to be typed in. Just a thought. – Wexler

Obviously I'm learning this because it's my homework assignment. And I've tried different orders. – Krill

Found out how.. dunno if it's the 'right' way but the results are the same. Use "SAVE test.sql APPEND" AFTER the sql line – Krill

The lab instructions are incorrect about SAVE. Welcome to the world of high education, where you're forced to learn things the hard way, that you'll never need to know in real life anyway :) (mind you, knowing how to SPOOL will come in useful) – Interrupter

@JeffreyKemp it's been 4 years and knowing how to SPOOL has never come in handy :( – Krill

Answer 1

How do I have it save ALL my queries instead of just the last one typed?

SAVE saves the content of the SQL*Plus buffer into the file. The buffer gets replaced with every SQL statement that you write, hence you get only the last command. Save has an append command that will append to the file.

So, first create your file.

save test.sql create

and append the file after every SQL script.

select * from employees
/
save test.sql append;
select * from departments
/
save test.sql append;

and so on

Bolter

Answer 2

Use spool option :

1. go to file-> spool file->give a file name(* Be careful in giving the file name don't use any space in the file name as well as the folder name)
2. now type command set lines 100 pages 100;
3. type command cl scr;
4. start typing your queries.
5. either go to file -> spool-> spool off or
6. type the command spool off;

Ostwald