ignore space in column value while loading data using sql loader by oracle

1
votes

I am trying to load data using oracle sql loader and using space as separator of columns but I am facing problem that one of the columns value including space, I need your support to avoid considering this space as column separator.

I tried to use regexp_replace and replace functions

DSTCOUNTRY " REGEXP_REPLACE(:DSTCOUNTRY,'dstcountry=','')",

the column value is: dstcountry="United States"

and the expecting value to be stored in the table is: United States

The sql loader command is: load data infile 'in' append into table test_table fields terminated by " " optionally enclosed by '"' TRAILING NULLCOLS DSTCOUNTRY " REPLACE(:DSTCOUNTRY,'dstcountry=','')",

I am using oracle 10G and 12C.

1
oraclesql-loader
Can you please share the SQL loader command or parameter file which you have used to load the data? - Edit the question and add the details. - Popeye
You may need to use the Oracle DataPump utility which has more flexibility on input data. Can you also edit your question and add a tag for your Oracle version, such as Oracle12c - Mark Stewart

1 Answers

1
votes

According to what you posted so far, it is optionally enclosed you're looking for. Here's an example.

Test table:

SQL> create table test (id number, dstcountry varchar2(20));

Table created.

Control file (contains sample data as well):

load data 
infile *
replace
into table test
fields terminated by " " 
optionally enclosed by '"'
trailing nullcols
(
id,
dstcountry)

begindata
123 "Croatia"
125 "United States"

Loading session and result:

SQL> $sqlldr scott/tiger control=test08.ctl log=test08.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pon Srp 22 17:59:23 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

SQL> select * from test;

        ID DSTCOUNTRY
---------- --------------------
       123 Croatia
       125 United States

SQL>