Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> sqlldr and LOBFILE

sqlldr and LOBFILE

From: AT <atangira_at_hotmail.com>
Date: 22 Oct 2002 14:51:05 -0700
Message-ID: <2e60bc88.0210221351.6d563f18@posting.google.com>


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.07
Received on Tue Oct 22 2002 - 16:51:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US