Oracle-L: Need PL/SQL script to read hex ascii file into a LONG RAW db colu

From: Martin, Alan (Contractor) (DLIS) <Alan.S.Martin_at_dla.mil>
Date: Wed, 30 Apr 2003 07:17:12 -0800
Message-ID: <F001.0058CA19.20030430071712@fatcity.com>

Step 1 of an application conversion requires a hex ascii flat file to be read into the following 8.0.5 DB table on a Sun box:

SQL> desc textbuf

 Name                            Null?    Type
 ------------------------------- -------- ----
 RECID                                    NUMBER(38)
 LOCKID                                   NUMBER(38)
 INCREV                                   NUMBER(38)
 INCLEN                                   NUMBER(38)
 BASEREV                                  NUMBER(38)
 BASELEN                                  NUMBER(38)
 INC                                      LONG RAW --> must remain this type

for step 1

Using SQL*Loader with a HEXTORAW function in the ctl file, I was able to load the table with limited success. As an experiment, I then tried to load a second table changing the LONG RAW to CLOB. Also, with limited success.

SQL> desc textbuf2

 Name                            Null?    Type
 ------------------------------- -------- ----
 ...
 INC                                      CLOB

It appears that short lines load fine. Lines of 90K, etc seem to be too long. The following ctl file was used in the CLOB case:

OPTIONS( READSIZE=50001556, BINDSIZE=50001556 ) LOAD DATA
INFILE '$data/textbuf0.unl'
TRUNCATE
INTO TABLE textbuf_load
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS

( RECID                                  INTEGER EXTERNAL 


, LOCKID                                 INTEGER EXTERNAL 

, INCREV                                 INTEGER EXTERNAL 

, INCLEN                                 INTEGER EXTERNAL 

, BASEREV                                INTEGER EXTERNAL 

, BASELEN                                INTEGER EXTERNAL 

, INC                                    CHAR(50000000) TERMINATED BY '|'

)

$ sqlldr / TEXTBUF.ctl

SQL*Loader: Release 8.0.5.0.0 - Production on Wed Apr 30 9:57:50 2003

Commit point reached - logical record count 1 SQL*Loader-523: error -2 writing to file (textbuf0.bad)

How do I handle these very long lines? Using PL/SQL? Anyone have a script?

Thanx,

Alan Martin
Defense Logistics Information Service

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Martin, Alan (Contractor) (DLIS)
  INET: Alan.S.Martin_at_dla.mil

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Apr 30 2003 - 10:17:12 CDT