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
  2  FROM dual;
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
  5. DATE
  6. LONG
  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