Oracle SQL - COLSEP and NUMWIDTH

Introduction

The default space separating the result columns is replaced by a vertical line.

It sets the GRADE and BONUS columns to 10 digits wide.

The following code uses the COLSEP and NUMWIDTH System Variables.

set colsep " | "
set numwidth 10

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w  w w  .  j a  v  a  2 s. com

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800.1234 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600.5678, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250.98765, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975.07860, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250.0009987, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850.999876, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450.230987, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL> select * from emp;

    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07001.00 | SMITH    | N     | TRAINER  |  07902.00 | 17-DEC-75 |  01800.12
 [N/A]    |  00020.00

 07002.00 | ALLEN    | JAM   | SALESREP |  07006.00 | 20-MAY-71 |  01600.57
 00300.00 |  00030.00

 07003.00 | WARD     | TF    | SALESREP |  07006.00 | 02-MAR-72 |  01250.99
 00500.00 |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07004.00 | JACK     | JM    | MANAGER  |  07009.00 | 02-APR-77 |  02975.08
 [N/A]    |  00020.00

 07005.00 | BROWN    | P     | SALESREP |  07006.00 | 28-SEP-76 |  01250.00
 01400.00 |  00030.00

 07006.00 | BLAKE    | R     | MANAGER  |  07009.00 | 01-NOV-73 |  02851.00
 [N/A]    |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07007.00 | CLARK    | AB    | MANAGER  |  07009.00 | 09-JUN-75 |  02450.23
 [N/A]    |  00010.00

 07008.00 | SCOTT    | DEF   | TRAINER  |  07004.00 | 26-NOV-79 |  03000.00
 [N/A]    |  00020.00

 07009.00 | KING     | CC    | DIRECTOR |  [N/A]    | 17-OCT-72 |  05000.00
 [N/A]    |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07010.00 | BREAD    | JJ    | SALESREP |  07006.00 | 28-SEP-78 |  01500.00
 00000.00 |  00030.00

 07011.00 | ADAMS    | AA    | TRAINER  |  07008.00 | 30-DEC-76 |  01100.00
 [N/A]    |  00020.00

 07012.00 | JONES    | R     | ADMIN    |  07006.00 | 03-OCT-79 |  08000.00
 [N/A]    |  00030.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07902.00 | FORD     | MG    | TRAINER  |  07004.00 | 13-FEB-79 |  03000.00
 [N/A]    |  00020.00

 07934.00 | MARY     | ABC   | ADMIN    |  07007.00 | 23-JAN-72 |  01300.00
 [N/A]    |  00010.00


14 rows selected.

SQL>
SQL> set colsep " | "
SQL>
SQL> set numwidth 10
SQL>
SQL> select * from emp;

    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07001.00 | SMITH    | N     | TRAINER  |  07902.00 | 17-DEC-75 |  01800.12
 [N/A]    |  00020.00

 07002.00 | ALLEN    | JAM   | SALESREP |  07006.00 | 20-MAY-71 |  01600.57
 00300.00 |  00030.00

 07003.00 | WARD     | TF    | SALESREP |  07006.00 | 02-MAR-72 |  01250.99
 00500.00 |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07004.00 | JACK     | JM    | MANAGER  |  07009.00 | 02-APR-77 |  02975.08
 [N/A]    |  00020.00

 07005.00 | BROWN    | P     | SALESREP |  07006.00 | 28-SEP-76 |  01250.00
 01400.00 |  00030.00

 07006.00 | BLAKE    | R     | MANAGER  |  07009.00 | 01-NOV-73 |  02851.00
 [N/A]    |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07007.00 | CLARK    | AB    | MANAGER  |  07009.00 | 09-JUN-75 |  02450.23
 [N/A]    |  00010.00

 07008.00 | SCOTT    | DEF   | TRAINER  |  07004.00 | 26-NOV-79 |  03000.00
 [N/A]    |  00020.00

 07009.00 | KING     | CC    | DIRECTOR |  [N/A]    | 17-OCT-72 |  05000.00
 [N/A]    |  00010.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07010.00 | BREAD    | JJ    | SALESREP |  07006.00 | 28-SEP-78 |  01500.00
 00000.00 |  00030.00

 07011.00 | ADAMS    | AA    | TRAINER  |  07008.00 | 30-DEC-76 |  01100.00
 [N/A]    |  00020.00

 07012.00 | JONES    | R     | ADMIN    |  07006.00 | 03-OCT-79 |  08000.00
 [N/A]    |  00030.00


    EMPNO | ENAME    | INIT  | JOB      |       MGR | BDATE     |      MSAL
--------- | -------- | ----- | -------- | --------- | --------- | ---------
     COMM |    DEPTNO
--------- | ---------
 07902.00 | FORD     | MG    | TRAINER  |  07004.00 | 13-FEB-79 |  03000.00
 [N/A]    |  00020.00

 07934.00 | MARY     | ABC   | ADMIN    |  07007.00 | 23-JAN-72 |  01300.00
 [N/A]    |  00010.00


14 rows selected.

SQL>

Related Topic