| Lesson 6 | Formatting date columns |
| Objective | Control 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:
- CHAR
- NCHAR
- VARCHAR2 (VARCHAR)
- NVARCHAR2 (NCHAR VARYING)
- DATE
- LONG
- BLOB , BFILE
- CLOB
- NCLOB
- 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