Thanks for the question, Marcelo.
Asked: May 22, 2017 - 8:00 pm UTC
Last updated: May 24, 2017 - 3:11 am UTC
Version: 11g
Viewed 10K+ times! This question is
You Asked
Hi Tom.
I need your help.
I'm trying to loader data using sqlldr to an Oracle 11g database.
My data has a column that sometimes comes like below:
,"\"Taiwan Mobile\"",
My ctl has:
FIELDS TERMINATED BY ","
optionally enclosed by '"'
I've been tried many types of replace and regexp_replace and I continue getting no solution.
My replaces works fine in SQL Developer, but it doesn't work in SQLLDR.
Examples of replaces used with both replace and regexp_replace:
, column "REPLACE(:column,'\"')"
, column "REPLACE(:column,'\\"')"
, column "REPLACE(:column,'\\\"')"
, column "REPLACE(:column,'"')"
In any case I got errors in my bad file at this column!!
Could you help me?
and Connor said...
I'd use an external table
SQL> CREATE TABLE t (
2 x VARCHAR2(50)
3 )
4 ORGANIZATION EXTERNAL (
5 TYPE ORACLE_LOADER
6 DEFAULT DIRECTORY temp
7 ACCESS PARAMETERS (
8 RECORDS DELIMITED BY NEWLINE
9 FIELDS TERMINATED BY ','
10 MISSING FIELD VALUES ARE NULL
11 (
12 x CHAR(50)
13 )
14 )
15 LOCATION ('t.dat')
16 );
Table created.
SQL>
SQL>
SQL> select * from t;
X
--------------------------------------------------
"\"Taiwan Mobile\""
SQL>
SQL> select replace(trim('"' from replace(x,'\"',chr(0) )),chr(0),'"')
2 from t;
REPLACE(TRIM('"'FROMREPLACE(X,'\"',CHR(0))),CHR(0)
--------------------------------------------------
"Taiwan Mobile"
Is this answer out of date? If it is, please let us know via a Comment