Using SQL *Plus to Work with Files - Save; Get and Start; Spool

At the SQL prompt, you can begin typing any SQL command. Upon hitting return (i.e., enter key) the SQL prompt will change to line number prompts. When you are finished typing a command, type / or RUN to execute the SQL command. Also, a semicolon at the end of the SQL command will execute the command immediately after hitting return. In addition to SQL commands, /, and RUN, other commands can be issued at the SQL prompt (a semicolon does not have to follow the non-SQL commands).

• DESCRIBE tablename--lists the attributes and their specifications of tablename
• EDIT--Places you in an editor (Notepad). The buffer contents are put into the editor
• GET filename--Retrieves a file and places it into the buffer
• SAVE filename--Saves the buffer to the name file
• CLEAR BUFFER--Clears the buffer

Line Editing

One way to change an SQL statement in the buffer is by using the line editor. The following are a list of line edit commands.

• LIST or L--Lists the contents of the buffer
• LIST n or L n--Lists the contents of line number n in the buffer and makes the line current
• LIST * or L *--Lists the current line
• LIST m n--Lists the range from m to n line
• Append text or A text--Adds to the end of the current line (e.g., "A ," adds a comma to the end of line
• INPUT or I--Adds one or more lines after the current line so you can begin adding the text.
• CHANGE /text--Deletes text from the current line
• CHANGE /oldtext/newtext--Replaces oldtext with newtext in the current line
• DEL -- Deletes the current line

Using Notepad for Editing

You can use Notepad to edit the contents of the buffer. There are two ways to enter Notepad.

• Use the menus Edit/editor/invoke. In addition here, you can use Edit/editor/define to change the editor. You might check Edit/editor/define prior to editing a buffer to make sure it is set up the way you would like.
• Type Edit at the SQL Prompt

When you are finished editing, click on File/exit (in Notepad). Next, type GET buffername at the SQL prompt. The buffer name will be listed a few lines up in SQL Plus upon returning to SQL Plus. Type RUN, to execute the command. Notepad can also be used to save the buffer contents. Use File/Save As to create a permanent file. Multiple SQL commands can be typed in Notepad. End each SQL command (except the last one) with a semicolon. After exiting notepad, type Start buffername instead of GET buffername to run all of the commands. Start filename can also be used to execute SQL command that are stored in a file.

Sending the Results to a File

To send queries and their results to a file for later printing (e.g., turning in homework assigments), the spool command is used. On the menu go to FILE/Spool to begin spooling--it will ask for a file name. To quit spooling, go to FILE/Spool and then click on end Spool.

Retrieving a File

To retrieve SQL command from a file use GET filename or START filename. GET filename places the file into the buffer. START filename executes the commands in the file.

Using SQL *Plus for Formatting Output - Formatting Column Headings; Clear; Set Commands

Formatting Output

SQL*Plus will automatically provide headers and page breaks for your output, but you can customize it to make a report that looks nice and contains the titles that you need.

You can change the heading names for the columns that you output with the following command:

     COLUMN name HEADING heading

To give a column a multi-line heading, use the | symbol as a line break.

COLUMN commision_pct HEADING Commission|Percentage

To format the data, use the COLUMN command with the FORMAT clause:

COLUMN name FORMAT format_string

Numeric output can be formatted as in this example:

COLUMN salary FORMAT $9,990.99

String lengths can be specified with COLUMN.

COLUMN comments FORMAT A10

The LIKE clause allows the display attributes for one column to be used for another.

COLUMN last_name LIKE comments HEADING Last|Name

The BREAK command will suppress display of duplicate values for a specified column. The SKIP clause allows you to skip n lines or skip a page after each column. To have your report make sense, you should have column be the primary column in the ORDER BY clause of your select.

BREAK ON column SKIP {n | PAGE}

The COMPUTE command will allow you to compute a function or set of functions on a column or set of columns in your output. COMPUTE has no effect without a corresponding BREAK command.

COMPUTE function OF column ON {break column|REPORT}

You can specify a title and footer that will appear on every page. TTITLE sets the top title, and BTITLE sets the bottom title, or footer. Both commands are set the same way:

TTITLE printspec [text|variable]

Following are some of the most common SQL*Plus parameters that you can set:    

Running a script

Once you have a command loaded into the SQL*Plus buffer, you can execute it by typing / at the SQL*Plus prompt. To load and run a program from a file, you can type:

SQL> START myfile
or:
SQL> @myfile

NOTE that myfile is assumed to have the extension .sql.

Defining Variables

You can fix the value of a variable with the DEFINE command. DEFINE assigns a CHAR datatype to the variable.

DEFINE variable_name = text

Next Page: SQL Statement Examples