Hi everyone,
to start with below are the metadata information:
table:
CREATE TABLE TEST_CLOB
(
"NM" VARCHAR2(30 CHAR),
"SEQ" NUMBER(7,0),
"D_REF" CLOB,
"USERID" VARCHAR2(10 CHAR),
"R_UPD" TIMESTAMP (6)
);
OPTIONS (ERRORS=100)
load data
infile 'test_rec.dat'
into table test_clob
fields terminated by '|'
trailing nullcols
(
NM,
SEQ,
D_REF CHAR(2000000),
USERID,
R_UPD "TO_TIMESTAMP(:s_row_upd, 'mon dd yyyy hh:mi:ss:ff3am')"
)
file test_rec.dat:
a_006_tyu|1|replace 90;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.printername="" print.documentname="" print.orientation = 1 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes hidegrayline=no )
header(height=504 color="536870912" )
summary(height=0 color="536870912" )
footer(height=100 color="536870912" )
detail(height=144 color="536870912" )
table(column=(type=char(1) updatewhereclause=no name=status dbname="status" )
|am14|Jun 18 2004 00:00:00:006AM
As you can see in the data for the file, it is pipe delimited and has double quotes ebmedded in the strings. By using the control file I am not able to proceed with the load. I think mostly because the text mentioned above has quotes and carriage returns.
I tried by using replacing double quotes with '\' which I read in few forums but I got error.
Can someone please advice?
Thanks
[Updated on: Thu, 29 December 2016 17:01]
Report message to a moderator