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
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.
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
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?
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]?
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
September 05, 2008 - 9:28 am UTC
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