Display numeric values without scientific notation in Oracle select

Navigation
0

I have to make multiple selects in Oracle using SQLPLUS displaying various information to be stored in text file. Some tables have numeric fields of type NUMBER (19). Some of these fields have values that are too high so that Oracle views them in scientific notation as 8.0E + 13. I know that if I use a to_char (field, '999999999999999') I can format the field but I do not want to do this as there are many tables and fields. Is there any way I can format the output so that all numeric fields are displayed without scientific notation? Here is a sample code. 

    create table teste_numerico(
      numero number(19)
    );

    insert into teste_numerico values (80000000003147);                      
    insert into teste_numerico values (80000000003153);                       
    insert into teste_numerico values (80000000003163);                     
    insert into teste_numerico values (80000000003170);                       
    insert into teste_numerico values (80000000003176);                       
    insert into teste_numerico values (80000000003181);                       
    insert into teste_numerico values (80000000003188);                       
    insert into teste_numerico values (80000000003194);                       
    insert into teste_numerico values (80000000003199);                       
    insert into teste_numerico values (80000000003206);                       
    insert into teste_numerico values (80000000003212);

Select Unformatted: 

    select *
      from teste_numerico;

Result: 

        NUMERO
    ----------
       8.0E+13 
       8.0E+13 
       8.0E+13 
       8.0E+13 
       8.0E+13 
       8.0E+13 
       8.0E+13 
       8.0E+13 
       8.0E+13 
       8.0E+13 
       8.0E+13 

     11 linhas selecionadas

Select with formatting: 

    select to_char(numero, '999999999999999')
      from teste_numerico;

Result: 

    TO_CHAR(NUMERO,'999999999999999')
    ---------------------------------
      80000000003147                  
      80000000003153                  
      80000000003163                  
      80000000003170                  
      80000000003176                  
      80000000003181                  
      80000000003188                  
      80000000003194                  
      80000000003199                  
      80000000003206                  
      80000000003212                  

     11 linhas selecionadas
    
asked by anonymous 19.03.2015 / 16:21

1 answer

0

numformat can be used 

set numformat 99999999999
select *
       from teste_numerico;
    
19.03.2015 / 17:33
Problems with PDFView Lib 'MsAjaxBundle' is not a valid script name. The name must be terminated with '.js'