title: From SQLoader to External Tables [Oracle]
body: <p>At the moment I have some flat files, that are loaded in DB with SQLoader, but I would like to replace SQLoader scripts with external tables, but I am having problems with "translating" SQLoader to external tables.</p>
  
  <p>So, for example I have a flat file, something like this:</p>
  
  <pre><code>~#~col1&gt;|col2&gt;|col3...
  </code></pre>
  
  <p>And I have a SQloader .ctl file, which looks something like this:</p>
  
  <pre><code>LOAD DATA                                                                       
  APPEND
  CONTINUEIF NEXT PRESERVE (1:3) &lt;&gt; '~#~'
  INTO TABLE my_tab
  FIELDS TERMINATED BY "&gt;|"  TRAILING NULLCOLS          
  (                                                                               
    col1                 "LTRIM(Trim(:col1),'~#~')",
    col2                 "TRIM(:col2)",                                          
    col3                 "TRIM(:col3)",
    col4                  CONSTANT #$TASKID$#,
    col5                  CONSTANT #$SESSION$#,
    col6                  RECNUM,
    col7                  SEQUENCE(MAX)
  )  
  </code></pre>
  
  <p>At the moment I have tried a lot of things - </p>
  
  <pre><code>create table my_tab(
     col1    varchar2(100) 
    ,col2    number(38)         
    ,col3    number(38)
    ,... 
  )
  organization external(
    type oracle_loader
      default directory my_dir
      access parameters(
        records delimited by newline
        fields terminated by '&gt;|' 
        missing field values are null
        reject rows with all null fields
      )
      location('my_file.txt')
   );
  </code></pre>
  
  <p>So I have a following question:</p>
  
  <p>How do I call TRIM function, when defining external table? (is it even   possible?) Or the only way is to create another view/table for data staging, and do all data transformations and then insert data into target table?</p>
  
  <p>Thanks in advance!</p>