2 Replies Latest reply: Jul 5, 2013 10:57 AM by Richard Harrison . RSS

    Number format in external tables

    oralicious

      Is it possible to create an external table with a numeric datatype looking at a file with a number format with a comma?

       

      If I try and create a table looking at the following data, the table creates but the row gets rejected with "invalid number" for fieldb

       

      fielda|fieldb

      somedata|1,000

      drop table xt_test;
      
      CREATE TABLE XT_test
                          ( fielda   varchar2(10),
                            fieldb   number
                          )
      ORGANIZATION EXTERNAL
        (  TYPE ORACLE_LOADER
           DEFAULT DIRECTORY XT__LOGS
           ACCESS PARAMETERS 
             ( records delimited by newline
                 badfile      XT__Bad:'testcsv.bad'
                 logfile     XT__LOGS:'testcsv.log'
                 discardfile XT__LOGS:'testcsv.dsc'
                 skip 1
                 fields  terminated by '|' 
                 lrtrim
                 missing field values are null
                         ( FIELDA                                 CHAR(20) , 
                           FIELDB                                 CHAR(20) 
                          )
                )
           LOCATION (XT__LOGS:'test.csv')
        )
      REJECT LIMIT UNLIMITED NOMONITORING;
      
      select * from XT_test;
      
        • 1. Re: Number format in external tables
          Purvesh K

          IMK, you cannot format the Numeric Data using External Tables. Hence, Creating the Column with Character (VARCHAR2) datatype initially for load and then processing it to convert into Number datatype looks like the best available option.

           

          Remember, to remove the Comma's in data, before altering column datatype.

          • 2. Re: Number format in external tables
            Richard Harrison .

            Hi,

            If you are on 11.2 you can use a preprocessor to solve this

             

            create a file called test.sh in you oracle directory (make sure it has execute permissions) - this file should contain

             

            /bin/sed 's/,//g' $1

             

            this will preprocess the csv file and remove all commas

             

             

             

            DROP TABLE XT_TEST;

            CREATE TABLE XT_test
                                ( fielda   varchar2(10),
                                  fieldb   number
                                )
            ORGANIZATION EXTERNAL
              (  TYPE ORACLE_LOADER
                 DEFAULT DIRECTORY DATA_PUMP_DIR
                 ACCESS PARAMETERS
                   ( records delimited by newline
            PREPROCESSOR DATA_PUMP_DIR:'test.sh'
                       badfile      DATA_PUMP_DIR:'testcsv.bad'
                       logfile     DATA_PUMP_DIR:'testcsv.log'
                       discardfile DATA_PUMP_DIR:'testcsv.dsc'
                       skip 1
                       fields  terminated by '|'
                       lrtrim
                     missing field values are null
                               ( FIELDA                                 CHAR(20) ,
                                 FIELDB    
                                )
                      )
                 LOCATION (DATA_PUMP_DIR:'test.csv')
              )
            REJECT LIMIT UNLIMITED NOMONITORING
            /

             

            Magic.....

             

            Regards,

            Harry

             

            http://dbaharrison.blogspot.com