in sqlplus , Display column segment in maximum lenght 30 characters To make the ENAME column 20 characters wide and display EMPLOYEE NAME on two lines as the column heading, 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.99To 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 WRAPFor 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 sIn order to print the current date and the name of each job in the top title, enter the following. 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 10/25/99Job: CLERKENAME MGR HIREDATE SAL DEPTNO ---------- ---------- --------- ---------- ---------- ADAMS 7788 12-JAN-83 1100 20 JAMES 7698 03-DEC-81 950 30 MILLER 7782 23-JAN-82 1300 10 SMITH 7902 17-DEC-80 800 20 ---------- 1037.5 Job Report 10/25/99Job: SALESMANENAME MGR HIREDATE SAL DEPTNO ---------- ---------- --------- ---------- ---------- ALLEN 7698 20-FEB-81 1600 30 MARTIN 7698 28-SEP-81 1250 30 TURNER 7698 08-SEP-81 1500 30 WARD 7698 22-FEB-81 1250 30 ---------- 14008 rows selected. 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: Job Report 10/25/99Job: SALESMANHIREDATE ---------- 1981-11-17Note 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.
To make the ENAME column 20 characters wide and display EMPLOYEE NAME on two lines as the column heading, 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.99To 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 WRAPFor 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 sIn order to print the current date and the name of each job in the top title, enter the following. 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 10/25/99Job: CLERKENAME MGR HIREDATE SAL DEPTNO
---------- ---------- --------- ---------- ----------
ADAMS 7788 12-JAN-83 1100 20
JAMES 7698 03-DEC-81 950 30
MILLER 7782 23-JAN-82 1300 10
SMITH 7902 17-DEC-80 800 20
----------
1037.5 Job Report 10/25/99Job: SALESMANENAME MGR HIREDATE SAL DEPTNO
---------- ---------- --------- ---------- ----------
ALLEN 7698 20-FEB-81 1600 30
MARTIN 7698 28-SEP-81 1250 30
TURNER 7698 08-SEP-81 1500 30
WARD 7698 22-FEB-81 1250 30
----------
14008 rows selected.
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: Job Report 10/25/99Job: SALESMANHIREDATE
----------
1981-11-17Note 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.