SQL*Plus

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:

• enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks
  
  
• format, perform calculations on, store, and print query results in the form of reports
  
  
• list column definitions for any table
  
  
• access and copy data between SQL databases (not covered)
  
  
• send messages to and accept responses from an end user (not covered)

Starting SQL*Plus

This example shows you how to start SQL*Plus. Follow the steps shown.

1. First login to your system's user ID.
   
   
2. Enter the command ``sqlplus /''.
   Note: This example assumes that you have been given the same ORACLE user ID as your login. If you forget to add the ``/'' you will be asked to enter your name and password for your ORACLE user ID, in which case you should press $\wedge C$ (cntrl-C) and start over. Entering your system user ID and password will not get you access to SQL*Plus. If your ORACLE user ID differs from your system ID refer to the ``SQL*Plus User's Guide and Reference''.

   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:

             SQL>
   

   The command prompt indicates that SQL*Plus is ready to accept your commands.

Leaving SQL*Plus

To leave SQL*Plus, enter the ``EXIT'' or ``QUIT'' commands at the SQL*Plus command prompt (case insensitive):

              SQL> QUIT

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.

Entering and Executing Commands

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:

• SQL commands, for working with information in the database
  
  
• PL/SQL blocks, also working with information in the database (not covered)
  
  
• SQL*Plus commands, for formatting query results, setting options and editing and storing SQL commands and PL/SQL blocks.
  
  

SQL Commands

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.

1. The example below shows you how to enter a SQL command:

             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. SQL*Plus will display a ``2'', the prompt for the second 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.

             SQL>
   

   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:

1. A semicolon(;) tells SQL*plus that you want to run the command. SQL*Plus will process the command and store it in the SQL buffer. Note: You cannot enter a comment (/**/) on the same line that you enter a semicolon.
   
   
2. A slash (/) on a line by itself also tells SQL*Plus that you wish to run the command. SQL*Plus will process the command and store it in the SQL buffer.
   
   
3. A blank line tells SQL*Plus that you have finished entering the command, but do not want to run it yet. Press [RETURN] at the end of the last line of the command. SQL*Plus prompts you with another line number. Press [RETURN] again; SQL*Plus now prompts you with the SQL*Plus command prompt. SQL*Plus does not execute the command, but stores it in the SQL buffer. If you subsequently enter another SQL command and execute it using a semicolon (;) or a slash (/), SQL*Plus overwrites the unexecuted command in the buffer.

SQL*Plus Commands

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''.

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:

          SQL> EDIT

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:

1. Using the REMARK command on a line by itself in the command file, followed by comments on the same line. To continue the comments on additional lines, enter additional REMARK commands. Do not place a REMARK command between different lines of a single SQL command.
   
   
2. Using the SQL comment delimiters, /*...*/, on separate lines in your command file, on the same line as a SQL command, or on a line in a PL/SQL block. The comments can span multiple lines.
   
   
3. Using the ANSI/ISO ``-\space-'' style comments within SQL statements, PL/SQL blocks, or SQL*Plus commands. Since there is no ending delimiter, the comment cannot span multiple lines. For PL/SQL and SQL, enter the comment after a command on a line or on a line by itself. For SQL*Plus commands, you can only include ``-\space-'' style comments if they reside alone on a line.

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.

          SQL> @file_name

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.

INTERACTIVE SQL*Plus COMMANDS

The following features of SQL*Plus make it possible for you to set up command files that allow end-user input:

• defining user variables
• substituting values in commands
• using the START command to provide values
• prompting for values

1. 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.

2. 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';
   

3. 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.

4. 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.

FORMATTING QUERY RESULTS

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.

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.

DEFINING PAGE TITLES AND DIMENSIONS

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: