SQL*Loader-350
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: SQL*Loader-350

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  2. #2
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Hi, does anyone have any ideas regarding this problem.

    Many thanks

    Alison

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    put

    month integer external NULLIF(month=BLANKS)


  5. #5
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  6. #6
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Sorry, but I forgot

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

    Regards

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  8. #8
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  10. #10
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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
  •  



Click Here to Expand Forum to Full Width