| SQLloader does not load blob [message #664377] |
Fri, 14 July 2017 06:00  |
 |
hrista
Messages: 4
Registered: July 2017
|
Junior Member |
|
|
Hello,
I have a problem with loading data via sqlloader.
I have table "fxx_kruz_xml" with one clob column "xml_dok", records are in unload file, each reccord has blob stored in the file.
After loading, sqlloader echoed all reccords are successfully loaded, but no blob was loaded.
What may be the problem ?
I tried insert one reccord with blob via sqlplus, and this was OK.
Structure of the table fxx_kruz_xml :
$ echo 'describe fxx_kruz_xml;' | sqlplus /
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 14 12:46:58 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Pi Jul 14 2017 12:39:05 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> Name Null? Type
----------------------------------------- -------- ----------------------------
X_KRUZ_XML NOT NULL NUMBER(10)
X_DOK NUMBER(10)
XML_DOK NCLOB
X_KRUZ_UZ NUMBER(10)
X_KRUZ_UV NUMBER(10)
X_KRUZ_VS NUMBER(10)
D_POSL_MOD NOT NULL DATE
CTL file for sqlloader :
$ more /LOAD_FS/UNLOAD10099/UZ/CTL/fxx_kruz_xml.ctl.1
OPTIONS (ROWS=500, BINDSIZE=6500000, READSIZE=13000000, PARALLEL=TRUE, DIRECT=FALSE)
LOAD DATA
CHARACTERSET UTF8
INFILE '/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.unl.1' "str ']\n'"
BADFILE '/LOAD_FS/UNLOAD10099/UZ/BAD/fxx_kruz_xml.bad.1'
DISCARDFILE '/LOAD_FS/UNLOAD10099/UZ/DSC/fxx_kruz_xml.dsc.1'
APPEND
INTO TABLE fxx_kruz_xml
FIELDS TERMINATED BY ']'
TRAILING NULLCOLS
(
X_kruz_xml,
X_dok,
xml_dok_filename FILLER CHAR(100),
xml_dok LOBFILE(xml_dok_filename) TERMINATED BY EOF NULLIF xml_dok=BLANKS,
X_kruz_uz,
X_kruz_uv,
X_kruz_vs,
d_posl_mod DATE "RRRR-MM-DD HH24:MI:SS"
)
First 10 rows from /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.unl.1 file :
$ head -10 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.unl.1
622400]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_622400.2.blob]]621957]]2014-04-28 07:11:59]
778800]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_778800.2.blob]]778262]]2014-05-05 19:15:42]
255056]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_255056.2.blob]]255024]]2014-03-24 10:29:28]
1110656]10115]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_1110656.2.blob]444462]1104661]]2015-03-03 13:32:11]
697704]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_697704.2.blob]]697161]]2014-05-04 01:34:16]
343704]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_343704.2.blob]]343593]]2014-04-01 21:51:47]
48256]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_48256.2.blob]]48256]]2014-03-06 18:25:16]
624752]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_624752.2.blob]]624308]]2014-04-28 09:14:15]
267256]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_267256.2.blob]]267219]]2014-06-06 09:22:09]
1068752]]/LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_1068752.2.blob]]1064373]]2014-10-08 07:25:19]
Listing blob files :
-rw-r--r-- 1 zaved uziv_dis 1318 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_622400.2.blob
-rw-r--r-- 1 zaved uziv_dis 2752 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_778800.2.blob
-rw-r--r-- 1 zaved uziv_dis 1393 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_255056.2.blob
-rw-r--r-- 1 zaved uziv_dis 11039 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_1110656.2.blob
-rw-r--r-- 1 zaved uziv_dis 2706 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_697704.2.blob
-rw-r--r-- 1 zaved uziv_dis 9567 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_343704.2.blob
-rw-r--r-- 1 zaved uziv_dis 2403 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_48256.2.blob
-rw-r--r-- 1 zaved uziv_dis 9394 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_624752.2.blob
-rw-r--r-- 1 zaved uziv_dis 4885 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_267256.2.blob
-rw-r--r-- 1 zaved uziv_dis 2678 Jul 12 15:53 /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.1_1068752.2.blob
Log from loading :
$ expand fxx_kruz_xml.log.1
SQL*Loader: Release 12.2.0.1.0 - Production on Thu Jul 13 22:40:38 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Control File: /LOAD_FS/UNLOAD10099/UZ/CTL/fxx_kruz_xml.ctl.1
Character Set UTF8 specified for all input.
Data File: /LOAD_FS/UNLOAD10099/UZ/fxx_kruz_xml.unl.1
File processing option string: "str ']
'"
Bad File: /LOAD_FS/UNLOAD10099/UZ/BAD/fxx_kruz_xml.bad.1
Discard File: /LOAD_FS/UNLOAD10099/UZ/DSC/fxx_kruz_xml.dsc.1
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 200 rows, maximum of 500000 bytes
Continuation: none specified
Path used: Conventional
Table FXX_KRUZ_XML, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X_KRUZ_XML FIRST * ] CHARACTER
X_DOK NEXT * ] CHARACTER
XML_DOK_FILENAME NEXT 100 ] CHARACTER
(FILLER FIELD)
XML_DOK DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field XML_DOK_FILENAME
Character Set UTF8 specified for all input.
NULL if XML_DOK = BLANKS
X_KRUZ_UZ NEXT * ] CHARACTER
X_KRUZ_UV NEXT * ] CHARACTER
X_KRUZ_VS NEXT * ] CHARACTER
D_POSL_MOD NEXT * ] DATE RRRR-MM-DD HH24:MI:SS
Table FXX_KRUZ_XML:
254069 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 330000 bytes(200 rows)
Read buffer bytes:13000000
Total logical records skipped: 0
Total logical records read: 254069
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Thu Jul 13 22:40:38 2017
Run ended on Thu Jul 13 22:41:36 2017
Elapsed time was: 00:00:58.41
CPU time was: 00:00:00.87
Count all records from fxx_kruz_xml after loading :
$ echo 'select count(*) from fxx_kruz_xml;' | sqlplus /
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 14 12:57:32 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Pi Jul 14 2017 12:46:58 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
COUNT(*)
----------
2032680
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Count all records which have blob :
$ echo 'select count(*) from fxx_kruz_xml where XML_DOK is not null;' | sqlplus /
SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 14 12:58:35 2017
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Pi Jul 14 2017 12:57:32 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
COUNT(*)
----------
0
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQLloader does not load blob [message #664440 is a reply to message #664421] |
Mon, 17 July 2017 18:45  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
hrista wrote on Mon, 17 July 2017 04:09OK, it is functioning, super,
but what happend if blob column xml_dok will be empty ?
The length will be 0. If you want it to be null, then you can update it like so:
update fxx_kruz_xml set xml_dok = null where length (xml_dok) = 0;
You have been inconsistent with blob, clob, and nclob. They are different and, in some situations, the difference can matter. You should be clear on what the data type of your column in your Oracle table is (blob, clob, or nclob) and what type of data you have in your file, such as image or text or rich text.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
