Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

March 28, 2008

Number data type formating with sql*plus – numwidth hint

Filed under: Oracle How To — H.Tonguç Yılmaz @ 7:23 am

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;

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

set numwidth 18
select * from t;

              COL1
------------------
123456789123456789

Thanks to Eduardo Legatti for this hint.

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 76 other followers