Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> sqlldr and LOBFILE
Hi,
I am trying to load a text file into a LONG RAW column and just not able to make it work :( I have looked at the Oracle documentation and searched the groups and have tried multiple permutations and combinations. (Only thing that works is inlining the data to be loaded (which is text) in the primary data file and using VARRAW() with the length-value pair data. However, I need to ave the data read from files in my application and cannot inline the data).
Can someone please help me figure out how to make it work through dynamic external files and what is going on here??!!
The table is defined as:
PK_ID NUMBER NOT NULL, COMMUNITY VARCHAR2 (10), BLOB_TYPE CHAR (10), BLOB LONG RAW NOT NULL, STATUS CHAR (10),
The control file is:
LOAD DATA
INFILE 'testlob.dat' "str '<endrec>\r\n'"
DISCARDMAX 1000
APPEND
INTO TABLE ec_blob
APPEND
FIELDS TERMINATED BY "|"
(fname FILLER CHAR, pk_id INTEGER EXTERNAL, community CONSTANT "xxx", blob_type CONSTANT "char", blob LOBFILE(fname) VARRAW(126976), status CONSTANT "Active")
The data file is:
a.txt|50<endrec>
The log file is (which does not really say what the error is):
SQL*Loader: Release 8.1.7.0.0 - Production on Tue Oct 22 17:33:18 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Control File: testlob.ctl
Data File: testlob.dat
File processing option string: "str '<endrec>\r\n'"
Bad File: testlob.bad
Discard File: testlob.dsc
(Allow 1000 discards)
Number to load: ALL Number to skip: 0 Errors allowed: 10 Bind array: 64 rows, maximum of 65536 bytes Continuation: none specified Path used: Conventional
Table EC_BLOB, loaded from every logical record. Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- FNAME FIRST * | CHARACTER (FILLER FIELD) PK_ID NEXT * | CHARACTER COMMUNITY CONSTANT Value is 'xxx' BLOB_TYPE CONSTANT Value is 'char' BLOB DERIVED ***** VARRAW
Maximum field length is 126978
Dynamic LOBFILE. Filename in field FNAME
STATUS CONSTANT
Value is 'Active'
Record 1: Rejected - Error on table EC_BLOB. ORA-00000: normal, successful completion
Table EC_BLOB:
0 Rows successfully loaded.
1 Row 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: 36736 bytes(64 rows) Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 1 Total logical records rejected: 1 Total logical records discarded: 0
Run began on Tue Oct 22 17:33:18 2002
Run ended on Tue Oct 22 17:33:19 2002
Elapsed time was: 00:00:00.47 CPU time was: 00:00:00.07Received on Tue Oct 22 2002 - 16:51:05 CDT