Oracle Keywords
Moderators: chulett, rschirm, roy
Oracle Keywords
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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'
)
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'
)
Last edited by seanc217 on Mon May 01, 2006 4:33 pm, edited 1 time in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.