Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Load data file from different directories into CLOB column

618435Jun 11 2008 — edited Jun 11 2008
Hi,
I am trying to load data files (.txt) into CLOB column using SQL*Loader in Oracle 10g2.

Here is my syntax and the error message :

I followed the help form the url : http://www.orafaq.com/wiki/SQL%2ALoader_FAQ#How_does_one_use_SQL.2ALoader_to_load_images.2C_sound_clips_and_documents.3F
 CREATE TABLE file_table (
       file_id    NUMBER(5),
       file_name  VARCHAR2(30),
       file_path  VARCHAR2(2000),
       image_data CLOB);
Control File Script :
LOAD DATA
INFILE *
INTO TABLE file_table
APPEND
FIELDS TERMINATED BY ','
(
 file_path  CHAR(30),
 file_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
D:\oracle\product\10.2.0\admin\IRIS\utl\01.txt
D:\oracle\product\10.2.0\admin\IRIS\utl\TEXT\03.txt
D:\oracle\product\10.2.0\admin\IRIS\utl\TEXT\DOCS\04.txt
[/CODE]

When I executed this I am getting the following error :

C:\>sqlldr userid=siva/password1@iris control=d:\file_cont.ctl skip=4

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Jun 11 13:39:02 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

Could you help we to find out the best approach to load around 100,000 text files which are in different directories?

Is SQL*Loader is good approach or through DBMS_LOB package?

All these stuff I am doing for ORACLE_TEXT search through the documents.

sivananda                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 9 2008
Added on Jun 11 2008
1 comment
829 views