Oracle PL/SQL Tutorial/SQL PLUS Session Environment/numformat
set numformat
The default value for NUMFORMAT is 10.
SQL*Plus will try to fit all the number data in 10 spaces of output if NUMFORMAT is 10.
SQL> create table t( my_column number );
Table created.
SQL>
SQL> insert into t values ( 1234567890 );
1 row created.
SQL> insert into t values ( 12345678901 );
1 row created.
SQL> insert into t values ( 12345678901234567890 );
1 row created.
SQL>
SQL> select * from t;
MY_COLUMN
----------
1234567890
1.2346E+10
1.2346E+19
3 rows selected.
SQL> set numformat 99999999999999999999.99999999999999;
SQL>
SQL> select * from t;
MY_COLUMN
------------------------------------
1234567890.00000000000000
12345678901.00000000000000
12345678901234567890.00000000000000
3 rows selected.
SQL> set numformat "";
SQL>
SQL> select * from t;
MY_COLUMN
----------
1234567890
1.2346E+10
1.2346E+19
3 rows selected.
SQL> drop table t;
Table dropped.
set numformat 09999.99
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> set numformat 9.99
SQL>
SQL> select * from employees;
EMPNO last_name INIT JOB MGR BDATE MSAL DEPTNO
----- -------------------- ----- -------- ----- ---------- ----- ------
1.00 Jason N TRAINER 2.00 18-12-1965 ##### #####
2.00 Jerry J SALESREP 3.00 19-11-1966 ##### #####
3.00 Jord T SALESREP 4.00 21-10-1967 ##### #####
4.00 Mary J MANAGER 5.00 22-09-1968 ##### #####
5.00 Joe P SALESREP 6.00 23-08-1969 ##### #####
6.00 Black R MANAGER 7.00 24-07-1970 ##### #####
7.00 Red A MANAGER 8.00 25-06-1971 ##### #####
EMPNO last_name INIT JOB MGR BDATE MSAL DEPTNO
----- -------------------- ----- -------- ----- ---------- ----- ------
8.00 White S TRAINER 9.00 26-05-1972 ##### #####
9.00 Yellow C DIRECTOR ##### 27-04-1973 ##### #####
##### Pink J SALESREP null 28-03-1974 ##### #####
10 rows selected.
SQL>
SQL>
SQL> set numformat 09999.99
SQL>
SQL>
SQL> select * from employees;
EMPNO last_name INIT JOB MGR BDATE MSAL
--------- -------------------- ----- -------- --------- ---------- ---------
DEPTNO
---------
00001.00 Jason N TRAINER 00002.00 18-12-1965 00800.00
00010.00
00002.00 Jerry J SALESREP 00003.00 19-11-1966 01600.00
00010.00
EMPNO last_name INIT JOB MGR BDATE MSAL
--------- -------------------- ----- -------- --------- ---------- ---------
DEPTNO
---------
00003.00 Jord T SALESREP 00004.00 21-10-1967 01700.00
00020.00
00004.00 Mary J MANAGER 00005.00 22-09-1968 01800.00
EMPNO last_name INIT JOB MGR BDATE MSAL
--------- -------------------- ----- -------- --------- ---------- ---------
DEPTNO
---------
00020.00
00005.00 Joe P SALESREP 00006.00 23-08-1969 01900.00
00030.00
EMPNO last_name INIT JOB MGR BDATE MSAL
--------- -------------------- ----- -------- --------- ---------- ---------
DEPTNO
---------
00006.00 Black R MANAGER 00007.00 24-07-1970 02000.00
00030.00
00007.00 Red A MANAGER 00008.00 25-06-1971 02100.00
00040.00
EMPNO last_name INIT JOB MGR BDATE MSAL
--------- -------------------- ----- -------- --------- ---------- ---------
DEPTNO
---------
00008.00 White S TRAINER 00009.00 26-05-1972 02200.00
00040.00
00009.00 Yellow C DIRECTOR 00010.00 27-04-1973 02300.00
EMPNO last_name INIT JOB MGR BDATE MSAL
--------- -------------------- ----- -------- --------- ---------- ---------
DEPTNO
---------
00020.00
00010.00 Pink J SALESREP null 28-03-1974 02400.00
00030.00
10 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
set numformat 99999
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> set numformat 9
SQL>
SQL> select * from employees;
EMPNO last_name INIT JOB MGR BDATE MSAL DEPTNO
----- -------------------- ----- -------- --- ---------- ---- ------
1 Jason N TRAINER 2 18-12-1965 ## ##
2 Jerry J SALESREP 3 19-11-1966 ## ##
3 Jord T SALESREP 4 21-10-1967 ## ##
4 Mary J MANAGER 5 22-09-1968 ## ##
5 Joe P SALESREP 6 23-08-1969 ## ##
6 Black R MANAGER 7 24-07-1970 ## ##
7 Red A MANAGER 8 25-06-1971 ## ##
EMPNO last_name INIT JOB MGR BDATE MSAL DEPTNO
----- -------------------- ----- -------- --- ---------- ---- ------
8 White S TRAINER 9 26-05-1972 ## ##
9 Yellow C DIRECTOR ## 27-04-1973 ## ##
## Pink J SALESREP nul 28-03-1974 ## ##
l
10 rows selected.
SQL>
SQL> set numformat 99999
SQL>
SQL> select * from employees;
EMPNO last_name INIT JOB MGR BDATE MSAL DEPTNO
------ -------------------- ----- -------- ------ ---------- ------ ------
1 Jason N TRAINER 2 18-12-1965 800 10
2 Jerry J SALESREP 3 19-11-1966 1600 10
3 Jord T SALESREP 4 21-10-1967 1700 20
4 Mary J MANAGER 5 22-09-1968 1800 20
5 Joe P SALESREP 6 23-08-1969 1900 30
6 Black R MANAGER 7 24-07-1970 2000 30
7 Red A MANAGER 8 25-06-1971 2100 40
EMPNO last_name INIT JOB MGR BDATE MSAL DEPTNO
------ -------------------- ----- -------- ------ ---------- ------ ------
8 White S TRAINER 9 26-05-1972 2200 40
9 Yellow C DIRECTOR 10 27-04-1973 2300 20
10 Pink J SALESREP null 28-03-1974 2400 30
10 rows selected.
SQL>
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>