Number data type formating with sql*plus – numwidth hint

After all those years I still love to see I am learning lots of new stuff each day. This is what makes you fit and motivates to go deeper with Oracle I guess. In my opinion especially small things that you forget or even somehow you didn’t still read about makes the difference, here is today’s example :)

-- until this morning I was also using column formating for large numbers like this
drop table tt purge;
create table tt (col1 number(18), col2 number(18));

insert into tt values (123456789123456789,123456789123456789);

select * from tt;

      COL1       COL2
---------- ----------
1,2346E+17 1,2346E+17

col col1 for 999999999999999999.9999999999
select * from tt;

                          COL1       COL2
------------------------------ ----------
 123456789123456789.0000000000 1,2346E+17

-- but from this morning an OTN forum thread reminded me SQL*Plus's numwidth option :)
drop table t purge;
create table t (col1 number(18));

insert into t values (123456789123456789);

show numwidth
numwidth 10
select * from t;


set numwidth 18
select * from t;


Thanks to Eduardo Legatti for this hint.

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s