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

You can't handle data of more than 32k with PL/SQL.

You will need to use C to do this.

Or if you aren't feeling quite so masochistic, Perl will handle this also.

Jared

On Wednesday 30 April 2003 08:17, Martin, Alan (Contractor) (DLIS) wrote:
> 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
>
> (c) Copyright 1998 Oracle Corporation. All rights reserved.
>
> 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

Content-Type: text/html; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

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).