From SQLoader to External Tables [Oracle]

Question

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.

So, for example I have a flat file, something like this:

~#~col1>|col2>|col3...

And I have a SQloader .ctl file, which looks something like this:

LOAD DATA                                                                       
APPEND
CONTINUEIF NEXT PRESERVE (1:3) <> '~#~'
INTO TABLE my_tab
FIELDS TERMINATED BY ">|"  TRAILING NULLCOLS          
(                                                                               
  col1                 "LTRIM(Trim(:col1),'~#~')",
  col2                 "TRIM(:col2)",                                          
  col3                 "TRIM(:col3)",
  col4                  CONSTANT #$TASKID$#,
  col5                  CONSTANT #$SESSION$#,
  col6                  RECNUM,
  col7                  SEQUENCE(MAX)
)  

At the moment I have tried a lot of things -

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 '>|' 
      missing field values are null
      reject rows with all null fields
    )
    location('my_file.txt')
 );

So I have a following question:

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?

Thanks in advance!

Answer 1

Assuming your records will always start with ~#~ you can consume that with a couple of dummy columns that don't map to the table definition:

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 '>|' 
      missing field values are null
      reject rows with all null fields
      (
        colx char(1) terminated by "~",
        coly char(1) terminated by "~",
        col1,
        col2,
        col3
      )
    )
    location('my_file.txt')
 );

The default behaviour without trim clause specified is LDRTRIM, which behaves the same as SQL*Loader's TRIM. You can also set that to NOTRIM as specify the whitespace trimming for each column.

---

If you want to discard your EOF marker records but load everything else you can use the LOAD WHEN option, optionally with NODISCARDFILE if you don't want to keep a record of them:

...
organization external(
  type oracle_loader
    default directory d42
    access parameters(
      records delimited by newline
      load when (1:15) != "###~~~EOF~~~###"
      nodiscardfile
      fields terminated by '>|' 
...