Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Krishna.

Asked: August 23, 2000 - 4:34 pm UTC

Last updated: September 05, 2008 - 9:28 am UTC

Version: 8i

Viewed 10K+ times! This question is

You Asked

Hi,
I need some help here. I am trying to load a text document in to
a table . Structure of my table is as follows.
LOB_ID VARCHAR2(1)
MY_LOB CLOB
This is my control file.
load data
infile 'h:\roy\sqlldr\dat\clob.dat'
replace
into table test_lob
fields terminated by ','
(
lob_id char(1),
MY_LOB LOBFILE("h:\roy\doc\acct.txt") TERMINATED BY EOF
)
This is clob.dat
1,
2,

and I get the following error
SQL*Loader-416: SDF clause for field MY_LOB in table TEST_LOB references
a non existent field.

I created controlfile, data file using an exaple from oracle online doc.
Thank you so much
Krishna P Roy

and Tom said...

load data
infile *
replace
into table test_lob
fields terminated by ','
(
lob_id char(1),
lob_file FILLER char,
MY_LOB LOBFILE(lob_file) TERMINATED BY EOF
)
begindata
1,h:\roy\doc\acct.txt
2,h:\roy\doc\acct.txt


will work.

or if you really want to load the same file for all rows

LOBFILE( CONSTANT h:\roy\doc\acct.txt ) TERMINATED ....

will do it (need keyword CONSTANT and no quotes)

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

text loading

raghunath, December 04, 2001 - 6:41 am UTC

good , one of the best forums for data loading problems

Similar problem with loading pictures

Godwing, July 22, 2004 - 6:25 pm UTC

I was trying to load pictures into my database but faced with the following errors:
Here is my control file:
LOAD DATA
INFILE 'c:\icones1.dat'
INTO TABLE image_demo
FIELDS TERMINATED BY ','
(
id SEQUENCE(MAX,1),
image_type,
ext_fname FILLER CHAR(80),
image LOBFILE(ext_fname) TERMINATED BY EOF
)
and the dat file icones1.dat
jpeg,c:\image\gent.jpeg,
jpeg,c:\image\jack1.jpeg,
jpeg,c:\image\jack2.jpeg,
jpeg,c:\image\jack3.jpeg,
jpeg,c:\image\jack23.jpeg,
jpeg,c:\image\jack21.jpeg,
jpeg,c:\image\jack22.jpeg,

Operating system is windows 2000
SQL*Loader: Release 8.1.7.0.0 - Production on Mon Jul 19 11:01:49 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Control File: c:\icones1.ctl
Data File: c:\icones1.dat
Bad File: c:\icones1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table IMAGE_DEMO, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID SEQUENCE (MAX, 1)
IMAGE_TYPE FIRST * , CHARACTER
EXT_FNAME NEXT 80 , CHARACTER
(FILLER FIELD)
IMAGE DERIVED * WHT CHARACTER
Dynamic LOBFILE. Filename in field EXT_FNAME

SQL*Loader-502: unable to open data file 'c:\Image\one.jpeg' for field IMAGE table IMAGE_DEMO
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-502: unable to open data file 'c:\Image\two.jpeg' for field IMAGE table IMAGE_DEMO
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.

Table IMAGE_DEMO:
3 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: 23232 bytes(64 rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 2
Total logical records discarded: 0

Run began on Mon Jul 19 11:01:49 2004
Run ended on Mon Jul 19 11:01:49 2004

Elapsed time was: 00:00:00.37
CPU time was: 00:00:00.08


Tom Kyte
July 23, 2004 - 8:24 am UTC

and does:

SQL*Loader-502: unable to open data file 'c:\Image\one.jpeg' for field IMAGE
table IMAGE_DEMO
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.

not lead to an answer? it would appear that the files do not exist on the machine where sqlldr is running?

GETTING SQL*LOADER -925 AND ORA-3113 WHILE LOADING CLOB COLUMN ORA-3113

TP, January 16, 2005 - 12:08 am UTC

 We are trying to use the SQL*Loader to Load the CLOB column in the
    data base.
    We are getting :
       +---------------------------------------------------------------------------+
    Current system time is 15-JAN-2005 16:04:57
    +---------------------------------------------------------------------------+
    SQL*Loader-925: Error while committing
    ORA-03113: end-of-file on communication channel
    ORA-24323: value not allowed
    /smwaci/applmgr/CUSTOM/xbol/11.5.0/bin/MWACPOCN.prog: line 1:  5054
    Segmentation fault      sqlldr apps/simple4u
    data=/smwaci/applmgr/CUSTOM/xbol/11.5.0/bin/load_clob.txt
    control=/smwaci/applmgr/CUSTOM/xbol/11.5.0/bin/load_clob.ctl
    /smwaci/applmgr/CUSTOM/xbol/11.5.0/bin/MWACPOCN
    Program exited with status -117
--------
Trace file showed:
    
    Current SQL statement for this session:
    INSERT INTO LOAD_CLOB (T,TEST) VALUES (:T, :TEST)
    
    
     cursor name: INSERT INTO LOAD_CLOB (T,TEST) VALUES (:T, :TEST)
     child pin: 0, child lock: 557141a4, parent lock: 55737f1c
     xscflg: 110424, parent handle: 606284ec, xscfl2: 5040000
     bind 0: dty=1 mxl=2000(256) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=2000 offset=0
       No bind buffers allocated
     bind 1: dty=1 mxl=4001(40000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0 size=4000 offset=0
       No bind buffers allocated
    
    SQL> desc apps.load_clob
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     T                                                  CHAR(10)
     TEST                                               CLOB
     A                                                  NUMBER
---------
tried to replace CLOB with LONG and it loaded..
Can u Please tell why the load errored out with CLOB and how he can avoid it. 

Tom Kyte
January 16, 2005 - 11:22 am UTC

please contact support when you get something like this.

Loading column CLOB which is NULL

Laxman Kondal, July 01, 2005 - 2:00 pm UTC

Hi Tom

I need to load a table, which has 8 columns and 2 clob colums and both are null, from sqlldr and it spits this error:

First I used this
.....
exp_log_theclob LOBFILE(lob_file) TERMINATED BY EOF,
imp_log_theclob LOBFILE(lob_file) TERMINATED BY EOF,
....

SQL*Loader-416: SDF clause for field EXP_LOG_THECLOB in table X_EXP_EI_LOGS references a non existent field.

When I use

exp_log_theclob CHAR nullif exp_log_theclob=BLANKS,
imp_log_theclob CHAR nullif imp_log_theclob=BLANKS,

then no error but record is not loaded.

Is there any way to load the two columns which are CLOBs.

Thanks and regards

Tom Kyte
July 01, 2005 - 2:17 pm UTC

give full example, ctl file, create table and explain how to gen up sample data. use as few columns as you can to reproduce.

Loading column CLOB which is NULL

Laxman Kondal, July 06, 2005 - 3:09 pm UTC

Hi Tom

Thanks and I found the error – control file had one column less.

This is one way you point to mistake and help us to gain knowledge, Thanks.

Regards



regarding your very first reply in this thread ...

max, February 22, 2006 - 11:02 am UTC

is there any way to have the file's *NAMES* loaded (into another column) as well?

Tom Kyte
February 22, 2006 - 12:35 pm UTC

don't make it a filler column, just remove the word FILLER

How to build filename

Tom, May 15, 2007 - 8:11 am UTC

Is there any way to build the file name for the lobfile clause using other columns in the data file. For example, let us suppose I had data of the form

id     name
1      tom
2      john
3      richard


and for each row I had a file of the form 1.jpg,2.jpg etc.

Is there any way to load these files without adding an extra column to hold the file names [even though these are redundant]?
Tom Kyte
May 15, 2007 - 8:53 pm UTC

not that I know of

however, if we are on unix, we could easily "add" .jpg to the first field, and using a sql function in the ctl file, strip it off for the actual load into the column - so the lobfile would see 1.jpg, but the database would just get 1

how can we insert & retrieve to and from a table with out using SQL LOADER

A reader, September 05, 2008 - 7:42 am UTC

Hi Toam,
how can we insert & retrieve to and from a table with out using SQL LOADER by using PL/SQL
please help me with code.

THANKS in ADVANCE
Tom Kyte
September 05, 2008 - 9:28 am UTC

...
please help me with code.
....

but isn't that what you get paid to do?

but here, I ran a simple search

http://asktom.oracle.com/pls/ask/search?p_string=loading+data+plsql

and immediately found this:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:464420312302

sqlldr ignoring Byte Order Mark in LOB files

Joe, September 24, 2008 - 11:18 am UTC

I have a set of data files to be loaded into a CLOB column. The data files are a mixture of ascii files (no Byte Order Mark) and unicode encoded files (utf-8, utf-16) with the appropriate BOM at the front of the file.  When I run sqlldr to load these files into a CLOB column, the BOM is appearing at the front of the CLOB text; it looks like it was treated as part of the data of the file.  I thought (hoped!) that sqlldr would recognize the BOM and handle the encoded data file.  The primary data file is plain ascii.

Thanks for any insight you might provide.

Table: STYLESHEET
A_ID                          NOT NULL VARCHAR2(16)
A_CONTENT                     CLOB

Sqlldr Control File:
LOAD DATA
APPEND
INTO TABLE STYLESHEET 
FIELDS TERMINATED BY x'09'
TRAILING NULLCOLS
(A_ID             CHAR(16),
 LOB_FILEPATH     FILLER CHAR,
 A_CONTENT        LOBFILE(LOB_FILEPATH) TERMINATED BY EOF 
)

Sqlldr parameter file:
USERID=xxx/yyy@zzz
CONTROL=A_STYLESHEET-Test01.ctl
DATA=A_STYLESHEET-Test01.txt
BAD=A_STYLESHEET-Test01_bad.txt
DISCARD=A_STYLESHEET-Test01_discard.txt
LOG=AX_STYLESHEET-Test01.log
ERRORS=1000
READSIZE=1000000
BINDSIZE=1000000
ROWS=100

Version SQL*Loader: Release 10.1.0.4.0

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here