DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 30

Thread: Migrating Data from csv to staging table

  1. #11
    Join Date
    Mar 2008
    Posts
    45

    sqllader

    Sir
    I already tried this data type..however oracle doesnt recgnise it....

  2. #12
    Join Date
    Mar 2008
    Posts
    45
    IF I USE COLUMN WITH DATATYPE SYSTIMESTAMP...IT DOESNT WORK....
    THEN WHAT i DID..
    I DECLARED THE DATA TYPE OF COLUMN EFFECT_DATE AS VARCHAR2(100)
    AND THEN WHILE THERE WERE NO ROWS IMPORTED ..i FIRST OF ALL
    UPDATED THE TABLE SETTING THIS COLUMN TO "TO_SYSTIMESTAMP"..

    IT SEEMED IT WORTKED AND THEN DATA LOADED SUCESSFULLY...BUT AFTER THIS i M RUNNING PROCEDURE TO LOAD ALL RECORDS IN2 ANOTHR TABLE THERE ITS NOT WORKING..ITS SAYING

    "ORA-01830 -date format picture ends before converting entire input string"

  3. #13
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Would you consider something quick and dirty to get rid of the issue?

    Years ago we bumped into some data conversion issue involving sqlloader and this is what we did to get rid of the issue in a quick and dirty way.

    Imagine your issue is your start_date column which at the end of the day has to be a timestamp data type one.

    1- Define two columns in your table
    start_date timestamp null,
    start_date_x varchar2(100) null

    2- Load your data into start_date_x and nulls into start_date

    3- After your sqlloader process loads your table -putting your troublemaker date into start_date_x and leaving start_date as Null run a mass update -no where clause on the table setting start_date = to_timestamp(start_date_x)

    Hope this helps.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #14
    Join Date
    Mar 2008
    Posts
    45

    Deppressd

    Hey first of all...
    i m highly fortunate that you continoulsy helpin me on this issue.
    Now I hav tried all the options evn the last one that u provided.
    its givin me new error
    ORA-01849: hour must be between 1 and 12
    so plz help me out I m struck with this bloddy stuff.....

  5. #15
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    not sure when are you getting ORA-01849, is it during update statement after loading data thru sqlloader?

    If yes... please post your update statement alongside with a five rows sample of the content of your table.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #16
    Join Date
    Mar 2008
    Posts
    45

    loader

    Here is my new table with new columns:-
    CREATE TABLE SIEBEL.STAGING_CONTRACT
    (ASSET_NUM VARCHAR2(100),
    PRODUCT_NAME VARCHAR2(100),
    STATUS VARCHAR2(30),
    BILL_TO VARCHAR2(50),
    SOLD_TO VARCHAR2(50),
    FULFILL_TO VARCHAR2(50),
    ORIGINAL_ORDER VARCHAR2(30),
    MODIFY_ORDER VARCHAR2(30),
    CANCEL_ORDER VARCHAR2(30),
    EFFECT_DATE TIMESTAMP NULL ,
    START_DATE VARCHAR2(100),------added new column
    END_DATE VARCHAR2(100), -------added new column
    EXPIRE_DATE TIMESTAMP NULL ,
    NET_PRICE VARCHAR2(22)
    )

    1.sucesflly loaded data thru CTL
    3. update staging_contract
    set EFFECT_DATE =to_timestamp(START_DATE)

    THEN IT GAVE ME THAT ERROR

  7. #17
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    ..and WHERE ARE THE FIVE ROWS I've asked you to show us? We have to see how your start_date and end_date looks like.

    You are missing the format in your to_timestamp() syntax, you cannot just specify the source with no format and pray for it to work.

    1- Look at the data.
    2- Look at the syntax... http://www.itk.ilstu.edu/docs/oracle...nctions178.htm
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #18
    Join Date
    Mar 2008
    Posts
    45
    Here are 5 records.They may appear haphazardly coz I jus took export frm SQLDevloper...

    "ASSET_NUM" "PRODUCT_NAME" "STATUS" "BILL_TO" "SOLD_TO" "FULFILL_TO" "ORIGINAL_ORDER" "MODIFY_ORDER" "CANCEL_ORDER" "EFFECT_DATE" "START_DATE" "END_DATE" "EXPIRE_DATE" "NET_PRICE"
    "R-1" "Agent Bronze XL" "Inactive" "1000" "1000" "1000" "R-1" "" "" "" "01-Jul-96 18:25:00:000" "01-Aug-98 18:25:00:000" "" "99.00"
    "R-2" "Agent Bronze XL" "Expired" "1001" "1001" "1001" "R-2" "" "" "" "01-Jul-96 18:25:00:000" "01-Jul-97 18:25:00:000" "" "99.00"
    "R-3" "Agent Bronze XL" "Expired" "1002" "1002" "1002" "R-3" "" "" "" "01-Jul-96 18:25:00:000" "22-May-01 00:00:00:000" "" "99.00"
    "R-4" "Agent Bronze XL" "Inactive" "1003" "1003" "1003" "R-4" "" "" "" "01-Jul-96 18:25:00:000" "23-Jan-97 17:03:00:000" "" "99.00"
    "R-5" "Agent Bronze XL" "Inactive" "1004" "1004" "1004" "R-5" "" "" "" "01-Jul-96 18:25:00:000" "23-Jan-97 17:02:00:000" "" "99.00"

  9. #19
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    How about working in your to_timestamp() syntax?

    I'm not going to do it for you, follows and example of your type-and-pray syntax and also an example of a working syntax both using your actual data.

    Code:
    SQL> 
    SQL> select  to_timestamp('22-May-01 00:00:00:000') 
      2  from    dual
      3  ;
    select  to_timestamp('22-May-01 00:00:00:000')
                         *
    ERROR at line 1:
    ORA-01849: hour must be between 1 and 12
    
    
    SQL> 
    SQL> 
    SQL> 
    SQL> select  to_timestamp(substr('22-May-01 00:00:00:000',1,18),'dd-mon-yy hh24:mi:ss')
      2  from    dual
      3  ;
    
    TO_TIMESTAMP(SUBSTR('22-MAY-0100:00:00:000',1,18),'DD-MON-YYHH24:MI:SS')
    ---------------------------------------------------------------------------
    22-MAY-01 12.00.00 AM
    
    SQL>
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #20
    Join Date
    Mar 2008
    Posts
    45

    sqlloader

    ----I am sorry i forgot to tell u while updating table i used that syntax too
    to_timestamp(new_date,'dd-mon-yy hh24:mi:ss.ff')..after this it gave me that specific error


    Himanshu

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