Sql Loader syntax error, but why ?????

[GBall]

Hi,
I'm trying to load a file with the following control file, but get a syntax error as follows. No one here, including the DBA can work out why. He's off today, so I'm posting here to see if anyone can help meantime.

Control File:
Load data
truncate
into table t_mailing_preference
fields terminated by "," optionally enclosed by '"'
WHEN ACTION="I" or ACTION="R" or ACTION="X"
(
ACTION "UPPERACTION)",
REQUEST_DATE "TO_DATEREQUEST_DATE,'DDMMYYYY')",
INTEREST_AREAS "CONCAT(UPPERINTEREST_AREAS),UPPER(04))",
NEW_INTEREST_AREAS FILLER,
PREFIX "UPPERPREFIX)",
INITIALS "UPPERINITIALS)",
SURNAME "UPPERSURNAME)",
SUFFIX "UPPERSUFFIX)",
ADDRESS_LINE_1 "UPPERADDRESS_LINE_1)",
ADDRESS_LINE_2 "UPPERADDRESS_LINE_2)",
ADDRESS_LINE_3 "UPPERADDRESS_LINE_3)",
TOWN "UPPERTOWN)",
COUNTY "UPPERCOUNTY)",
POSTCODE "UPPERPOSTCODE)",
DPS FILLER
)

Error:
SQL*Loader: Release 8.1.7.2.1 - Production on Tue Nov 13 17:28:56 2001

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL*Loader-350: Syntax error at line 10.
Expecting "(", found "or".
into table t_mailing_preference WHEN ACTION="I" or ACTION="R" or ACT
^
Regards,
Graham

 

[carp]

Don't have my book handy and it's been awhile since I wrote a control file, but based on the error message, you might try sticking the WHEN clause after the column specs:

Load data
truncate
into table t_mailing_preference
fields terminated by "," optionally enclosed by '"'
(
ACTION "UPPERACTION)",
REQUEST_DATE "TO_DATEREQUEST_DATE,'DDMMYYYY')",
INTEREST_AREAS "CONCAT(UPPERNTEREST_AREAS),UPPER(04))",

NEW_INTEREST_AREAS FILLER,
PREFIX "UPPERPREFIX)",
INITIALS "UPPERNITIALS)",
SURNAME "UPPERSURNAME)",
SUFFIX "UPPERSUFFIX)",
ADDRESS_LINE_1 "UPPERADDRESS_LINE_1)",
ADDRESS_LINE_2 "UPPERADDRESS_LINE_2)",
ADDRESS_LINE_3 "UPPERADDRESS_LINE_3)",
TOWN "UPPERTOWN)",
COUNTY "UPPERCOUNTY)",
POSTCODE "UPPERPOSTCODE)",
DPS FILLER
)
WHEN ACTION="I" or ACTION="R" or ACTION="X"

 

[carp]

No - that can't be right either; it would have choked on WHEN instead of OR. Disregard previous post!

 

[carp]

Ahhhh - coffee's starting to kick in!

Try modifying your control file's WHEN clause:

WHEN (ACTION="I&quot or (ACTION="R&quot or (ACTION="X&quot

 

[GBall]

No, fraid not.
I noticed that ACTION is a reserved word and changed it to FUNC - no use there either !

Regards,
Graham

 

[GBall]

Well, I've solved it.
It appears that OR is not supported ???
I only wanted to drop the trailer record and changed it to:
When action <> ''

bizarre... Regards,
Graham

 

[karluk]

Believe it or not, it appears SQL*Loader can't handle ORs in the WHEN clause. Here is an excerpt from the Oracle utilities manual:

&quot;A WHEN clause can contain several comparisons provided each is preceded by AND. Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND.&quot;

I find it pretty silly to have such a strong restriction on selection logic. However, there should be a work around available in your case. I haven't tested it, but I think the clause would be

WHEN ACTION in (&quot;I&quot;, &quot;R&quot;, &quot;X&quot

 

[carp]

Or run SQL*Loader three times, changing the WHEN clause for each run. Yeah, THAT'S efficiency!

 

[JFlack]

We normally do what you want by loading a staging table with SQL*Loader, and using SQL or PL/SQL to get the data to its real destination.