DSXchange

Hi there,

I have a table I am loading with a column named year. However the word year is a keyword in Oracle so I get the following error:

Oracle_Time,0: SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 1 14:06:52 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL*Loader-350: Syntax error at line 7.
Expecting positive integer or column name, found keyword year.
YEAR POSITION(21:24) INTEGER NULLIF YEAR = X'00000080',
^
Log file contents:


Thanks for any help.

Look at the sqlldr control script, particularly the line immediately ahead of YEAR. Is it complete? Or is there a number missing from the end of it?

Hi Ray,

Here is the control file...

OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES)

LOAD DATA INFILE '\\.\pipe\ora.324.777000d.fifo.1' "FIX 53"
APPEND INTO TABLE time
(
TIME_KEY POSITION(1:20) DECIMAL(38,0) NULLIF TIME_KEY = X'0000000000000000000000000000000000000000',
YEAR POSITION(21:24) INTEGER NULLIF YEAR = X'00000080',
MONTH POSITION(25:28) INTEGER NULLIF MONTH = X'00000080',
WEEK_OF_YEAR POSITION(29:32) INTEGER NULLIF WEEK_OF_YEAR = X'00000080',
WEEK_OF_MONTH POSITION(33:36) INTEGER NULLIF WEEK_OF_MONTH = X'00000080',
DAY_OF_YEAR POSITION(37:40) INTEGER NULLIF DAY_OF_YEAR = X'00000080',
DAY_OF_MONTH POSITION(41:44) INTEGER NULLIF DAY_OF_MONTH = X'00000080',
QUARTER POSITION(45:48) INTEGER NULLIF QUARTER = X'00000080',
DAY_OF_WEEK POSITION(49:52) INTEGER NULLIF DAY_OF_WEEK = X'00000080'

)

"keyword YEAR" suggests that YEAR might be a reserved word. Apart from that the load script looks OK to me; you might edit the post and disable smilies so that 8) is not converted into the "cool" smiley.

Yes. Year is a keyword. That is the problem. Once I changed the column names it works fine, but what if I want the column names to be year and month?

I submitted this as a case to IBM.

Thanks!!

Use quotes around any reserved words:

"YEAR" POSITION(21:24) INTEGER NULLIF "YEAR" = X'00000080',

Don't recall if it wants single or double quotes, but one of the two should fix your problem.

Explicitly double quotes. This turns them into "quoted identifiers". You also need quoted identifiers if they are crazy enough to require illegal characters (such as space) or case sensitive identifier names.

That's it!

I've not had to do it, not having run into cwazy peoples who create case sensitive object names in Oracle, or ones with spaces in them, but when you do you need to wrap them in double-quotes.

I know that you can use "" to surround keywords, but DS generates the control file right?

So DS should be doing this. I have submitted this as a case to IBM.

Thanks

Hi there,

There is an environment variable for this called APT_ORACLE_LOAD_DELIMITED set it to 1 and eveything works OK.

Excellent! Thanks for posting the solution. :D