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 Go to next message
abhi_orcl
Messages: 39
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)
  );

control file:
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
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 #658911 is a reply to message #658906] Fri, 30 December 2016 12:44 Go to previous messageGo to next message
abhi_orcl
Messages: 39
Registered: December 2016
Member
Thanks Barbara..it did work for my file.
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 Go to previous messageGo to next message
abhi_orcl
Messages: 39
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
TO:
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
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.
Re: SQL Loader double quotes in clob data (3 threads from 2 sub-forums merged by bb) [message #658999 is a reply to message #658921] Tue, 03 January 2017 09:52 Go to previous message
abhi_orcl
Messages: 39
Registered: December 2016
Member
Thanks Barbara..that worked
Previous Topic: how imp new data
Next Topic: issue wit impdp
Goto Forum:
  


Current Time: Wed Dec 13 03:32:39 CST 2017

Total time taken to generate the page: 0.03216 seconds