Re: SQL*Loader SQL Operator Limit

Bosco Ng wrote:

> Hi, I'm trying to applying SQL operations in SQL*Loader.
>
> In my SQL*Loader control file, I got something like:
>
> LOAD DATA
> REPLACE
> INTO TABLE A_TEMP
> (
> SeqNum SEQUENCE(1,1),
> A POSITION(1:10) CHAR(10),
> B POSITION(11:13) CHAR(3),
> ...
> Control_Ind EXPRESSION "CASE WHEN :A IN
> ('001','002','003','004') THEN :A||' '||:B||' '||:C||' '||:D WHEN :A
> IN ('005','006','007','008','009','010') THEN :A||' '||:B WHEN :A IN
> ('008','009','010','011','012') THEN :E END"
> )
>
> The data name and literals are dummy values just for demonstration
> purpose.
>
> The problem is, when I run this, I got the following error:
>
> SQL*Loader-350: Syntax error at line 31.
> Token longer than max allowable length of 258 chars
>
> The problem is that SQL*Loader treats the "" SQL operation as a single
> token my token is way too long.
>
> How can I handle this, I really want to do this processing while I
> load the data into the staging tables so that I don't need to update
> the staging table after the loading.
>
> Thanks

Blow the data into a staging table and parse it out from there using a trigger or proc.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)