Results 1 to 6 of 6

Thread: Error ORA-01861

  1. #1
    Join Date
    Oct 2003
    Posts
    14

    Unanswered: Error ORA-01861

    I have created a Procedure called INSERT_ITEMS. I initially had the error PLS-00201: Identifier must be declared. I therefore created three public synonyms to match the Tables and Procedure. I have am faced with the following error:

    ERROR at line 1:
    ORA-01861: literal does not match format string
    ORA-06512: at "MSIPPITT.INSERT_ITEMS", line 3

    The first lines of the procedure are as follows :

    as
    begin
    insert into MTL_SYSTEM_ITEMS_INTERFACE
    (LAST_UPDATE_DATE
    ,CREATION_DATE

    I can post the rest if necessary. Any pointers as to what could be the cause would be appreciated.

    Many thanks
    Mark

  2. #2
    Join Date
    Oct 2003
    Posts
    26

    Re: Error ORA-01861

    Hi,

    Posting the rest of the code would help

    -Arvind

  3. #3
    Join Date
    Oct 2003
    Posts
    14
    Here is the rest of the code: Hope someone could shed some light on this.

    as
    begin
    insert into MTL_SYSTEM_ITEMS_INTERFACE
    (LAST_UPDATE_DATE
    ,CREATION_DATE
    ,ENABLED_FLAG
    ,START_DATE_ACTIVE
    ,END_DATE_ACTIVE
    ,DESCRIPTION
    ,SEGMENT1
    ,PURCHASING_ITEM_FLAG
    ,SHIPPABLE_ITEM_FLAG
    ,CUSTOMER_ORDER_FLAG
    ,INTERNAL_ORDER_FLAG
    ,SERVICE_ITEM_FLAG
    ,INVENTORY_ITEM_FLAG
    ,INVENTORY_ASSET_FLAG
    ,PURCHASING_ENABLED_FLAG
    ,CUSTOMER_ORDER_ENABLED_FLAG
    ,INTERNAL_ORDER_ENABLED_FLAG
    ,SO_TRANSACTIONS_FLAG
    ,MTL_TRANSACTIONS_ENABLED_FLAG
    ,STOCK_ENABLED_FLAG
    ,BOM_ENABLED_FLAG
    ,BUILD_IN_WIP_FLAG
    ,CHECK_SHORTAGES_FLAG
    ,RETURNABLE_FLAG
    ,COLLATERAL_FLAG
    ,TAXABLE_FLAG
    ,LIST_PRICE_PER_UNIT
    ,UNIT_OF_ISSUE
    ,PLANNING_TIME_FENCE_DAYS
    ,SERIAL_NUMBER_CONTROL_CODE
    ,BACK_ORDERABLE_FLAG
    ,PICK_COMPONENTS_FLAG
    ,UNIT_WEIGHT
    ,WEIGHT_UOM_CODE
    ,PRIMARY_UOM_CODE
    ,ITEM_NUMBER
    ,MATERIAL_COST
    ,MATERIAL_OH_RATE
    ,PROCESS_FLAG
    ,ATP_COMPONENTS_FLAG
    ,ATP_FLAG
    ,PRIMARY_UNIT_OF_MEASURE
    ,MINIMUM_ORDER_QUANTITY
    ,FIXED_ORDER_QUANTITY
    ,FIXED_DAYS_SUPPLY)

    select

    LAST_UPDATE_DATE
    ,CREATION_DATE
    ,ENABLED_FLAG
    ,TO_DATE(START_DATE_ACTIVE)
    ,TO_DATE(END_DATE_ACTIVE)
    ,DESCRIPTION
    ,SEGMENT1
    ,PURCHASING_ITEM_FLAG
    ,SHIPPABLE_ITEM_FLAG
    ,CUSTOMER_ORDER_FLAG
    ,INTERNAL_ORDER_FLAG
    ,SERVICE_ITEM_FLAG
    ,INVENTORY_ITEM_FLAG
    ,INVENTORY_ASSET_FLAG
    ,PURCASING_ENABLED_FLAG
    ,CUSTOMER_ORDER_ENABLED_FLAG
    ,INTERNAL_ORDER_ENABLED_FLAG
    ,SO_TRANSACTIONS_FLAG
    ,MTL_TRANSACTIONS_ENABLED_FLAG
    ,STOCK_ENABLED_FLAG
    ,BOM_ENABLED_FLAG
    ,BUILD_IN_WIP_FLAG
    ,CHECK_SHORTAGES_FLAG
    ,RETURNABLE_FLAG
    ,COLLATERAL_FLAG
    ,TAXABLE_FLAG
    ,LIST_PRICE_PER_UNIT
    ,UNIT_OF_ISSUE
    ,PLANNING_TIME_FENCE_DAYS
    ,SERIAL_NUMBER_CONTROL_CODE
    ,BACK_ORDERABLE_FLAG
    ,PICK_COMPONENTS_FLAG
    ,UNTI_WEIGHT
    ,WEIGHT_UOM_CODE
    ,PRIMARY_UOM_CODE
    ,ITEM_NUMBER
    ,MATERIAL_COST
    ,MATERIAL_OH_RATE
    ,PROCESS_FLAG
    ,ATP_COMPONENTS_FLAG
    ,ATP_FLAG
    ,PRIMARY_UNIT_OF_MEASURE
    ,MINIMUM_ORDER_QUANTITY
    ,FIXED_ORDER_QUANTITY
    ,FIXED_DAYS_SUPPLY
    from XX_MTL_SYS_ITEMS_INTERFACE;

    end INSERT_ITEMS;

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Just a guess, but you might want to check out the TO_DATE conversion of columns

    START_DATE_ACTIVE
    END_DATE_ACTIVE

    It seems that those 2 columns are of character data-type in table XX_MTL_SYS_ITEMS_INTERFACE. You should try to explicitely state the format of your "character date".

    Example :

    INSERT INTO
    ...
    SELECT
    LAST_UPDATE_DATE
    ,CREATION_DATE
    ,ENABLED_FLAG
    ,TO_DATE(START_DATE_ACTIVE,'DD.MM.YYYY') -- just an example !!
    ,TO_DATE(END_DATE_ACTIVE,'DD.MM.YYYY')
    ,DESCRIPTION
    ...

    You should always specify the format explicitely. That way, your code can work indepently from the NLS settings of the current session.

  5. #5
    Join Date
    Oct 2003
    Posts
    14
    Hi, thanks for your help with this but I'm still struggling with the same error and here is the modified code. I would appreciate any help someone could shed on this. Thanks

    as
    begin
    insert into MTL_SYSTEM_ITEMS_INTERFACE
    (LAST_UPDATE_DATE
    ,CREATION_DATE
    ,ENABLED_FLAG
    ,START_DATE_ACTIVE
    ,END_DATE_ACTIVE
    ,DESCRIPTION
    ,SEGMENT1
    ,PURCHASING_ITEM_FLAG
    ,SHIPPABLE_ITEM_FLAG
    ,CUSTOMER_ORDER_FLAG
    ,INTERNAL_ORDER_FLAG
    ,SERVICE_ITEM_FLAG
    ,INVENTORY_ITEM_FLAG
    ,INVENTORY_ASSET_FLAG
    ,PURCHASING_ENABLED_FLAG
    ,CUSTOMER_ORDER_ENABLED_FLAG
    ,INTERNAL_ORDER_ENABLED_FLAG
    ,SO_TRANSACTIONS_FLAG
    ,MTL_TRANSACTIONS_ENABLED_FLAG
    ,STOCK_ENABLED_FLAG
    ,BOM_ENABLED_FLAG
    ,BUILD_IN_WIP_FLAG
    ,CHECK_SHORTAGES_FLAG
    ,RETURNABLE_FLAG
    ,COLLATERAL_FLAG
    ,TAXABLE_FLAG
    ,LIST_PRICE_PER_UNIT
    ,UNIT_OF_ISSUE
    ,PLANNING_TIME_FENCE_DAYS
    ,SERIAL_NUMBER_CONTROL_CODE
    ,BACK_ORDERABLE_FLAG
    ,PICK_COMPONENTS_FLAG
    ,UNIT_WEIGHT
    ,WEIGHT_UOM_CODE
    ,PRIMARY_UOM_CODE
    ,ITEM_NUMBER
    ,MATERIAL_COST
    ,MATERIAL_OH_RATE
    ,PROCESS_FLAG
    ,ATP_COMPONENTS_FLAG
    ,ATP_FLAG
    ,PRIMARY_UNIT_OF_MEASURE
    ,MINIMUM_ORDER_QUANTITY
    ,FIXED_ORDER_QUANTITY
    ,FIXED_DAYS_SUPPLY)

    select

    TO_DATE(LAST_UPDATE_DATE,'DD-MM-YYYY')
    ,TO_DATE(CREATION_DATE,'DD-MM-YYYY')
    ,ENABLED_FLAG
    ,TO_DATE(START_DATE_ACTIVE,'DD-MM-YYYY')
    ,TO_DATE(START_DATE_ACTIVE,'DD-MM-YYYY')
    ,DESCRIPTION
    ,SEGMENT1
    ,PURCHASING_ITEM_FLAG
    ,SHIPPABLE_ITEM_FLAG
    ,CUSTOMER_ORDER_FLAG
    ,INTERNAL_ORDER_FLAG
    ,SERVICE_ITEM_FLAG
    ,INVENTORY_ITEM_FLAG
    ,INVENTORY_ASSET_FLAG
    ,PURCASING_ENABLED_FLAG
    ,CUSTOMER_ORDER_ENABLED_FLAG
    ,INTERNAL_ORDER_ENABLED_FLAG
    ,SO_TRANSACTIONS_FLAG
    ,MTL_TRANSACTIONS_ENABLED_FLAG
    ,STOCK_ENABLED_FLAG
    ,BOM_ENABLED_FLAG
    ,BUILD_IN_WIP_FLAG
    ,CHECK_SHORTAGES_FLAG
    ,RETURNABLE_FLAG
    ,COLLATERAL_FLAG
    ,TAXABLE_FLAG
    ,LIST_PRICE_PER_UNIT
    ,UNIT_OF_ISSUE
    ,PLANNING_TIME_FENCE_DAYS
    ,SERIAL_NUMBER_CONTROL_CODE
    ,BACK_ORDERABLE_FLAG
    ,PICK_COMPONENTS_FLAG
    ,UNTI_WEIGHT
    ,WEIGHT_UOM_CODE
    ,PRIMARY_UOM_CODE
    ,ITEM_NUMBER
    ,MATERIAL_COST
    ,MATERIAL_OH_RATE
    ,PROCESS_FLAG
    ,ATP_COMPONENTS_FLAG
    ,ATP_FLAG
    ,PRIMARY_UNIT_OF_MEASURE
    ,MINIMUM_ORDER_QUANTITY
    ,FIXED_ORDER_QUANTITY
    ,FIXED_DAYS_SUPPLY
    from XX_MTL_SYS_ITEMS_INTERFACE;

    end INSERT_ITEMS;

  6. #6
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    OK. So it was not the date. Might be another (implicit) conversion of one data-type to another.

    Please post/attach :
    1. DESC MTL_SYSTEM_ITEMS_INTERFACE
    2. DESC XX_MTL_SYS_ITEMS_INTERFACE
    3. a few sample rows from XX_MTL_SYS_ITEMS_INTERFACE, preferably the row/rows that raise the error.

Posting Permissions

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