Oracle Keywords

seanc217 · Post by **seanc217** » Mon May 01, 2006 12:18 pm

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.

ray.wurlod · Post by **ray.wurlod** » Mon May 01, 2006 2:21 pm

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?

seanc217 · Post by **seanc217** » Mon May 01, 2006 3:44 pm

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'

)

ray.wurlod · Post by **ray.wurlod** » Mon May 01, 2006 4:22 pm

"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.

seanc217 · Post by **seanc217** » Mon May 01, 2006 4:34 pm

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!!

chulett · Post by **chulett** » Mon May 01, 2006 6:31 pm

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.

ray.wurlod · Post by **ray.wurlod** » Mon May 01, 2006 8:10 pm

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.

chulett · Post by **chulett** » Mon May 01, 2006 8:22 pm

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.

seanc217 · Post by **seanc217** » Tue May 02, 2006 7:47 am

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

seanc217 · Post by **seanc217** » Wed May 03, 2006 7:53 am

Hi there,

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

chulett · Post by **chulett** » Wed May 03, 2006 8:02 am

Excellent! Thanks for posting the solution. :D