Re: Using SQL* Loader to load inline CLOBs, getting error

From: FC <flavio_at_tin.it>
Date: Wed, 19 Feb 2003 18:06:27 GMT
Message-ID: <DIP4a.202959$0v.5678028@news1.tin.it>

"Travis Bryant" <tbryant_at_chevrontexaco.com> wrote in message news:28892ae6.0302190837.7a747b31_at_posting.google.com...
> Originally the exact same data file was used to load into a table
> where the datatype of the column in question was a LONG, not a CLOB.
> Thus, in the control file, we could specify position(33) and VARCHAR
> as the datatype and it would read all the way to the end of the line.
> There were never newline characters in the html so no need to worry
> about that. We may have to load the html from a second file but we're
> trying not to change the file format because it is an interface from
> another system and we're trying to minimize changes to that system.
>
> Thanks,
> Travis
>

This one works on my system, but, if html grows beyond 20000 characters, you'll need to adjust it.
Also, it requires the default newline terminator character(s) to work:

load data
infile 'test'
into table cra_stg_invoice_cb_page
replace
when (1:1) = 'I'

 (invoice_number                POSITION(02:11) CHAR(10),
  company_code                  POSITION(12:15) CHAR(4),
  fiscal_year                   POSITION(16:19) CHAR,
  customer_number               POSITION(20:29) CHAR(10),
  page_number                   POSITION(30:32) DECIMAL EXTERNAL,
  html_page                     POSITION(33)    CHAR(20000) TERMINATED BY

"\n")

hope it helps.
Bye,
Flavio Received on Wed Feb 19 2003 - 12:06:27 CST