From SQLoader to External Tables [Oracle]
<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>