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

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

"FC" <flavio_at_tin.it> wrote in message news:<Dim4a.196488$0v.5480215_at_news1.tin.it>...
> "Travis Bryant" <tbryant_at_chevrontexaco.com> wrote in message
> news:28892ae6.0302171733.28ee0680_at_posting.google.com...
> > Hi, I am attempting to load a file using SQL*Loader to store html
> > pages as CLOBs in an Oracle 8.1.7 db.
> >
> > Previously we stored the html pages as LONGs, but since we are moving
> > to a new database we thought we needed to change the LONGs to CLOBs.
> > However, the existing control file will not work. Here is the relevant
> > section of the existing control file:
> >
> > into table cra_stg_invoice_cb_page
> > 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) VARCHAR(20000)
> >
> > When I try to change the datatype of the last column ("html_page") to
> > CHAR instead of VARCHAR(20000) (as it suggests in the Oracle reference
> > manual), I get this error message:
> >
> > SQL*Loader-462: error inserting LOB into column HTML_PAGE, row 2,
> > table cra_stg_invoice_cb_page ORA-24806: LOB form mismatch
> >
> > I've tried giving the CHAR a size (like 20000), or making the POSITION
> > go from (33:19999), but nothing seems to work. Any insight would be
> > appreciated.
> >
> > Thanks,
> > Travis Bryant
> > tbryant_at_chevrontexaco.com
>
> It looks like you are loading inline CLOBs.
> How do you deal with newlines contained in the HTML string?
> Is newline your record terminator or what?
> Why not using file locators pointing to each html source instead?
>
> Bye,
> Flavio
Received on Wed Feb 19 2003 - 10:37:25 CST