Specifies display attributes for a given column, such as
Syntax
COL[UMN] [{column|expr} [option ...]]
where 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]Terms and Clauses
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.
Refer to the following list for a description of each term or clause:
| {column|expr} | Identifies the data item (typically, the name of a column) in a SQL SELECT command to which the column command refers. If you use an expression in a COLUMN command, you must enter expr exactly as it appears in the SELECT command. If the expression in the SELECT command is a+b, for example, you cannot use b+a or (a+b) in a COLUMN command to refer to the expression in the SELECT command. |
| If you select columns with the same name from different tables, a COLUMN command for that column name will apply to both columns. That is, a COLUMN command for the column ENAME applies to all columns named ENAME that you reference in this session. COLUMN ignores table name prefixes in SELECT commands. Also, spaces are ignored unless the name is placed in double quotes. | |
| To format the columns differently, assign a unique alias to each column within the SELECT command itself (do not use the ALIAS clause of the COLUMN command) and enter a COLUMN command for each column's alias. | |
| ALI[AS] alias | Assigns a specified alias to a column, which can be used to refer to the column in BREAK, COMPUTE, and other COLUMN commands. |
| The MI and PR format elements can only appear in the last position of a number format model. The S format element can only appear in the first or last position. | |
| If a number format model does not contain the MI, S or PR format elements, negative return values automatically contain a leading negative sign and positive values automatically contain a leading space. | |
| A number format model can contain only a single decimal character (D) or period (.), but it can contain multiple group separators (G) or commas (,). A group separator or comma cannot appear to the right of a decimal character or period in a number format model. | |
| SQL*Plus formats NUMBER data right-justified. A NUMBER column's width equals the width of the heading or the width of the FORMAT plus one space for the sign, whichever is greater. If you do not explicitly use FORMAT, then the column's width will always be at least the value of SET NUMWIDTH. | |
| If a value does not fit within the column width, SQL*Plus indicates overflow by displaying a pound sign (#) in place of each digit the width allows. | |
| If a positive value is extremely large and a numeric overflow occurs when rounding a number, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and a numeric overflow occurs when rounding a number, then the negative infinity sign replaces the value (-~). | |
| With all number formats, SQL*Plus rounds each value to the specified number of significant digits as set with the SET NUMWIDTH command. | |
| HEA[DING] text | Defines a column heading. If you do not use a HEADING clause, the column's heading defaults to column or expr. If text contains blanks or punctuation characters, you must enclose it with single or double quotes. Each occurrence of the HEADSEP character (by default, '|') begins a new line. For example, |
COLUMN ENAME HEADING 'Employee |Name'
You can enter any number of COLUMN commands for one or more columns. All column attributes set for each column remain in effect for the remainder of the session, until you turn the column OFF, or until you use the CLEAR COLUMN command. Thus, the COLUMN commands you enter can control a column's display attributes for multiple SQL SELECT commands.
When you enter multiple COLUMN commands for the same column, SQL*Plus applies their clauses collectively. If several COLUMN commands apply the same clause to the same column, the last one entered will control the output.
Examples
To make the ENAME column 20 characters wide and display EMPLOYEE NAME on two lines at the top, enter
SQL> COLUMN ENAME FORMAT A20 HEADING 'EMPLOYEE |NAME'
To format the SAL column so that it shows millions of dollars, rounds to cents, uses commas to separate thousands, and displays $0.00 when a value is zero, you would enter
SQL> COLUMN SAL FORMAT $9,999,990.99
To assign the alias NET to a column containing a long expression, to display the result in a dollar format, and to display <NULL> for null values, you might enter
SQL> COLUMN SAL+COMM+BONUS-EXPENSES-INS-TAX ALIAS NET SQL> COLUMN NET FORMAT $9,999,999.99 NULL '<NULL>'
Note that the example divides this column specification into two commands. The first defines the alias NET, and the second uses NET to define the format.
Also note that in the first command you must enter the expression exactly as you entered it (or will enter it) in the SELECT command. Otherwise, SQL*Plus cannot match the COLUMN command to the appropriate column.
To wrap long values in a column named REMARKS, you can enter
SQL> COLUMN REMARKS FORMAT A20 WRAP
For example:
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- --------------------
123 25-AUG-86 144 This order must be s
hipped by air freigh
t to ORDIf you replace WRAP with WORD_WRAP, REMARKS looks like this:
CUSTOMER DATE QUANTITY REMARKS
---------- --------- -------- ---------------------
123 25-AUG-86 144 This order must be
shipped by air freight
to ORDIf you specify TRUNCATE, REMARKS looks like this:
CUSTOMER DATE QUANTITY REMARKS ---------- --------- -------- -------------------- 123 25-AUG-86 144 This order must be s
In order to print the current date and the name of each job in the top title, enter the following. (For details on creating a date variable, see "Displaying the Current Date in Titles" under "Defining Page Titles and Dimensions"![[*]](jump.gif)
.)
SQL> COLUMN JOB NOPRINT NEW_VALUE JOBVAR
SQL> COLUMN TODAY NOPRINT NEW_VALUE DATEVAR
SQL> BREAK ON JOB SKIP PAGE ON TODAY
SQL> TTITLE CENTER 'Job Report' RIGHT DATEVAR SKIP 2 -
> LEFT 'Job: ' JOBVAR SKIP 2
SQL> SELECT TO_CHAR(SYSDATE, 'MM/DD/YY') TODAY,
2 ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO
3 FROM EMP WHERE JOB IN ('CLERK', 'SALESMAN')
4 ORDER BY JOB, ENAME;Your two page report would look similar to the following report, with "Job Report" centered within your current linesize:
Job Report 08/01/94 Job: CLERK ENAME MGR HIREDATE SAL DEPTNO ---------- ------- --------- ----------- ---------- ADAMS 7788 14-JAN-87 1100 20 JAMES 7698 03-DEC-81 950 30 MILLER 7782 23-JAN-82 1300 10 SMITH 7902 17-DEC-80 800 20
Job Report 08/01/94 Job: CLERK ENAME MGR HIREDATE SAL DEPTNO ---------- ------- --------- ----------- ---------- ALLEN 7698 20-JAN-81 1600 30 MARTIN 7698 03-DEC-81 950 30 MILLER 7782 23-JAN-82 1300 10 SMITH 7902 17-DEC-80 800 20
To change the default format of DATE columns to 'YYYY-MM-DD', you can enter
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
The following output results:
Session altered
To display the change, enter a SELECT statement, such as:
SQL> SELECT HIREDATE 2 FROM EMP 3 WHERE EMPNO = 7839;
The following output results:
HIREDATE ---------- 1981-11-17
See the Oracle7 Server SQL Language Reference Manual for information on the ALTER SESSION command.
Note that in a SELECT statement, some SQL calculations or functions, such as TO_CHAR, may cause a column to be very wide. In such cases, use the FORMAT option to alter the column width.