SQL*Plus (pronounced "sequel plus") is an interactive tool for the Oracle RDBMS environment. SQL*Plus can be used simply to process SQL statements one at a time, process SQL statements interactively with end users, utilize PL/SQL for procedural processing of SQL statements, list and print query results, format query results into reports, describe the contents of a given table, and copy data between databases.
SQL*Plus originated from the beginning of the Oracle RDBMS days as a product called User Friendly Interface (UFI). UFI was used primarily to administer the Oracle environment. UFI was later renamed to SQL*Plus with the advent of Oracle Version 5. There have been some improvements to SQL*Plus from the UFI days; however, most of the commands and the ease of formatting results are as easy today as they were with the UFI product.
There have been additions to several of the command capabilities, additional ways of starting SQL*Plus, and a changed role for SQL*Plus through the major releases of the Oracle RDBMS kernel. For example, before Oracle Version 6, using UFI or SQL*Plus was the only way to administrate the Oracle database. With Oracle Version 6 came a new tool called SQL*DBA that took over many of the database responsibilities such as backup and recovery and startup and shutdown. SQL*Plus also exists in the world of client/server and is available with all the major graphical interfaces.
SQL*Plus is the main ad hoc, character-mode interface to the Oracle RDBMS. SQL*Plus can easily be used to produce a variety of types of character mode reports. SQL*Plus can also be used to create dynamic SQL*Plus scripts or even dynamic operating-system specific command language programs. SQL*Plus can be used for some Oracle administration functions, and it can be programmed to be interactive during a specific terminal session. SQL*Plus can process ANSI SQL as well as PL/SQL blocks.
There are five types of SQL*Plus commands:
- Those that initiate the SQL*Plus environment
- SQL*Plus Execute commands
- SQL*Plus Editing commands
- SQL*Plus Formatting commands
- Miscellaneous commands
The execute commands are used to initiate the processing of SQL statements and PL/SQL blocks, measure the processing time of SQL or PL/SQL statements, execute non-Oracle programs, execute SQL*Forms programs, or attain additional help.
- / - Execute the SQL statement or PL/SQL block currently in the SQL buffer. (This is probably the most used of the SQL*Plus commands.)
- HELP - Provides online assistance with SQL, PL/SQL, or SQL*Plus commands.
- HOST - Execute non-Oracle commands (operating system-dependent) without leaving SQL*Plus.
- RUN - Displays and executes the contents of the SQL buffer.
- RUNFORM - Execute a SQL*Forms program without leaving SQL*Plus.
- TIMING - Displays the system CPU time with the SQL prompt.
The SQL buffer is a work area assigned to the SQL*Plus environment. This buffer contains only SQL or PL/SQL syntax. You can use the following commands to load, save, and manipulate the contents of this buffer:
A new text or APPEND new text - Appends text to the end of the current line of the SQL buffer.
C/target text/new text/ or CHANGE/target text/new text/ - Changes the target text to the new text on the current line in the SQL buffer.
DEL - Deletes the current line in the SQL buffer.
EDIT filename - Utilizes an operating system-dependent text editor. To edit the SQL buffer with an operating system-dependent text editor, simply leave off the filename.
GET filename - Reads an operating system-dependent file into the SQL buffer.
I text or INPUT text - Adds the text after the current line in the SQL buffer.
L number or LIST number - Displays the contents of the SQL buffer. When the number syntax is used, LIST will display the line number and make that line the current line in the SQL buffer. SAVE filename Saves the contents of the SQL buffer to an operating system-dependent file.
START filename param1 param2 ... - START will execute the contents of the SQL*Plus command file named in filename and pass any input parameters to the SQL*Plus command file.
The SQL*Plus formatting commands are used to manipulate the result set from a SQL query.
BREAK ON column_name and options
This command controls the organization of rows returned by the query. BREAK can manipulate the appearance of the output by specifying under what conditions a BREAK should occur and what actions should be taken at the BREAK. The appearance of the output can be controlled by skipping a line or skipping to top of next page and providing totals when used in conjunction with COMPUTE. Any number of lines can be skipped at a BREAK point. BREAK points can be defined at the column level, for multiple columns, on a row, on a page, or on a report. Entering BREAK by itself at the SQL prompt will display the current BREAK settings.
BTITLE print_options and/or text or variable options
BTITLE is used to place text at the bottom of each page. There are various print options that position text at various locations. BTITLE will simply center the text if no print options are specified. print options include BOLD, CENTER, COL, FORMAT, LEFT, RIGHT, SKIP, and TAB. BTITLE spelled out by itself will display the current text setting. Other options that can be specified are ON and OFF. BTITLE is ON by default.
CLEAR and options
CLEAR resets any of the SQL*Plus formatting commands. You can also use it to clear the screen. The options include BREAKS, BUFFER, COLUMNS, COMPUTES, SCREEN, SQL, and TIMING.
COLUMN column_name and options
COLUMN is used to alter the default display attributes for a given column (column_name) of a SQL query. There are a variety of options, but the more common ones are FORMAT, HEADING, JUSTIFY, NEWLINE, NEW_VALUE, and NOPRINT. The FORMAT option is useful in applying editing to numeric fields, date masks to date fields, and specific lengths to variable-length character fields. The HEADING option overrides the SQL*Plus default heading for the particular column. The JUSTIFY option overrides the SQL*Plus column alignment to the heading default. The NEWLINE option will print the column on the beginning of the next line. NEW_VALUE assigns the contents of the column to a SQL*Plus variable . This value can then be used in conjunction with TTITLE or to store intermediate results for master/detail type reports, and is useful to store and pass information between two or more separate SQL statements.
COMPUTE function OF options ON break options
COMPUTE calculates and prints totals for groupings of rows defined by the BREAK command. A variety of standard functions can be utilized. The most common option is the name of the column in the query on which the total is to be calculated. The break option determines where the totals are to be printed and reset, as defined by the BREAK command.
This section presents a variety of commands that enable you to interact with the user, comment on the code, and enhance coding options.
ACCEPT variable number or char PROMPT text
ACCEPT receives input from the terminal and places the contents in variable. This variable can already have been defined with the DEFINE command. If the PROMPT option is specified, then the text will be displayed after skipping a line. The variable attributes of number or char can be defined at this time. The variable will be a char if not otherwise defined.
DEFINE creates a user-defined variable and assigns it to be of char (character) format. This variable can be assigned a default value at this time.
DESC or DESCRIBE database object
DESCRIBE displays the columns associated with a table, view, or synonym.
PAUSE prints the contents of text after skipping a line, and then waits for the Return or Enter key to be pressed.
PROMPT simply skips a line and prints the contents of text.
REM or REMARK
SQL*Plus will ignore the contents of this line when it is used in SQL*Plus command files. REMARK enables documentation or other comments to be contained in these SQL*Plus command files.
SET SQL*Plus System Variable
The SET command controls the default settings for the SQL*Plus environment. You can automatically alter these settings for each SQL*Plus session by including them in the LOGIN.SQL file. The following are some common SET options utilized for reporting:
- SET LINESIZE 80 Controls the width of the output report line
- SET PAGESIZE 55 Controls the number of lines per page
- SET FEEDBACK OFF - Suppresses the number of query rows returned
- SET VERIFY OFF - Suppresses the substitution text when using &variables, including command line variables
- SET TERMOUT OFF - Suppresses all terminal output; this is particularly useful in conjunction with the SPOOL command
- SET ECHO OFF - Suppresses the display of SQL*Plus commands
The SPOOL command is used to open, close, or print an operating system-dependent file. Specifying SPOOL filename will create an operating system-dependent file; filename can contain the full pathname of the file and extension. If no file extension is given, the file suffix, LST, will be appended (filename.LST). Options include OFF or OUT. If OFF is specified, then the operating system-dependent file is simply closed. If OUT is specified, then the operating system-dependent file is closed and sent to the operating system-dependent printer assigned as the default printer to the user's operating system environment.
UNDEFINE removes the previously Defined variable from the SQL*Plus environment.
rameshd9 likes this