In a follow up to my recent post on using the STR directive with sql loader here is another example where the same technique is used to load data which has carriage return / line feed characters within it that we want to actually load and store. Normally with basic sql loader syntax these would really confuse things.
STR to the rescue again.
Here is some setup for the demo
We create a basic table
create table demo( a_id number,h_id number,title varchar2(128),descn varchar2(4000),risk varchar2(4000),comm varchar2(4000));
And this is the file we want to load
[#BOR#][#EOC#]109[#EOC#]4[#EOC#]testdata_Duplicate[#EOC#]testdata_Duplicate from chat[#EOC#]this
is
carriage return field[#EOC#]test2[#EOR#]
The file has [#EOR#] as an end of record marker, and [#EOC#] as the field terminator, the data in the middle contains carriage returns that we want to preserve on load.
So what does the control file look like to do that?
LOAD DATA
INFILE "data.dat" "STR '[#EOR#]'"
INTO TABLE DEMO
TRUNCATE
FIELDS TERMINATED by '[#EOC#]'
TRAILING NULLCOLS
(field filler,a_id integer external,h_id integer external,title char(128),descn char(4000),risk char(4000),comm char(4000))
So we tell sql loader explicitly the field and record terminators - now lets load this in
sqlldr / control=data.ctl
SQL*Loader: Release 12.1.0.2.0 - Production on Tue Jan 13 16:58:01 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Table DEMO:
1 Row successfully loaded.
Check the log file:
data.log
for more information about the load.
Selecting the data back confirms the carriage returns are there (and doubly confirmed by the use of the dump function to show what data is actually there.
sqlplus /
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 13 16:59:16 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Jan 13 2015 16:59:11 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> set pages 0 lines 1024
SQL> select * from demo;
109 4 testdata_Duplicate
testdata_Duplicate from chat
this
is
carriage return field
test2
SQL> select dump(risk) from demo;
Typ=1 Len=30: 116,104,105,115,10,105,115,10,99,97,114,114,105,97,103,101,32,114,101,116,117,114,110,32,32,102,105,101,108,100
Very easy once you know how!
Comments
Post a Comment