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.

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

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.

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. The following paragraph discusses interpreting SQL*Plus column formats.

The default date format in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter and may use a date format displaying two digit years. You can use the SQL TO_CHAR function, or the SQL*Plus COLUMN FORMAT command in your SELECT statements to control the way dates are displayed in your report.

The default width of CHAR, NCHAR, VARCHAR2 (VARCHAR) and NVARCHAR2 (NCHAR VARYING) columns is the width of the column in the database. SQL*Plus formats these datatypes left-justified. If a value does not fit within the column width, SQL*Plus wraps or truncates the character string depending on the setting of SET WRAP.
A LONG, BLOB, BFILE, CLOB, NCLOB or XMLType column's width defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever one is smaller. SQL*Plus truncates or wraps XMLType columns after 2000 bytes. To avoid this you need to set an explicit COLUMN format for the XMLType column. A COLUMN format can be up to a maximum of 60000 per row.