SQL*Plus default settings

alain78700 · #1 (**permalink**) 11-21-11, 08:03

Hello,

I have an Oracle table (let's call it TOTO) with columns (le't call them COLUM1 and COLUM2) having a format "NOT NULL NUMBER (13,4).

One row in this TOTO table has the following values : COLUM1 = 956147811.7200 and COLUM2 = 334649782.2500.

When doing a "standard and simple" select through SQL*Plus on these columns, it returns rounded values that could be confusing !!

SQL> select column1, column2 from toto where .......;

COLUM1 COLUM2
---------- ----------
956147812 334649782

Only by using the TO_CHAR statement with a format tied to column's definition, I can retrieve the true content of these columns :

SQL> select to_char(colum1, '9999999999.9999'), to_char(colum2, '9999999999.9999') from toto where .......;

TO_CHAR(COLUM1,' TO_CHAR(COLUM2,'
---------------- ----------------
956147811.7200 334649782.2500

Is there a way (option in the SQL*Plus settings) to have the true values returned when doing a "simple" select ??

Thanks in advance for your answers.

Alain.

Littlefoot · #2 (**permalink**) 11-21-11, 08:50

SET NUMWIDTH is, I suppose, what you are looking for. By default, it is set to 10.

Here's an example:

Code:

SQL> create table test (col number);

Table created.

SQL> insert all
  2    into test values (956147811.7200)
  3    into test values (334649782.2500)
  4  select * from dual;

2 rows created.

SQL> select col from test;

       COL
----------
 956147812
 334649782

SQL> set numwidth 12
SQL> select col from test;

         COL
------------
956147811,72
334649782,25

SQL> set numwidth 20
SQL> select col from test;

                 COL
--------------------
        956147811,72
        334649782,25

SQL>

More reading here: SET System Variable Summary.

Internet Services

Web Development

Digital Marketing

Consumer Tech