Tutorials
Oracle 9i
Tutorial 2: SQL *Plus Commands
Using SQL *Plus to Work with Files
SQL Statement Examples
Using SQL *Plus for Storing Temporary ValuesAt 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).
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.
You can use Notepad to edit the contents of the buffer. There are two ways to enter Notepad.
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.
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} |
|
|
LINESIZE {80|n} |
Determines the length of a line before it is wrapped or truncated (see |
|
PAGESIZE {24|n} |
Page length before a page break is issued and columns are re-displayed. |
|
PAUSE {ON|OFF|text} |
|
|
TAB {ON|OFF} |
|
|
UNDERLINE {-|c|ON|OFF} |
Sets the underline character for column headings to |
|
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 |
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.
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

|
hi i view the solution and output files it says "Forbidden You don't have permission to access /developers/articles/sqlplus_output1.html on this server." |