-
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
-
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....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Many thanks, that worked great
I should have thought of that doh!!!
Allie
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|