load data
infile 'c:\oracle_toad\sql_loader\v1_data.txt'
replace into table v1 fields terminated by ','
( a integer external, b char, c char )
1,2,"da,ta1"
2,4,"dat,a2"
2,4,"da,ta2"
"" are not supposed to be inserted as a part of data. That's just for reference.
I intentionally inserted "," in each of the data set. I am hoping to insert 1, 2, "da,ta1" <<< like this. Is there a way that I can include the separator "," within the data set?
Here's an example:
Test table:
SQL> create table test (col1 number, col2 varchar2(20), col3 varchar2(20));
Table created.
Control file:
load data
infile *
replace
into table test
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
col1,
col2,
col3
)
begindata
1,2,"da,ta1"
2,4,"dat,a2"
2,4,"da,ta2"
Loading session & the result:
SQL> $sqlldr scott/tiger control=test04.ctl log=test04.log
SQL*Loader: Release 11.2.0.2.0 - Production on Pon Kol 27 14:11:26 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 2
Commit point reached - logical record count 3
SQL> select * From test;
COL1 COL2 COL3
---------- -------------------- --------------------
1 2 da,ta1
2 4 dat,a2
2 4 da,ta2
SQL>