Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    34

    Unanswered: SQL Loader Error

    I have a control file

    UNRECOVERABLE
    load data
    CHARACTERSET UTF16


    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA_BUS.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA_HOME.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_CHN.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_UDM.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA.dat' "str '<EORD>'"

    BADFILE 'K:\DEV\SQL_LOADER\BAD\AWT20015_DMS_PHONE.bad'

    into table DWSSTG01.AWT20015_DMS_PHONE
    fields terminated by "<EOFD>"
    trailing nullcols
    (
    IMC_KEY_NO,
    SEQUENCE_NO,
    PHN_TYPE_CD,
    PRM_CNTRY_CD,
    PRM_CITY_CD,
    PRM_PHN,
    PRM_PHN_EXT,
    AFF_KEY_NO,
    INSERT_DT_KEY_NO,
    INTGRT_SRC_TRX_CD
    )

    I am getting error while executing that file


    SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jan 26 10:48:33 2010

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Control File: Control_Files\AWT20015_DMS_PHONE_STG.ctl
    Character Set UTF16 specified for all input.
    Using character length semantics.

    There are 5 data files:
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA_BUS.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_NA_BUS.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA_HOME.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_NA_HOME.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_CHN.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_CHN.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_UDM.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_UDM.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA.dat
    File processing option string: "str '<EORD>'"
    Bad File: K:\DEV\SQL_LOADER\BAD\AWT20015_DMS_PHONE.bad
    Discard File: none specified

    (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Continuation: none specified
    Path used: Direct

    Load is UNRECOVERABLE; invalidation redo is produced.

    Table DWSSTG01.AWT20015_DMS_PHONE, loaded from every logical record.
    Insert option in effect for this table: INSERT
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    IMC_KEY_NO FIRST * CHARACTER
    Terminator string : '<EOFD>'
    SEQUENCE_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PHN_TYPE_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_CNTRY_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_CITY_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_PHN NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_PHN_EXT NEXT * CHARACTER
    Terminator string : '<EOFD>'
    AFF_KEY_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'
    INSERT_DT_KEY_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'
    INTGRT_SRC_TRX_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'

    SQL*Loader-951: Error calling once/load initialization
    ORA-26010: Column ROW_ID_NO in table DWSSTG01.AWT20015_DMS_PHONE is NOT NULL and is not being loaded

    I searched in internet for this error but couldnt help me
    Can anyone plese help me out

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    26010, 00000, "Column %s in table %s is NOT NULL and is not being loaded\n"
    // *Cause:   A column which is NOT NULL in the database is not being loaded
    //           and will cause every row to be rejected.
    // *Action:  Load the column by specifying the NOT NULL column in the
    //           INTO TABLE clause in the SQL*Loader control file.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2009
    Posts
    34
    But i am not getting that column from the source.
    i am using SSIS to get that source file populated, but that column ROW_ID_NO is not present in the source file.
    How can i do that

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Use a constant or sequence number

    Quote Originally Posted by rahulsony111 View Post
    But i am not getting that column from the source.
    i am using SSIS to get that source file populated, but that column ROW_ID_NO is not present in the source file.
    How can i do that
    Assign a constant value (or even a sequence number):
    Code:
    LOAD DATA
    -- Etc ...
    AFF_KEY_NO,
    INSERT_DT_KEY_NO,
    INTGRT_SRC_TRX_CD,
    ROW_ID_NO CONSTANT 'N/A'
    )
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2009
    Posts
    34
    row_id_no is an identity key, i cannot keep it as a constant
    however now i am getting different error
    i changed my control file a bit
    this is my control file

    load data
    CHARACTERSET UTF16


    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA_BUS.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA_HOME.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_CHN.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_UDM.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA.dat' "str '<EORD>'"

    BADFILE 'K:\DEV\SQL_LOADER\BAD\AWT20015_DMS_PHONE.bad'

    into table DWSSTG01.AWT20015_DMS_PHONE
    fields terminated by '<EOFD>'
    trailing nullcols
    (
    imc_key_no,
    phn_type_cd,
    prm_cntry_cd,
    prm_city_cd,
    prm_phn,
    prm_phn_ext,
    aff_key_no,
    insert_dt_key_no,
    INTGRT_SRC_TRX_CD,
    sequence_no
    )
    and the log file is


    SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jan 26 13:35:25 2010

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Control File: Control_Files\AWT20015_DMS_PHONE_STG.ctl
    Character Set UTF16 specified for all input.
    Using character length semantics.

    There are 5 data files:
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA_BUS.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_NA_BUS.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA_HOME.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_NA_HOME.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_CHN.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_CHN.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_UDM.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_UDM.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA.dat
    File processing option string: "str '<EORD>'"
    Bad File: K:\DEV\SQL_LOADER\BAD\AWT20015_DMS_PHONE.bad
    Discard File: none specified

    (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Bind array: 64 rows, maximum of 256000 bytes
    Continuation: none specified
    Path used: Conventional

    Table DWSSTG01.AWT20015_DMS_PHONE, loaded from every logical record.
    Insert option in effect for this table: INSERT
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    IMC_KEY_NO FIRST * CHARACTER
    Terminator string : '<EOFD>'
    PHN_TYPE_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_CNTRY_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_CITY_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_PHN NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_PHN_EXT NEXT * CHARACTER
    Terminator string : '<EOFD>'
    AFF_KEY_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'
    INSERT_DT_KEY_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'
    INTGRT_SRC_TRX_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    SEQUENCE_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'


    Data File K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA_BUS.dat -

    Record 1: Rejected - Error on table DWSSTG01.AWT20015_DMS_PHONE, column IMC_KEY_NO.
    Field in data file exceeds maximum length
    SQL*Loader-510: Physical record in data file (K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA_HOME.dat) is longer than the maximum(1048576)
    SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
    Specify SKIP=1 when continuing the load.

    Table DWSSTG01.AWT20015_DMS_PHONE:
    0 Rows successfully loaded.
    1 Row not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.


    Space allocated for bind array: 165120 bytes(64 rows)
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 1
    Total logical records rejected: 1
    Total logical records discarded: 0

    Run began on Tue Jan 26 13:35:25 2010
    Run ended on Tue Jan 26 13:35:26 2010

    Elapsed time was: 00:00:00.72
    CPU time was: 00:00:00.05

    please help me
    its urgent

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Use sequence.

    Then use a sequence number:
    Code:
    LOAD DATA
    -- Etc ...
    AFF_KEY_NO,
    INSERT_DT_KEY_NO,
    INTGRT_SRC_TRX_CD,
    ROW_ID_NO SEQUENCE(MAX,1)
    )
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Nov 2009
    Posts
    34
    I changed as you said

    UNRECOVERABLE
    load data
    CHARACTERSET UTF16


    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA_BUS.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA_HOME.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_CHN.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_UDM.dat' "str '<EORD>'"
    infile 'K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA.dat' "str '<EORD>'"

    BADFILE 'K:\DEV\SQL_LOADER\BAD\AWT20015_DMS_PHONE.bad'

    into table DWSSTG01.AWT20015_DMS_PHONE
    fields terminated by '<EOFD>'
    trailing nullcols
    (
    imc_key_no,
    phn_type_cd,
    prm_cntry_cd,
    prm_city_cd,
    prm_phn,
    prm_phn_ext,
    aff_key_no,
    insert_dt_key_no,
    INTGRT_SRC_TRX_CD,
    sequence_no,
    ROW_ID_NO SEQUENCE(MAX,1)
    )

    Even though i got error


    SQL*Loader: Release 10.2.0.1.0 - Production on Tue Jan 26 13:51:22 2010

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Control File: Control_Files\AWT20015_DMS_PHONE_STG.ctl
    Character Set UTF16 specified for all input.
    Using character length semantics.

    There are 5 data files:
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA_BUS.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_NA_BUS.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA_HOME.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_NA_HOME.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_CHN.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_CHN.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_UDM.dat
    File processing option string: "str '<EORD>'"
    Bad File: Control_Files\AWT20015_DMS_PHONE_STAGE_UDM.bad
    Discard File: none specified

    (Allow all discards)
    Data File: K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA.dat
    File processing option string: "str '<EORD>'"
    Bad File: K:\DEV\SQL_LOADER\BAD\AWT20015_DMS_PHONE.bad
    Discard File: none specified

    (Allow all discards)

    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Continuation: none specified
    Path used: Direct

    Load is UNRECOVERABLE; invalidation redo is produced.

    Table DWSSTG01.AWT20015_DMS_PHONE, loaded from every logical record.
    Insert option in effect for this table: INSERT
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    IMC_KEY_NO FIRST * CHARACTER
    Terminator string : '<EOFD>'
    PHN_TYPE_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_CNTRY_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_CITY_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_PHN NEXT * CHARACTER
    Terminator string : '<EOFD>'
    PRM_PHN_EXT NEXT * CHARACTER
    Terminator string : '<EOFD>'
    AFF_KEY_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'
    INSERT_DT_KEY_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'
    INTGRT_SRC_TRX_CD NEXT * CHARACTER
    Terminator string : '<EOFD>'
    SEQUENCE_NO NEXT * CHARACTER
    Terminator string : '<EOFD>'
    ROW_ID_NO SEQUENCE (MAX, 1)


    Data File K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_ST AGE_NA_BUS.dat -

    Record 1: Rejected - Error on table DWSSTG01.AWT20015_DMS_PHONE, column IMC_KEY_NO.
    Field in data file exceeds maximum length
    SQL*Loader-510: Physical record in data file (K:\DEV\SQL_LOADER\data_files\AWT20015_DMS_PHONE_S TAGE_NA_HOME.dat) is longer than the maximum(1048576)
    SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

    Table DWSSTG01.AWT20015_DMS_PHONE:
    0 Rows successfully loaded.
    1 Row not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.

    Bind array size not used in direct path.
    Column array rows : 5000
    Stream buffer bytes: 256000
    Read buffer bytes: 1048576

    Total logical records skipped: 0
    Total logical records read: 1
    Total logical records rejected: 1
    Total logical records discarded: 0
    Total stream buffers loaded by SQL*Loader main thread: 0
    Total stream buffers loaded by SQL*Loader load thread: 0

    Run began on Tue Jan 26 13:51:22 2010
    Run ended on Tue Jan 26 13:51:23 2010

    Elapsed time was: 00:00:01.08
    CPU time was: 00:00:00.06

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Typo or what?

    You did not have this in your first controlfile:

    Code:
    --- etc ---
    INTGRT_SRC_TRX_CD,
    sequence_no, ?????
    ROW_ID_NO SEQUENCE(MAX,1)
    )
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Nov 2009
    Posts
    34
    I am sorry
    there is nothing wrong in code
    i made a mistake in SSIS designer

    Thnak you very much for helping

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •