SQL*Loader-350

Hi there,

I am getting the following problem from an SQL Loader file.

This file ran correctly last year when the database was version 7.3.3, however is has since been upgraded to version 8i (running on NT)

SQL*Loader-350: Syntax error at line 14.
Expecting positive integer or column name, found keyword month.
month integer external NULLIF month=BLANKS,

The cmd file looks like:-

set ORACLE_SID=DRPL
f:\orant8i\bin\sqlldr userid=DRPL/DRPL control=f:\drpl\drplload\ctl\week_2003.ctl log=f:\drpl\drplload\log\week_2003.log

The ctl file is:-

OPTIONS ( DIRECT=TRUE )
LOAD DATA
INFILE "f:\drpl\drpldata\week2003.dat"
DISCARDFILE "f:\drpl\drplload\discard\week2003.dis"
DISCARDMAX 999
APPEND
INTO TABLE WEEK_CONVERSION
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
( year char,
start_date date(8) "YYYYMMDD",
day_number integer external,
week_number integer external,
year_week char,
month integer external NULLIF month=BLANKS,
quarter integer external NULLIF quarter=BLANKS,
last_friday_of_month char,
seq integer external)

Many thanks in advance

Allie

Hi there, me again,

I've just tested the loader script on both version 7.3.3 & version 8i databases.

It works perfectly on the v7.3.3 but still comes up with the same message on v8i.

I'm unsure why "month" should be considered as a reserve/keyword.

If I take out the "NULLIF month=BLANKS" but leave in "NULLIF quarter=BLANKS"
it works ???

Thanks

Alison

Hi, does anyone have any ideas regarding this problem.

Many thanks

Alison

put

month integer external NULLIF(month=BLANKS)

Hi there,

Thanks for the advice, however I am still getting the below error.

Any ideas?

"SQL*Loader-350: Syntax error at line 14.
Expecting positive integer or column name, found keyword month.
month integer external NULLIF (month=BLANKS)"

Cheers

Alison

Sorry, but I forgot

As a test I changed the column month to be month1 and the process worked.

Regards

Quote:

---

Originally posted by alison
I'm unsure why "month" should be considered as a reserve/keyword.

---

Becase it simply is a reserved word. Maybe it was not so in 7.3, but it definitely is in 8i. You can find all the reserved words by:

SELECT keyword FROM v$reserved_words;

If you want to insist in using MONTH as a column name then you must enclosed it in double quotes whenewer you reference it....

With regards to your last post I used the following command.

"month" integer external NULLIF "month" = BLANKS

The result was the following error

Error calling once/load initialization
ORA-02373: Error parsing insert statement for table WEEK_CONVERSION.
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: invalid column name

I also used

month integer external NULLIF "month" = BLANKS

SQL*Loader-403: Referenced column "month" not present in table WEEK_CONVERSION.

Thanks

Allie

I'm not sure this is the cause of your problems, but you could give it a try:

When you enclose column name in double quotes, not only that reserved words are not treated as the reserved words, but also the upper/lowercase of the column name is preserved! I assume your column MONTH is stored in upper case in the database dictionary, so you must use uppercase when you specify it in double quotes! So try:

"MONTH" integer external NULLIF "MONTH" = BLANKS

Many thanks, that worked great

I should have thought of that doh!!!


Allie