Advanced SQL*Plus Commands

Advanced SQL*Plus Commands

This section introduces some of the advanced features of SQL*Plus including editing the SQL command buffer, formatting output from SQL SELECT statements, saving the output from SQL statements and collecting performance statistics on the execution of SQL statements.

Editing The SQL Buffer

SQL*Plus has several commands to allow the user to edit or modify SQL statements. Once a new SQL statement has been typed in (ending with a ;) this statement is placed into a buffer and is considered to be the current SQL statement. All of the following commands operate on the current SQL statement in the buffer.

/ - Execute the current SQL statement in the buffer
APPEND - Add text to the end of the current line of the SQL statement in the buffer
CHANGE - Replace text on the current line of the SQL statement with new text
CLEAR - Clear the buffer
DEL - Delete the current line in the buffer
INPUT - Add one or more lines to the SQL statement in the buffer
LIST - List the current SQL statement in the buffer
RUN - Execute the current SQL statement in the buffer
SAVE - Save the current SQL statement to a script file
START - Load a SQL statement located in a script file and then run that SQL statement

SQL statements may be typed with a free format. Spaces and <cr> characters may be used to separate key words in a SQL statement. SQL*Plus displays line numbers in the left hand margin indicating the current line for a SQL statement that spans multiple lines.

In the following example, an erroneous SQL statement has been entered. After the ; was typed, an error message was displayed indicating the approximate location of the error and a brief error message description.

      SQL> SELECT tname, tabtype
      2  FRO
      3  tab;
      FRO
      *
      ERROR at line 2:
      ORA-00923: FROM keyword not found where expected

To correct line number 2, the user can type the line number followed by the correct portion of the SQL statement. This corrects the SQL statement in the buffer. The last step is to execute the SQL statement in the buffer by typing the RUN command.

      SQL> 2 FROM
      SQL> RUN

      TNAME                          TABTYPE
      ------------------------------ -------
      MACHINE                        TABLE
      EMPLOYEE                       TABLE

The LIST command can be used to display the current contents of the SQL buffer. An asterisk (*) is used to mark the current line of the SQL statement within the buffer.

      SQL> LIST
      1  SELECT tname, tabtype
      2  FROM
      3* tab

The current line of the SQL statement in the buffer can be appended using the APPEND command. The syntax is: APPEND new text. In the following example the new text ``xyz'' is appended to line number 3 which is the current line in the buffer.

      SQL> LIST
      1  SELECT tname, tabtype
      2  FROM
      3* tab

      SQL> APPEND xyz
      3* tabxyz

      SQL> LIST
      1  SELECT tname, tabtype
      2  FROM
      3* tabxyz

Text on a line in the SQL statement can also be replaced using the CHANGE command. The syntax for the CHANGE command is: CHANGE / old text / new text /

In the following example, text on the current line number 3 will be replaced with blank text:

      SQL> LIST
      1  SELECT tname, tabtype
      2  FROM
      3* tabxyz

      SQL> CHANGE/xyz//
      3* tab

      SQL> LIST
      1  SELECT tname, tabtype
      2  FROM
      3* tab

To move to a different line of the SQL statement in the buffer, simply type the line number.

      SQL> LIST
      1  SELECT tname, tabtype
      2  FROM
      3* tab

      SQL> 2
      2* FROM

      SQL> LIST
      1  SELECT tname, tabtype
      2* FROM
      3  tab

The DEL command can be used to delete the current line of the SQL statement out of the buffer as in the following example.

      SQL> LIST
      1  SELECT tname, tabtype
      2* FROM
      3  tab

      SQL> DEL
      2

      SQL> LIST
      1  SELECT tname, tabtype
      2* tab

A SQL statement in the buffer can be saved to a file for later use. The SAVE command serves this purpose. The syntax for the SAVE command is: SAVE filename

In this example, the current contents of the buffer are saved to a file called query.sql:

      SQL> LIST
      1  SELECT tname, tabtype
      2* FROM
      3  tab

      SQL> SAVE query.sql

A directory and/or drive letter (for those using MS DOS or MS Windows) can be placed in front of the file name in order to re-direct the file to another drive or directory. For example, to save the current statement to a floppy disk:

      SQL> SAVE a:\query.sql

A SQL statement saved in a file can then be loaded and executed using the START command. The syntax for the START command is: START filename

Here, the file query.sql created in the previous example is loaded and executed using the START command:

      SQL> START query.sql

      TNAME                          TABTYPE
      ------------------------------ -------
      MACHINE                        TABLE
      EMPLOYEE                       TABLE

      SQL>

Again, a drive letter and/or directory name can be placed in front of the file name.

In many cases, it is easiest to create and edit a set of text files containing the queries and then use the START command to execute them. Instructions for this vary depending on the operating system. For example, under a UNIX system, one can use a text editor such as VI, Emacs or Pico to create text files with the create statements to create the tables, insert statements to add data and select statements to perform some queries.

Under MS Windows 95 or NT, one can use the Windows NotePad editor to create these same types of files. If the files are stored on a floppy disk (for example, the a: drive), then the START command can be used as follows:

   SQL> START a:\query.sql

When working with SQL statements and SQL*Plus commands in a script file, be sure and make backups of your disks and files.

Formatting SQL*Plus Output

SQL*Plus contains several commands that can alter the appearance of the output. These commands are only in effect for the current SQL*Plus session. They can also be included in SQL script files and can be executed using the START command.

The formatting commands include:

BREAK - Set the formatting behavior for the output of SQL statements
BTITLE - Place a title on the bottom of each page in the printout from a SQL statement
COLUMN - Change the appearance of an output column from a query
REMARK - Place a comment following the REMARK keyword
SET - Set a SQL*Plus variable to a new value
SHOW - Show the current value of a SQL*Plus variable
TTITLE - Place a title on the top of each page in the printout from a SQL statement
UNDEFINE - Delete a user defined variable

Note that none of these SQL*Plus formatting commands changes the underlying table structures.

Perhaps the most useful command is COLUMN which changes the appearance of data for a given column. The syntax for the COLUMN command is as follows:

      COLUMN column_name option1 option2 ...

Where option can be one or more of the following:

FORMAT format - Changes the format for the column. For example, to only display the first 10 characters of an employee's last name (column LNAME), use the following:

          COLUMN lname FORMAT A10

          COLUMN salary FORMAT $9,999,990.99

HEADING heading_text - changes the heading for a column.
JUSTIFY LEFT or JUSTIFY CENTER or JUSTIFY RIGHT - aligns the output with the left, center or right of the column.
NULL text - Indicates the text that should be displayed in place of a NULL value.
WRAPPED or WORD_WRAPPED or TRUNCATED - Indicates how text that is longer than the displayed column width should be handled. TRUNCATED means it will be cut off at the maximum width of the field. WRAPPED will wrap the value down to the next line of output. WORD_WRAPPED does the same as Wrapped but breaks the value up on white space.

The SET and SHOW commands can also be useful. To see a listing of all of the SQL*Plus variables, type SHOW ALL

      SQL> show all
      appinfo is ON and set to "SQL*Plus"
      arraysize 15
      autocommit OFF
      autoprint OFF
      autotrace OFF
      shiftinout INVISIBLE
      blockterminator "." (hex 2e)
      btitle OFF and is the 1st few characters of the next SELECT
      cmdsep OFF
      colsep " "
      compatibility version NATIVE
      concat "." (hex 2e)
      copycommit 0
      copytypecheck is ON
      define "&" (hex 26)
      echo OFF
      editfile "afiedt.buf"
      embedded OFF
      escape OFF
      feedback ON for 6 or more rows
      flagger OFF
      flush ON
      heading ON
      headsep "|" (hex 7c)
      linesize 100
      lno 24
      loboffset 1
      long 80
      longchunksize 80
      newpage 1
      null ""
      numformat ""
      numwidth 9
      pagesize 24
      pause is OFF
      pno 0
      recsep WRAP
      recsepchar " " (hex 20)
      release 800030000
      repfooter OFF and is NULL
      repheader OFF and is NULL
      serveroutput OFF
      showmode OFF
      spool OFF
      sqlcase MIXED
      sqlcode 0
      sqlcontinue "> "
      sqlnumber ON
      sqlprefix "#" (hex 23)
      sqlprompt "SQL> "
      sqlterminator ";" (hex 3b)
      suffix "SQL"
      tab ON
      termout ON
      time OFF
      timing OFF
      trimout ON
      trimspool OFF
      ttitle OFF and is the 1st few characters of the next SELECT
      underline "-" (hex 2d)
      user is "TUCANO"
      verify ON
      wrap : lines will be wrapped

Some of the SQL*Plus variables of interest include BTITLE and TTITLE described above, Other useful variables include:

SET COLSEP - The separator characters between columns (default is a space)
SET ECHO - Determines if SQL*Plus commands should be echoed or not. The default is OFF meaning SQL*Plus commands will not be echoed.
SET HEADING - Determines if column headings should be displayed or not. Default is ON.
SET LINESIZE - Determines the maximum number of characters in a line of output. The default is 80.
SET LONG - Determines how much data in a LONG column will be displayed. The default is 80 bytes.
SET NULL - Determines what value should be displayed in place of a NULL value.
SET PAGESIZE - The number of lines for one page (before the headers repeat). The default is 24 lines.
SET PAUSE - Determines if output should be paused after PAGESIZE lines have been displayed. The default is OFF.
SET TRIMOUT - Determines if each line should be padded with blanks out to a length of LINESIZE. The default is ON meaning the output will be trimmed (e.g., will not be padded with spaces).
SET WRAP - Determines if the output will be wrapped to the next line or truncated if the line is longer than LINESIZE. The default is ON meaning long output will be wrapped.

The following example shows an SQL*Plus script file (myquery.sql) that utilizes some of the above formatting commands. The output of this script is displayed afterwards.

      TTITLE 'Employees, Departments and Department Managers'
      SET PAGESIZE 36
      COLUMN address      FORMAT A20 WORD_WRAPPED
      COLUMN dept_manager FORMAT A13 WORD_WRAPPED HEADING 'Dept. Manager'
      COLUMN dno          FORMAT 999
      SELECT employee.fname, employee.lname, employee.address,
      employee.dno, department.dname,
      employee2.fname || ' ' || employee2.lname  dept_manager
      FROM   employee, department, employee employee2
      WHERE  employee.dno = department.dnumber
      AND department.mgrssn = employee2.ssn ;
      When the above script is executed, the following output is 
displayed:

      SQL> START a:\myquery.sql

      Mon Jan 05
      Employees, Departments and Department Managers

      FNAME    LNAME    ADDRESS              DNO DNAME          Dept. Manager
      -------- -------- -------------------- --- -------------- -------------
      JOHN     SMITH    731 FONDREN,           5 RESEARCH       FRANKLIN WONG
      HOUSTON, TX

      FRANKLIN WONG     638 VOSS,HOUSTON TX    5 RESEARCH       FRANKLIN WONG
      RAMESH   NARAYAN  975 FIRE OAK,          5 RESEARCH       FRANKLIN WONG
      HUMBLE, TX

      JOYCE    ENGLISH  5631 RICE, HOUSTON,    5 RESEARCH       FRANKLIN WONG
      TX

      JAMES    BORG     450 STONE, HOUSTON,    1 HEADQUARTERS   JAMES BORG
      TX

      ALICIA   ZELAYA   3321 CASTLE, SPRING,   4 ADMINISTRATION JENNIFER
      TX                                      WALLACE

      JENNIFER WALLACE  291 BERRY, BELLAIRE,   4 ADMINISTRATION JENNIFER
      TX                                      WALLACE

      AHMAD    JABBAR   980 DALLAS, HOUSTON,   4 ADMINISTRATION JENNIFER
      TX                                      WALLACE

      8 rows selected.

Saving SQL*Plus Output using the `SPOOL` Command

SQL*Plus has a command called SPOOL that can send the output from any SQL statement to a file. Indeed, anything that is displayed in SQL*Plus can be echoed to this spool file.

The SPOOL command is invoked with the name of a file that will contain the output. Once this has been executed, the output from all subsequent SQL statements will be copied to the file. To end capturing the output, issue the SPOOL OFF command. The following is an example:

      SQL> SPOOL a:\myfile.out

      SQL> SELECT * from EMPLOYEE;

      etc.     Any SQL statements typed here will show up in the output.

      SQL> SPOOL OFF

The SPOOL OFF command turns the output off. Everything between SPOOL a:\myfile.out and SPOOL OFF will be in the file myfile.out. This is a simple ASCII text file that can be read by Windows Notepad, MS Word, or just about any word processor, e-mail package, etc. To print, load this file into MS Word, set the font to Courier and print as you would with any other document.

Note that some SQL*Plus commands will not show up in the SPOOL file. To have them echo to the SPOOL file, use the SET ECHO ON option.

Also, when SPOOLing to a file, SQL*Plus makes each line 80 characters long by padding with spaces. This can be shortened to fewer characters using the SET LINESIZE option. For example, SET LINESIZE 70 will pad each line of output to 70 characters.

The SET TRIMOUT and SET TABS options offer other ways to change the spooled output.

Prompting and Accepting user Input

SQL*Plus has several commands that can be used to prompt the user for input, accept input from the user and store it in a variable, and then use that variable in a query.

The following example shows the prompt/accept sequence for a query.

      PROMPT Type the department you are looking for
      ACCEPT dept NUMBER PROMPT "Department Number: "
      SELECT fname, lname, dno
      FROM   employee
      WHERE  dno = &dept ;

When this script is executed, the following output is shown:

      SQL>  START a:\empquery.sql
      Type the department you are looking for
      Department Number: 5
      old   3: WHERE dno = &dept
      new   3: WHERE dno =         5

      FNAME    LNAME          DNO
      -------- -------- ---------
      JOHN     SMITH            5
      FRANKLIN WONG             5
      RAMESH   NARAYAN          5
      JOYCE    ENGLISH          5
      In the above example, the user typed "5" in response to the 
Department Number: prompt.

The first PROMPT command simply echoes out a line to the display. The second command, ACCEPT, accepts input from the user. In this case, the variable that will hold the input is called dept. The input should be of type NUMBER, and the PROMPT Department Number: should be displayed.

Once the user types the department number and presses enter, the variable dept takes on the value. The following two lines (starting with old and new) are verifying the values used for the dept variable. To suppress the display of this verification, use the SET VERIFY OFF command before running the script or as one of the first commands in the script.

In the following example, additional commands have been added including the SET VERIFY and REMARK commands to improve the script.

      REMARK   This script accepts a department number as
      REMARK   input from the user and then displays the
      REMARK   last name, first name, address and department number
      REMARK   of the employees in that department
      REMARK   Turn off VERIFY
      SET VERIFY OFF

      REMARK   Format some columns
      COLUMN dno     FORMAT 99999 HEADING 'Dept.|Number'
      COLUMN address FORMAT A25   HEADING 'Address'
      COLUMN fname   FORMAT A10   HEADING 'First|Name'
      COLUMN lname   FORMAT A12   HEADING 'Last|Name'

      REMARK   Prompt the user and get a department number
      PROMPT Type the department you are looking for
      ACCEPT dept NUMBER PROMPT "Department Number: "

      REMARK   Perform the query
      SELECT fname, lname, address, dno
      FROM   employee
      WHERE  dno = &dept ;

When this script is executed, the following output is shown:

      SQL>  START a:\empquery.sql
      Type the department you are looking for
      Department Number: 4

      First      Last                                    Dept.
      Name       Name         Address                   Number
      ---------- ------------ ------------------------- ------
      ALICIA     ZELAYA       3321 CASTLE, SPRING, TX        4
      JENNIFER   WALLACE      291 BERRY, BELLAIRE, TX        4
      AHMAD      JABBAR       980 DALLAS, HOUSTON, TX        4

Collecting Statistics On SQL Statements

SQL*Plus has several commands that monitor the execution of SQL statements. The commands can be used to gather statistical information for performance monitoring purposes. The first command is called AUTOTRACE and is used to trace the execution plan for an SQL statement. To use AUTOTRACE, a special table must be created in the schema to hold the statistical information. Execute the following CREATE TABLE command in your schema:

      create table PLAN_TABLE (
      statement_id    varchar2(30),
      timestamp       date,
      remarks         varchar2(80),
      operation       varchar2(30),
      options         varchar2(30),
      object_node     varchar2(128),
      object_owner    varchar2(30),
      object_name     varchar2(30),
      object_instance numeric,
      object_type     varchar2(30),
      optimizer       varchar2(255),
      search_columns  number,
      id              numeric,
      parent_id       numeric,
      position        numeric,
      cost            numeric,
      cardinality     numeric,
      bytes           numeric,
      other_tag       varchar2(255),
      partition_start varchar2(255),
      partition_stop  varchar2(255),
      partition_id    numeric,
      other           long);
      This table need only be created once. No data is permanently 
stored in PLAN_TABLE so it will not take much if any space.

To check the execution plan for each SQL statement, turn the AUTOTRACE option on with the following SQL*Plus command:

      SET AUTOTRACE ON

Then execute an SQL statement:

      SQL>  SELECT * FROM employee;
      FNAME    MI LNAME          SSN BDATE     ADDRESS
      -------- -- -------- --------- --------- ------------------------
      JOHN     B  SMITH    123456789 09-JAN-55 731 FONDREN, HOUSTON, TX
      FRANKLIN T  WONG     333445555 08-DEC-45 638 VOSS,HOUSTON TX
      ALICIA   J  ZELAYA   999887777 19-JUL-58 3321 CASTLE, SPRING, TX
      JENNIFER S  WALLACE  987654321 20-JUN-31 291 BERRY, BELLAIRE, TX
      RAMESH   K  NARAYAN  666884444 15-SEP-52 975 FIRE OAK, HUMBLE, TX
      JOYCE    A  ENGLISH  453453453 31-JUL-62 5631 RICE, HOUSTON, TX
      AHMAD    V  JABBAR   987987987 29-MAR-59 980 DALLAS, HOUSTON, TX
      JAMES    E  BORG     888665555 10-NOV-27 450 STONE, HOUSTON, TX

      Execution Plan
      ----------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE
      1    0   TABLE ACCESS (FULL) OF 'EMPLOYEE'

      Statistics
      ----------------------------------------------------------
      0  recursive calls
      3  db block gets
      2  consistent gets
      0  physical reads
      0  redo size
      1891  bytes sent via SQL*Net to client
      651  bytes received via SQL*Net from client
      4  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      8  rows processed
      Notice the regular output from the SQL command is given followed by 
the Execution plan and the statistics.

To turn off AUTOTRACE, issue the following command:

      SET AUTOTRACE OFF

The next useful command for performance monitoring is called TIMING. This command starts a timer that can be read at any interval, similar to how a stopwatch operates. To set up a timer, issue the following command:

      SQL> TIMING START select_emp
      SQL> SELECT * FROM employee ;

      FNAME    MI LNAME          SSN BDATE
      -------- -- -------- --------- ---------
      JOHN     B  SMITH    123456789 09-JAN-55
      FRANKLIN T  WONG     333445555 08-DEC-45
      ALICIA   J  ZELAYA   999887777 19-JUL-58
      JENNIFER S  WALLACE  987654321 20-JUN-31
      RAMESH   K  NARAYAN  666884444 15-SEP-52
      JOYCE    A  ENGLISH  453453453 31-JUL-62
      AHMAD    V  JABBAR   987987987 29-MAR-59
      JAMES    E  BORG     888665555 10-NOV-27

      8 rows selected.

      SQL> TIMING SHOW select_emp
      timing for: select_emp
      real: 1760
      Thus the above query took 1.76 seconds to complete.

To stop a timer, issue the TIMING STOP command. Note that AUTOTRACE and TIMING should probably not be used in conjunction as it would be difficult to separate the execution time for the SQL statement from the time taken to generate the plan and statistics.

Using the ALTER SESSION Statement for Date Formats

In the previous examples of SQL statements, the default format of data of type DATE has been in the form: DD-MON-YY

The TO_CHAR and TO_DATE functions can be used to convert dates to other formats, however, this may become inconvenient, especially when inserting a large number of rows.

The ALTER SESSION statement can be used to alter various characteristics of the current SQL*Plus session including the default date format. This statement is often used to format dates to conform to regional customs. The syntax of ALTER SESSION for use with changing the default date format is as follows:

      ALTER SESSION
      SET NLS_DATE_FORMAT = <DATE_FORMAT>

The date_format can include the following codes:


`YY`	A 2 digit year such as 98.
`YYYY`	A 4 digit year such as 1998.
`NM`	A month number.
`MONTH`	The full name of the month.
`MON`	The abbreviated month (Jan, Feb, Mar).
`DDD`	The day of the year. For use is Julian dates.
`DD`	The day of the month.
`D`	The day of the week.
`DAY`	The name of the day.
`HH`	The hour of the day (12 hour clock)
`HH24`	The hour of the day (24 hour clock)
`MI`	The minutes.
`SS`	The seconds.

For example, to change the default date to include a full four digit year, issue the following ALTER SESSION statement:

      ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'
      From this point, all INSERT, UPDATE and DELETE statements must 
format the date accordingly. Also, any SELECT statements will return the date 
formatted accordingly.

Note that this change only remains in effect for the current session. Logging out of SQL*Plus and logging back in (or re-connecting to the Oracle database using the connect command) will reset the date format back to its default.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity. Information used on this site is at your own risk.
The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.

Editing The SQL Buffer

Formatting SQL*Plus Output

Saving SQL*Plus Output using the SPOOL Command

Prompting and Accepting user Input

Collecting Statistics On SQL Statements

Using the ALTER SESSION Statement for Date Formats

Saving SQL*Plus Output using the `SPOOL` Command