This section will provide a basic understanding of SQL*Plus. Most of the information contained in this section is DIRECTLY extracted from ``SQL*PLUS User's Guide and Reference'' and all credit should be given to ORACLE. If you require more detailed information than provided in this section, consult the ``SQL*Plus User's Guide and Reference''.
SQL*Plus enables you to manipulate SQL commands and PL/SQL blocks, and to perform many additional tasks as well. Through SQL*Plus, you can:
This example shows you how to start SQL*Plus. Follow the steps shown.
SQL*Plus displays its version number, the date, and copyright information, and automatically checks your authentication. Next, SQL*Plus displays the SQL*Plus command prompt:
The command prompt indicates that SQL*Plus is ready to accept your commands.
To leave SQL*Plus, enter the ``EXIT'' or ``QUIT'' commands at the SQL*Plus command prompt (case insensitive):
SQL*Plus displays the version of ORACLE you disconnected from and the versions of tools available through SQL*Plus. After a moment you see the operating system prompt.
To tell SQL*Plus what to do, simply type the command you wish to enter. Usually, you separate the words in a command from each other by a space or tab. You can use additional spaces or tabs between words, if you wish, to make your commands more readable.
You can enter three kinds of commands at the command prompt:
The SQL command language enables you to manipulate data in the database. The syntax of the SQL query commands will not be discussed in this manual, as these are covered in your lecture. In this section we will discuss the logistics of SQL*Plus as it relates to SQL, and in Chapter 6 we will discuss some of the SQL commands associated with manipulating the data dictionary.
SQL> SELECT EMPNO, ENAME, JOB, SAL
If you make a mistake, use [BACKSPACE] to erase it and re-enter. When you are done, press [RETURN] to move to the next line.
2 FROM EMP WHERE SAL < 2500;
The semicolon (;) means that this is the end of the command. Press [RETURN] and SQL*Plus will process the command and display the results on the screen. After displaying the results and the number of rows retrieved, SQL*PLus displays the command prompt again.
If you made a mistake and therefore did not get the results you expected, simply re-enter the command.
You can divide your SQL command into separate lines at any points you wish, as long as individual words are not split between lines.
You can end a SQL command in one of three ways:
You can use SQL*Plus commands to manipulate SQL commands and PL/SQL blocks, and to format and print query results.
To speed up command entry, you can abbreviate many SQL*Plus commands to one or a few letters. For abbreviations of all SQL*Plus commands, refer to the command descriptions in Chapter 6 of the ``SQL*Plus User's Guide and Reference''.
You can continue a long SQL*Plus command by typing a hyphen (-) at the end of the line and pressing [RETURN]. If you wish, you can type a space before typing the hyphen. SQL*Plus displays a right angle-bracket ($>$) as a prompt for each additional line.
You do not need to end a SQL*Plus command with a semicolon. When you finish entering the command, you can just press [RETURN]. However, if you wish, you can enter a semicolon at the end of a SQL*Plus command.
You can use a number of SQL*Plus commands to edit the SQL command or PL/SQL block currently stored in the buffer. Or, you can use a host operating system editor to edit the buffer contents. SQL*Plus commands are not stored in the buffer; you must edit them at the prompt or re-enter the command.
SQL*Plus provides some line editing commands to help you modify the buffer. These line editing commands will only be listed below and will not be explained; for an explanation of these commands, see Chapter 3 of ``SQL*Plus User's Guide and Reference''.
|APPEND||A text||add text at the end of a line|
|CHANGE /old/new||C /old/new||change old to new in the line|
|CHANGE /text||C text||delete text from a line|
|CLEAR BUFFER||CL BUFF||delete all lines|
|DEL||(none)||delete a line|
|INPUT||I||add one or more lines|
|INPUT text||I text||add a line consisting of text|
|LIST||L||list all lines in the SQL buffer|
|LIST n||L n OR n||list one line|
|LIST *||L *||list the current line|
|LIST LAST||L LAST||list the last line|
|LIST m n||L m n||list a range of lines (m to n)|
Once you have modified the buffer you can use the ``RUN'' command to execute the buffer contents. To save the contents of the buffer to a text file, use the ``SAVE file\_name'' command.
You can use your host operating system's default text editor to create and edit host system files and to modify the SQL*Plus buffer. To invoke the default text editor without leaving SQL*Plus enter:
EDIT loads the contents of the buffer into your system's default text editor. When you exit from your text editor, the text is saved back into the current buffer.
To load the buffer contents into a text editor other than the default (EDITOR environment variable), use the SQL*Plus DEFINE command to define a variable, \_EDITOR, to hold the name of the editor.
SQL> DEFINE _EDITOR = /usr/gnu/bin/emacs
You can create a command file with a host operating system text editor by entering EDIT followed by the name of the file:
SQL> EDIT file_name
EDIT adds the file name extension ``.SQL'' to the name unless you type a period and a different extension at the end of the file name.
When you create a command file using EDIT, you can include SQL*Plus commands at the end of the file. You cannot do this when you create a command file using the INPUT and SAVE command because SAVE appends a slash to the end of the file. This slash would cause SQL*Plus to run the command file twice, once upon reaching the semicolon at the end of the last SQL command , and once upon reaching the slash at the end of the file.
You can enter comments in a command file in one of three ways:
If you want to place the contents of a command file in the buffer, you must retrieve the commands from the file in which they are stored. You can retrieve a command file using the SQL*Plus:
SQL> GET file_name
When appropriate to the operating system, SQL*Plus adds a period and the extension SQL to the file name unless you type a period at the end of the file name followed by a different extension. Once the contents are in the buffer, you can edit the command further and if the file does not contain SQL*Plus commands, you could also execute it with the RUN command.
The START command retrieves a command file and runs the command(s) it contains. Use START to run a command file containing SQL commands, PL/SQL blocks, and/or SQL*Plus commands.
SQL> START file_name
You can also use the @(``at'' sign) command to run a command file. The @ command lists and runs the commands in the specified command file in the same manner as the START command.
You can run a command file as you start SQL*Plus from the system prompt:
% sqlplus / @file_name
If your command file generates a SQL error while running from a batch file on the host operating system, you may want to abort the command file and exit with a return code. Use the SQL*Plus command WHENEVER SQLERROR to do this.
You may wish to set up your SQL*Plus environment in a particular way (such as showing the current time as part of the SQL*Plus prompt) and then re-use those settings with each session. You do this by setting up a file called ``login.sql'' which will be read from the current directory when SQL*Plus is started. You can add any SQL commands, PL/SQL blocks, or SQL*Plus commands to this file.
The following features of SQL*Plus make it possible for you to set up command files that allow end-user input:
You can define variables, called user variables, for repeated use in a single command file by using the SQL*Plus command DEFINE. Note that you can also define user variables to use in titles and to save you keystrokes (by defining a long string as the value for a variable with a short name).
SQL> DEFINE EMPLOYEE = SMITH
To list all user variable definitions, enter DEFINE by itself at the command prompt.
A substitution variable is a user variable name preceded by one or two ampersands (\&). When SQL*Plus encounters a substitution variable in a command, SQL*Plus executes the command as though it contained the value of the substitution variable, rather than the variable itself.
You can use the substitution variable anywhere in SQL and SQL*Plus commands, except as the first word entered at the command prompt. When SQL*Plus encounters an undefined substitution variable in a command, SQL*Plus prompts you for the value.
SQL*Plus reads your response from the keyboard, even if you have redirected terminal input or output to a file. If a terminal is not available (if, for example, you run the command file in a batch mode), SQL*Plus uses the redirected file.
If the SQL command containing the reference has quote marks around the variable and you do not include them, then the user must include the quotes when prompted. SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. You can suppress this listing by setting the SET command variable VERIFY to OFF.
SQL*Plus automatically DEFINEs any substitution variable preceded by two ampersands (if already defined), but does not DEFINE those preceded by only one ampersand. If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example,
SQL> SELECT * FROM EMP WHERE EMPNO='&X.01'; Enter value for X: 123
will be interpreted as:
SQL> SELECT * FROM EMP WHERE EMPNO='12301';
You can bypass the prompts for values associated with substitution variables by passing values to parameters in a command file through the START command. You do this by placing an ampersand (&) followed by a numeral in the command file in place of a substitution variable. Each time you run this command file, START replaces each &1 in the file with the first value (called an argument) after START file_name, then replaces each &2 with the second value, and so forth. Note: You cannot use parameters when you run a command with RUN or slash (/). You must store the command in a command file and run it with START.
Three SQL*Plus commands - PROMPT, ACCEPT and PAUSE - help you communicate with the end user. PROMPT simply displays a message you specify on-screen; use it to give directions or information to the user. ACCEPT prompts the user for a value and stores it in the user variable you specify.
SQL> INPUT 1. PROMPT Enter a valid employee number 2. PROMPT For example: 7123, 7456, 7890 3. ACCEPT ENUMBER NUMBER PROMPT 'EMP. no.: ' 4. SELECT ENAME, MBR, JOB, SAL 5. FROM EMP 6. WHERE EMPNO = &ENUMBER 7. SQL> SAVE PROMPT2 Created file PROMPT2 SQL> START PROMPT2 ...
If you want to display a message on the user's screen and then have the user enter [RETURN] after reading the message, use the SQL*Plus command PAUSE. For example, you might want to include the following lines in a command file:
PROMPT Before continuing, make sure you have your account card. PAUSE Press RETURN to continue.
If you want to clear the screen before displaying a report (or at any other time), include the SQL*Plus CLEAR command with its SCREEN clause at the appropriate point in your command file.
Through the SQL*Plus COLUMN command you can change the column headings and reformat the column data in your query results. SQL*Plus uses the default column headings when displaying query results. You can define a more useful column heading with the HEADING clause of the COLUMN command.
SQL> COLUMN ENAME HEADING Employee SQL> COLUMN ENAME HEADING 'Employee|Name' SQL> SET UNDERLINE = SQL> SET UNDERLINE '-'
The new headings will remain in effect until you enter different headings, reset each column's format, or exit from SQL*Plus. To change a heading to two or more words, enclose the new heading in single or double quotation marks when you enter the COLUMN command. To display a column heading on more than one line, use a vertical bar (|) where you want to begin a new line. You can change the character used to underline each column heading by using the SET command with the UNDERLINE clause.
The COLUMN command is also used to change the default format of NUMBERS and TEXT. The COLUMN command identifies the column you want to format and the model you want to use. Only a few examples on formatting will be shown; for a complete list of the models, see page 6-23 of ``SQL*Plus User's Guide and Reference'' manuals. The COLUMN command can be affected by the settings of NUMWIDTH, LONGCHUNKSIZE, WRAP, WRAPPED, WRAPPED_WORD, and TRUNCATED variables.
SQL> COLUMN column_name HEADING model SQL> COLUMN SAL FORMAT $99,990 SQL> COLUMN EMANE FORMAT A4
If you want to give a column the same display attributes you gave to another column, you should use the LIKE clause.
SQL> COLUMN COMM LIKE SAL HEADING Bonus
To list the current display attributes for a specific column or all columns, use the COLUMN clause by itself (or the name of the column). To reset the display attributes for all columns (or a specific column), use the CLEAR command (or CLEAR clause of the COLUMN command). Note that you may wish to place the command CLEAR COLUMNS at the beginning of every command file to ensure that previously entered COLUMN commands will not affect queries you run in a given file.
SQL> COLUMN SQL> COLUMN column_name SQL> CLEAR COLUMNS SQL> COLUMN column_name CLEAR
You can suppress and restore the display attributes you have given to a specific column through the COLUMN command by using the ON and OFF clause. The OFF clause tells SQL*Plus to use the default display attributes for the column, but does not remove the attributes you have defined through the COLUMN command.
SQL> COLUMN column_name OFF SQL> COLUMN column_name ON
SQL*Plus wraps column values to additional lines when the value does not fit within the column width. If you want to insert a record separator after each WRAPPED line of output or EACH line then use the RECSEP and RECSEPCHAR variables of the SET command.
SQL> SET RECSEP WRAPPED SQL> SET RECSEP EACH SQL> SET RECSEPCHAR '-'
You can have the whole word wrapped to additional lines when a column value wraps to additional lines.
SQL> COLUMN column_name WORD_WRAPPED
SQL*Plus BREAK and COMPUTE commands create subset of records and add space and/or summary lines after each subset. The column you specify in a BREAK command is called a break column which suppresses duplicate values. By including the break column in your ORDER BY clause, you create meaningful subsets of records in your output. You can then add formatting to the subsets within the same BREAK command and add a summary (containing total, averages, and so on) by specifying the break column in a COMPUTE command.
SQL> BREAK ON DEPTNO SQL> SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE SAL < 2500 4 ORDER BY DEPTNO;
You can insert blank lines or begin a new page each time the value changes in the break column.
SQL> BREAK ON DEPTNO SKIP 1 SQL> BREAK ON DEPTNO SKIP PAGE
You may wish to insert blank lines or a blank page after every row. Note that SKIP PAGE only skips the number of lines defined to be a page. Thus it may not cause a physical page break.
SQL> BREAK ON ROW SKIP 1 SQL> BREAK ON ROW SKIP PAGE
If you want to use different spacing techniques in one report or insert space after the value changes in more than one ordered column, you must specify multiple columns and actions in a single BREAK command. Each BREAK command you enter replaces the previous one.
SQL> BREAK ON DEPTNO SKIP PAGE ON JOB SKIP 1
To list the current break definition, enter the BREAK command with no clause. To remove the current break definition, enter the CLEAR command with the BREAKS clause. Note that you may wish to place the command CLEAR BREAKS at the beginning of every command file to ensure that previously entered BREAK commands will not affect queries you run in a given file.
If you organize the rows of a report into subsets with the BREAK command, you can perform various computations on the rows in each subset. You do this with the functions of the SQL*Plus COMPUTE command. Use the BREAK and COMPUTE commands together in the following forms:
BREAK ON break_column1 SKIP PAGE ON break_column2 SKIP 1 COMPUTE function OF column column column ... ON break_column_2
The COMPUTE command has no effect without a corresponding BREAK command. You can COMPUTE on NUMBER columns, and in certain cases, on all types of columns. See COMPUTE in Chapter 6 of ``SQL*Plus User's Guide and Reference'' for details.
|SUM||Computes the sum of the values in the column|
|MIN||Computes the minimum value in the column|
|MAX||Computes the maximum value in the column|
|AVG||Computes the average of the values in the column|
|STD||Computes the standard deviation of the values in the column|
|VAR||Computes the variance of the values in the column|
|COUNT||Computes the number of non-null values in the column|
|NUM||Computes the number of rows in the column|
The function you specify in the COMPUTE command applies to all columns you enter after OFF and before ON. The computed values print on a separate line when the value of the ordered column changes. Labels for the computed values appear in the first column. If you use COMPUTE on the first column, you should create a dummy column for the label using the COLUMN command. Otherwise, the label will not print.
Note: The format of the column controls the appearance of the COMPUTE function column, as well as the individual values of the column. When you establish the format of a NUMBER column, you must allow for the size of sums you will include in your report.
You can calculate and print summary lines based on all values in a column by using BREAK and COMPUTE in the following forms:
BREAK ON REPORT COMPUTE function OF column column column ... ON REPORT
To print a grand total (or grand average, grand maximum, and so on) in addition to subtotals (or sub-averages, and so on), include a break column and an ON REPORT clause in your BREAK command. Then, enter one COMPUTE command for the break column and another to compute ON REPORT:
BREAK ON break_column ON REPORT COMPUTE function OF column ON break_column COMPUTE function OF column ON REPORT
You can print multiple summary lines on the same break column. To do so include the function for each summary line in the COMPUTE command as follows:
COMPUTE function function ... OF column column ... ON break_column
To list the current COMPUTE definition, enter the COMPUTE command with no clause. To remove the current COMPUTE definition, enter the CLEAR command with the COMPUTES clause. Note that you may wish to place the command CLEAR COMPUTES at the beginning of every command file to ensure that previously entered COMPUTE commands will not affect queries you run in a given file.
The word page refers to a screenful of information on your display, or a page of spooled (printed) report. You can place top and bottom titles on each page, set the number of lines per page, and determine the width of each line.
The TTITLE command defines the top title; the BTITLE command defines the bottom title. A TTITLE or BTITLE command consists of the command name TTITLE or BTITLE followed by one or more clauses specifying a position or format and a CHAR value you wish to place in that position or give that format. You can include multiple sets of clauses and CHAR values:
TTITLE pos_clause(s) char_value pos_clause(s) char_value ... or BTITLE pos_clause(s) char_value pos_clause(s) char_value ...
For descriptions of all TTITLE and BTITLE clauses, see the discussion of TTITLE in chapter 6 of ``SQL*Plus User's Guide and Reference''. The most often used clauses of TTITLE and BTITLE are summarized below:
|COL n||Makes the next CHAR value appear in the specified column of the line.|
|SKIP n||Skips to a new line n times.|
|LEFT||Left-aligns the following CHAR value.|
|CENTER||Centers the following CHAR value.|
|RIGHT||Right-aligns the following CHAR value.|