Home » RDBMS Server » Server Utilities » SQLloader does not load blob (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production)
SQLloader does not load blob [message #664377] Fri, 14 July 2017 06:00 Go to next message
hrista
Messages: 3
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 #664378 is a reply to message #664377] Fri, 14 July 2017 07:09 Go to previous messageGo to next message
BlackSwan
Messages: 25523
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/search?hl=en&site=webhp&source=hp&q=sqlldr+load+blob

Are the columns X_kruz_uz, X_kruz_uv, X_kruz_vs, d_posl_mod properly loaded?
Re: SQLloader does not load blob [message #664386 is a reply to message #664377] Sat, 15 July 2017 00:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8724
Registered: November 2002
Location: California, USA
Senior Member
Remove the following part from your SQL*Loader control file:

NULLIF xml_dok=BLANKS
Re: SQLloader does not load blob [message #664421 is a reply to message #664386] Mon, 17 July 2017 06:09 Go to previous messageGo to next message
hrista
Messages: 3
Registered: July 2017
Junior Member
OK, it is functioning, super,
but what happend if blob column xml_dok will be empty ?
Re: SQLloader does not load blob [message #664439 is a reply to message #664421] Mon, 17 July 2017 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65079
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why are you talking about BLOB when it is NCLOB?

Re: SQLloader does not load blob [message #664440 is a reply to message #664421] Mon, 17 July 2017 18:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8724
Registered: November 2002
Location: California, USA
Senior Member
hrista wrote on Mon, 17 July 2017 04:09
OK, 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.

Re: SQLloader does not load blob [message #664493 is a reply to message #664440] Thu, 20 July 2017 02:40 Go to previous message
hrista
Messages: 3
Registered: July 2017
Junior Member
You are right, I am new in oracle world, I migrate from Informix to Oracle
so I must clarify Oracle terminology.

Veru thank you for your answers.
Previous Topic: datapump export import
Next Topic: importing tables....
Goto Forum:
  


Current Time: Fri Jul 21 00:19:25 CDT 2017

Total time taken to generate the page: 0.05559 seconds