Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Question Unanswered: SQL*Plus default settings

    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.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •