How to load data into Oracle using SQL Loader with skipping and merging columns?

Question

I am trying to load data into Oracle database using sqlloader, My data looks like following. 1|2|3|4|5|6|7|8|9|10

I do not want to load first and last column into table, I want to load 2|3|4|5|6|7|8|9 into one field. The table I am trying to load into has only one filed named 'field1'.

If anyone has this kind of experience, could you give some advice? I tried BOUNDFILLER, FILLER and so on, I could not make it. Help me. :)

Gary_W Gary_W · Accepted Answer · 2017-05-12T03:54:14

Load the entire row from the file into a BOUNDFILLER, then extract the part you need into the column. You have to tell sqlldr that the field is terminated by the carriage return/linefeed (assuming a Windows OS) so it will read the entire line from the file as one field. here the whole line from the file is read into "dummy" as BOUNDFILLER. "dummy" does not match a column name, and it's defined as BOUNDFILLER anyway, so the whole row is "remembered". The next line in the control file starts with a column that DOES match a column name, so sqlldr attempts to execute the expression. It extracts a substring from the saved "dummy" and puts it into the "col_a" column.

The regular expression in a nutshell returns the part of the string after but not including the first pipe, and before but not including the last pipe. Note the double backslashes. In my environment anyway, when using a backslash to take away the special meaning of the pipe (not needed when between the square brackets) it gets stripped when passing from sqlldr to the regex engine so two backslashes are required in the control file (normally a pipe symbol is a logical OR) so one gets through in the end. If you have trouble here, try one backslash and see what happens. Guess how long THAT took me to figure out!

load data 
infile 'x_test.dat' 
TRUNCATE
into table x_test
FIELDS TERMINATED BY x'0D0A'
(
 dummy BOUNDFILLER,
 col_a expression "regexp_substr(:dummy, '[^|]*\\|(.+)\\|.*', 1, 1, NULL, 1)"
)

EDIT: Use this to test the regular expression. For example, if there is an additional pipe at the end:

select regexp_substr('1|2|3|4|5|6|7|8|9|10|', '[^|]*\|(.+)\|.*\|', 1, 1, NULL, 1)
from dual;

2nd edit: For those uncomfortable with regular expressions, this method uses nested SUBSTR and INSTR functions:

SQL> with tbl(str) as (
     select '1|2|3|4|5|6|7|8|9|10|' from dual
   )
   select substr(str, instr(str, '|')+1, (instr(str, '|', -1, 2)-1 - instr(str
, '|')) ) after
   from tbl;

AFTER
---------------
2|3|4|5|6|7|8|9

Deciding which is easier to maintain is up to you. Think of the developer after you and comment at any rate! :-)

How to load data into Oracle using SQL Loader with skipping and merging columns?

1 Answers