2.1.5.2 Saving output to a file

Frequently, you will want to save output from aSQL*Plus session to a file—perhaps because you are generating a report, or because you want a record of your actions, or because you are dynamically generating commands to execute later. At any rate, an easy way to do this in SQL*Plus is to use its SPOOL command:

SQL> SPOOL report01.txt

SQL> @run_report

...output scrolls past and gets written to the file report01.txt...

SQL> SPOOL OFF

The first command, SPOOL report01.txt, tells SQL*Plus to save everything from that point forward into the file report01.txt. The last command, SPOOL OFF, tells SQL*Plus to stop saving the output and to close the file.

The SPOOL command works fine in the GUI version of SQL*Plus, but it does not work explicitly when using iSQL*Plus. Instead, save output to a file by setting the iSQL*Plus "Output:" drop-down menu to "File."

2.1.5.3 Exiting sql*Plus

To exit SQL*Plus and return to the operating system, use the EXIT command:

SQL> EXIT

If you happen to be spooling a file when you exit, SQL*Plus will stop spooling and close it.

What happens if you exit with any pending (uncommitted) changes to the database? These changes are normally caused by running SQL or PL/SQL, which manipulates data in the database but is not followed by an explicit COMMIT or ROLLBACK. So what happens to the pending transactions depends on the AUTOCOMMIT setting. The default, AUTOCOMMIT ON, will attempt to commit any uncommitted changes. If you have changed the default by using:

SQL> SET AUTOCOMMIT OFF

then Oracle will "roll back" any uncommitted changes.

To disconnect from the database but remain connected to SQL*Plus, use the command DISCONNECT, which will look something like this in action:

SQL> DISCONNECT

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL>

Why might you want to use DISCONNECT? If you're writing a script that changes connections in midstream, it's safer to disconnect before reconnecting. Otherwise, if you happen to be using operating system authentication, the script might reconnect itself automatically . . . maybe to the wrong account.

2.1.5.4 Editing a statement

SQL*Plus keeps the most recently issued statement in a buffer, and you can edit this statement using either the built-in line editor (which is sort of a pain unless you're an old gray-haired programmer like me) or an external editor of your choosing. To start with, I'll show how to set and use an external editor.

Use the EDIT command to have SQL*Plus save the buffer to a file, temporarily pause SQL*Plus, and invoke the editor:

SQL> EDIT

By default, the file will be saved with the name afiedt.buf, but you can change that with SET EDITFILE. Or, if you want to edit an existing file, just supply its name as an argument to EDIT:

SQL> EDIT errpkg.pkg

Once you've saved the file and exited the editor, the SQL*Plus session will read the contents of the newly edited file into its buffer, and then resume.

The default editors that Oracle assumes are:

For Unix, Linux, and relatives

ed

For Microsoft Windows variants

Notepad

Although these default values are actually hardcoded into the sqlplus executable, you can easily change them by assigning your own value to the SQL*Plus _EDITOR variable. Here's an example:

SQL> DEFINE _EDITOR = /bin/vi

where /bin/vi is the full path to an editor that's popular among a handful of strange people (yes, me). I recommend using the editor's full pathname here, for security reasons.

If you really want to use the line editor (and it can be really handy), the essential commands you need to know are:

L

List the most recent statement.

n

Make the nth line of the statement the current line.

DEL

Delete the current line.

C /old/new/

In the current line, change the first occurrence of old to new. The delimiter (here a forward slash) can be any arbitrary character.

n text

Make text the current text of line n.

I

Insert a line below the current line. To insert a new line prior to line 1, use a line zero command (e.g., 0 text).

By the way, EDIT works just fine in the GUI version of SQL*Plus, or you can copy and paste as well. If you are using iSQL*Plus, EDIT won't work, but you can either copy and paste from its editing window or use the "Save Script" and "Load Script" buttons.