|
Page 2 of 4
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 nonSQL 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
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.
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.
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:
|
ECHO {ON|OFF}
|
Controls whether each command is displayed as it is executed.
|
|
HEADING {ON|OFF}
|
OFF will suppress column headings from being displayed.
|
|
LINESIZE {80|n}
|
Determines the length of a line before it is wrapped or truncated (see WRAP).
|
|
PAGESIZE {24|n}
|
Page length before a page break is issued and columns are re-displayed.
|
|
PAUSE {ON|OFF|text}
|
ON will pause the output at the end of each page. [Enter] must be pressed to continue output.
|
|
TAB {ON|OFF}
|
OFF uses spaces to format white space between columns. ON uses a tab between columns.
|
|
UNDERLINE {-|c|ON|OFF}
|
Sets the underline character for column headings to c. ON or OFF turns the underline feature on or off.
|
|
VERIFY {ON|OFF}
|
Controls whether to display the value of a command before and after the entry of a substitution variable.
|
|
WRAP {ON|OFF}
|
ON will wrap a row if it is too long for LINESIZE. OFF will truncate the output when the row length reaches LINESIZE.
|
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
|
