SQL*Plus Quick Reference
Release 8.1.5

A66735-01

Library

Product

Prev Up Next

SQL*Plus Quick Reference, 6 of 10


Formatting Query Results

Use the following commands to format, store and print your query results.

ATTRIBUTE [type_name.attribute_name [option...]]

      Specifies display attributes for a given column, or lists the current display attributes for a single column or for all columns; option represents one of the following clauses:

      ALI[AS] alias
      CLE[AR]
      FOR[MAT] format
      LIKE {type_name.attribute_name|alias}
      ON|OFF
      
BRE[AK] [ON report_element [action [action]]] ...

      Specifies where and how formatting will change in a report (for example, skipping a line each time a given column value changes). Enter BREAK with no clauses to list the current BREAK definition.

      Where report_element requires the following syntax:

      {column|expr|ROW|REPORT}
      
      

      and where action requires the following syntax:

      [SKI[P] n|[SKI[P]] PAGE] [NODUP[LICATES]|DUP[LICATES]]
      
BTI[TLE] [printspec [text|variable] ...]|[OFF|ON]

      Places and formats the specified title at the bottom of each report page, or lists the current BTITLE definition. See TITLE for additional information on valid printspec clauses.

CL[EAR] option ...

      Resets or erases the current value or setting for the specified option; option represents one of the following clauses:

      BRE[AKS]
      BUFF[ER]
      COL[UMNS]
      COMP[UTES]
      SCR[EEN]
      SQL
      TIMI[NG]
      
COL[UMN] [{column|expr} [option ...]]

      Specifies the display attributes for a given column, such as text for the column heading, or formats for CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING), LONG, CLOB, NCLOB and NUMBER data; option represents one of the following clauses:

      ALI[AS] alias
      CLE[AR]
      FOLD_A[FTER]
      FOLD_B[EFORE]
      FOR[MAT] format
      HEA[DING] text
      JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
      LIKE {expr|alias}
      NEWL[INE]
      NEW_V[ALUE] variable
      NOPRI[NT]|PRI[NT]
      NUL[L] text
      OLD_V[ALUE] variable
      ON|OFF
      WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
      

      Enter COLUMN followed by column or expr and no other clauses to list the current display attributes for only the specified column or expression. Enter COLUMN with no clauses to list all current column display attributes.

      Enter FORMAT followed by the appropriate format element to specify the display format for the column.

      To change the display format of a NUMBER column, use FORMAT followed by one of the elements in the following table:

      Element  Example(s)  Description 

      9999
      
       

      Number of "9"s specifies number of significant digits returned. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero. 

      0999
      9990
      
       

      Displays a leading zero or a value of zero in this position as a 0. 

      $9999
      
       

      Prefixes value with dollar sign. 

      B9999
      
       

      Displays a zero value as blank, regardless of "0"s in the format model. 

      MI 

      9999MI
      
       

      Displays "-" after a negative value. For a positive value, a trailing space is displayed.  

      S9999
      
       

      Returns "+" for positive values and "-" for negative values in this position. 

      PR 

      9999PR
      
       

      Displays a negative value in <angle brackets>. For a positive value, a leading and trailing space is displayed. 

      99D99
      
       

      Displays the decimal character in this position, separating the integral and fractional parts of a number. 

      9G999
      
       

      Displays the group separator in this position. 

      C999
      
       

      Displays the ISO currency symbol in this position. 

      L999
      
       

      Displays the local currency symbol in this position. 

      , (comma) 

      9,999
      
       

      Displays a comma in this position. 

      . (period) 

      99.99
      
       

      Displays a period (decimal point) in this position, separating the integral and fractional parts of a number. 

      999V99
      
       

      Multiplies value by 10n, where n is the number of "9"s after the "V." 

      EEEE 

      9.999EEEE
      
       

      Displays value in scientific notation (format must contain exactly four "E"s). 

      RN or rn 

      RN
      
       

      Displays upper- or lowercase Roman numerals. Value can be an integer between 1 and 3999. 

      DATE 

      DATE
      
       

      Displays value as a date in MM/DD/YY format; used to format NUMBER columns that represent Julian dates. 

COMP[UTE] [function [LAB[EL] text] ...
OF {
expr|column|alias} ...
ON {
expr|column|alias|REPORT|ROW} ...]

      Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions. The following table lists valid functions. All functions except NUMBER apply to non-null values only.

      Function  Computes  Applies to Datatypes 
      AVG
      
       

      Average of non-null values 

      NUMBER 

      COU[NT]
      
       

      Count of non-null values 

      All types 

      MAX[IMUM]
      
       

      Maximum value 

      NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING) 

      MIN[IMUM]
      
       

      Minimum value 

      NUMBER, CHAR, NCHAR, VARCHAR2 (VARCHAR), NVARCHAR2 (NCHAR VARYING) 

      NUM[BER]
      
       

      Count of rows 

      All types 

      STD
      
       

      Standard deviation of non-null values 

      NUMBER 

      SUM
      
       

      Sum of non-null values 

      NUMBER 

      VAR[IANCE]
      
       

      Variance of non-null values 

      NUMBER 

REPF[OOTER] [PAGE] [printspec [text|variable] ...] |[OFF|ON]

      Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition. See REPHEADER for additional information on valid printspec clauses.

REPH[EADER] [PAGE] [printspec [text|variable] ...] |[OFF|ON]

      Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition. Use one of the following clauses in place of printspec:

      COL n
      S[KIP] [n]
      TAB n
      LE[FT]
      CE[NTER]
      R[IGHT]
      BOLD
      FORMAT text
      
SPO[OL] [filename[.ext]|OFF|OUT]

      Stores query results in an operating system file and, optionally, sends the file to a printer. OFF stops spooling. OUT stops spooling and sends the file to your host computer's standard (default) printer. Enter SPOOL with no clauses to list the current spooling status.

TTI[TLE] [printspec [text|variable] ...]|[OFF|ON]

      Places and formats a specified title at the top of each report page, or lists the current TTITLE definition. Use one of the following clauses in place of printspec:

      COL n
      S[KIP] [n]
      TAB n
      LE[FT]
      CE[NTER]
      R[IGHT]
      BOLD
      FORMAT text
      

Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product