SQL* Plus CLI  «Prev  Next»

Lesson 6Formatting date columns
ObjectiveControl the display of date columns.

Formatting Date Columns(Control Display)

SQL*Plus does not include support for formatting date columns. To format a date, you must use Oracles built-in TO_CHAR function within your SQL statement to convert the date to a character string in your desired format. You then have a text column, and you can use the SQL*Plus COLUMN command to specify a width and title. The following example shows TO_CHAR being used to convert a date so that it displays in day-month-year format:

SQL> COLUMN todays_date FORMAT A11
SQL> SELECT TO_CHAR(SYSDATE,'dd-Mon-yyyy')
todays_date
  2  FROM dual;
TODAYS_DATE
-----------
27-Sep-1999
Notice that the select statement specifies a column alias of todays_date, and that the name used in the COLUMN command matches that alias. That is key to making this work. If you do not specify a column alias, then Oracle will generate a name based on the formula used for the column, making it difficult for you to write a COLUMN command that matches.

Formatting Datatypes

When displaying datatypes, you can either accept the SQL*Plus default display width or you can change it using the COLUMN command. The format model will stay in effect until you enter a new one, reset the column's format with
COLUMN column_name CLEAR
or exit from SQL*Plus. Datatypes, in this manual, include the following types:
  1. CHAR
  2. NCHAR
  3. VARCHAR2 (VARCHAR)
  4. NVARCHAR2 (NCHAR VARYING)
  5. DATE
  6. LONG
  7. BLOB , BFILE
  8. CLOB
  9. NCLOB
  10. XMLType

Default Display

The default width of datatype columns is the width of the column in the database. The column width of a LONG, BLOB, BFILE, CLOB, NCLOB or XMLType defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever is the smaller. The default width and format of unformatted DATE columns in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter. Otherwise, the default format width is A9. Left justification is the default for datatypes.

Changing Default Display

You can change the displayed width of a datatype or DATE, by using the COLUMN command with a format model consisting of the letter A (for alphanumeric) followed by a number representing the width of the column in characters. Within the COLUMN command, identify the column you want to format and the model you want to use:
COLUMN column_name FORMAT model
If you specify a width shorter than the column heading, SQL*Plus truncates the heading.

Oracle Column Format

Click the link below to read about interpreting SQL*Plus column formats.
Oracle Column Format