2 Replies Latest reply: Mar 2, 2013 7:52 AM by user12068228 RSS

    External Table - load a logfile with more than 4000 bytes per column

    user12068228
      Hi
      I'm trying to import a logfile in a database table which has only one column: txt_line
      In this column I'm trying to fill a log entry per record. Each log entry normally has more than 4000 bytes so in the external table it should be a clob.
      Below is a working external table which works but cuts every entry after 4000 bytes. How ist it possible to directly load the data in a clob column? All I found are descriptions where I have one clob-file per record.
      Any help is appreciated
      Thank you



      Source-File
      ....more than 4000 bytes.......]].......more thatn 4000 bytes.....]]......more than 4000 bytes......

      ]] ist the record delimiter

      External table:
      create table TST_TABLE
      (
      txt_line varchar2(4000)
      )
      organization external
      (type
      ORACLE_LOADER
      default directory tmp_ext_tables
      access parameters (
      records delimited by ']]'
      fields (txt_line char(4000))
      )
      location ('test5.log')
      )
      reject limit 0
      ;
        • 1. Re: External Table - load a logfile with more than 4000 bytes per column
          L-MachineGun
          user12068228 wrote:

          I'm trying to import a logfile in a database table which has only one column: txt_line
          In this column I'm trying to fill a log entry per record. Each log entry normally has more than 4000 bytes so in the external table it should be a clob.
          Below is a working external table which works but cuts every entry after 4000 bytes. How ist it possible to directly load the data in a clob column? All I found are descriptions where I have one clob-file per record.
          Any help is appreciated
          . . . E t c . . .
          And what did you expect if you define the source field and target column as 4000 characters?

          Try this:
          CREATE TABLE tst_table
           (
             txt_line CLOB
           )
           ORGANIZATION EXTERNAL
           (TYPE oracle_loader
              DEFAULT DIRECTORY tmp_ext_tables
              ACCESS PARAMETERS (
                 RECORDS DELIMITED BY ']]'
                 FIELDS (txt_line CHAR(32000))
              )
            LOCATION ('test5.log')
           )
          REJECT LIMIT 0
          ;
          :p