Home » RDBMS Server » Server Utilities » SQL Loader double quotes in clob data (3 threads from 2 sub-forums merged by bb) (Oracle Database 12c 12.1.0.2.0 - 64bit Production )
| SQL Loader double quotes in clob data (3 threads from 2 sub-forums merged by bb) [message #658903] |
Thu, 29 December 2016 16:46  |
 |
abhi_orcl
Messages: 40
Registered: December 2016
|
Member |
|
|
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 |
|
|
|
| Re: SQL Loader double quotes in clob data (3 threads from 2 sub-forums merged by bb) [message #658906 is a reply to message #658903] |
Thu, 29 December 2016 18:17  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The double quotes are not a problem.
The carriage returns do present a problem. You need to have some way to tell where the record ends, such as a record delimiter or something at the beginning or end of each row that tells if to continue or concatenating rows if there are always the same number of rows per record. In the example below, I have used AM as the record delimiter.
You need to replace :s_row_upd with :r_upd.
You cannot have hh (hour) of 00 with am or pm, so you must use hh24 and take a substr (substring) to eliminate the am.
Please see the example below.
SCOTT@orcl_12.1.0.2.0> host type test.ctl
OPTIONS (ERRORS=100)
load data
infile 'test_rec.dat' "str'AM'"
into table test_clob
fields terminated by '|'
trailing nullcols
(
NM,
SEQ,
D_REF CHAR(2000000),
USERID,
R_UPD "TO_TIMESTAMP(SUBSTR(:r_upd, 1, 24), 'mon dd yyyy hh24:mi:ss:ff3')"
)
SCOTT@orcl_12.1.0.2.0> CREATE TABLE TEST_CLOB
2 (
3 "NM" VARCHAR2(30 CHAR),
4 "SEQ" NUMBER(7,0),
5 "D_REF" CLOB,
6 "USERID" VARCHAR2(10 CHAR),
7 "R_UPD" TIMESTAMP (6)
8 )
9 /
Table created.
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=test.ctl
SQL*Loader: Release 12.1.0.2.0 - Production on Thu Dec 29 16:12:06 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Table TEST_CLOB:
1 Row successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> select * from test_clob
2 /
NM SEQ
------------------------------ ----------
D_REF
--------------------------------------------------------------------------------
USERID
----------
R_UPD
---------------------------------------------------------------------------
a_006_tyu 1
replace 90
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no prin
t.printername="" print.documentname="" print.orientation = 1 print.margin.left =
110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 pri
nt.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.pr
ompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overri
deprintjob=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
18-JUN-04 12.00.00.006000 AM
1 row selected.
|
|
|
|
|
|
| Re: SQL Loader double quotes in clob data (3 threads from 2 sub-forums merged by bb) [message #658920 is a reply to message #658911] |
Fri, 30 December 2016 15:52 |
|
abhi_orcl
Messages: 40
Registered: December 2016
|
Member |
|
|
There is just one more issue that I am facing now. The requirement says not to store the data file information in the control file and hence the current setup changes FROM:
load data
[b]infile 'test_rec.dat' "str'AM'"[/b]
into table test_clob
load data
"str'AM'
into table test_clob
and then while running the SQL Loader command, include the data file name like - sqlldr scott/tiger control=test.ctl data=<my_locns>/test_rec.dat.
Now, when I am invoking the SQL Loader using the changes above, I am getting the error:
SQL*Loader-350: Syntax error at line 3.
Expecting keyword INTO, found "str'AM'".
"str'AM'"
^
I am unable to get a workaround for this? Is there any solution to this?
Thanks
[Updated on: Fri, 30 December 2016 15:53] Report message to a moderator |
|
|
|
| Re: SQL Loader double quotes in clob data (3 threads from 2 sub-forums merged by bb) [message #658921 is a reply to message #658920] |
Fri, 30 December 2016 18:05 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In your control file, use:
infile * "str'AM'"
In your command line, where you specify data= ... your file path will be different from mine. I am using Windows, so I use \ instead of /. Also, if there are spaces or other unusual characters in your file path, then you may need to enclose the whole path and file name in double quotes, as I have done below. On your operating system, the path and file name may also be case sensitive.
Please see the demonstration below.
SCOTT@orcl_12.1.0.2.0> host type test.ctl
OPTIONS (ERRORS=100)
load data
infile * "str'AM'"
into table test_clob
fields terminated by '|'
trailing nullcols
(
NM,
SEQ,
D_REF CHAR(2000000),
USERID,
R_UPD "TO_TIMESTAMP(SUBSTR(:r_upd, 1, 24), 'mon dd yyyy hh24:mi:ss:ff3')"
)
SCOTT@orcl_12.1.0.2.0> host sqlldr scott/tiger control=test.ctl data="c:\my_oracle_files\test_rec.dat"
SQL*Loader: Release 12.1.0.2.0 - Production on Fri Dec 30 16:01:17 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Table TEST_CLOB:
1 Row successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> select * from test_clob
2 /
NM SEQ
------------------------------ ----------
D_REF
--------------------------------------------------------------------------------
USERID
----------
R_UPD
---------------------------------------------------------------------------
a_006_tyu 1
replace 90
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no prin
t.printername="" print.documentname="" print.orientation = 1 print.margin.left =
110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 pri
nt.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.pr
ompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overri
deprintjob=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
18-JUN-04 12.00.00.006000 AM
1 row selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 02:42:53 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
