CODEWITHSUNDEEP
oracle-databasesql-loaderseparator
Daniel Lee
Daniel Lee

Reputation: 25

How to include a separator in the inserted data in SQL load, Oracle?

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?

Upvotes: 0

Views: 491

Answers (1)

Littlefoot
Littlefoot

Reputation: 143073

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>

Upvotes: 1

Related Questions