DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: Migrating Data from csv to staging table

  1. #1
    Join Date
    Mar 2008
    Posts
    45

    Migrating Data from csv to staging table

    In my staging table i have column as Effect_Date as Date its data type.
    I have one Flat file separated by '|' .And one of the column contains Date in the format '21-JAN-08 12:30:54'.When ever I m runninng CTL file to load data into Staging Table..it doesnt do so..Kindly suggest me should I need to declare some function in CTL For date or sumthing else.


    Himanshu

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I assume you are talking about sqlloader.

    Please copy/paste your code, control file and log.
    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.

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    Check this url and search for date mask .
    http://download.oracle.com/docs/cd/B...t.htm#i1008914

    Regards

    Raj

  4. #4
    Join Date
    Mar 2008
    Posts
    45

    SQloadr

    here is my code where i face problem

    LOAD DATA
    INFILE *
    APPEND INTO TABLE SIEBEL.STAGING_CONTRACT
    FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
    (
    ASSET_NUM ,
    PRODUCT_NAME ,
    STATUS ,
    BILL_TO ,
    SOLD_TO ,
    FULFILL_TO ,
    ORIGINAL_ORDER ,
    MODIFY_ORDER ,
    CANCEL_ORDER ,
    EFFECT_DATE ,
    EXPIRE_DATE ,
    NET_PRICE

    )


    BEGINDATA
    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

    so this 'EFFECT_DATE AND EXPIRE_DA* BOTH are not loading values to my Staging Table...
    Bcoz of this SQLLoadr is not working...
    My table Representation is :-
    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 SYSTIMESTAMP),
    EXPIRE_DATE SYSTIMESTAMP,
    NET_PRICE VARCHAR2(22)
    )

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Lets be sure your issue is related to the systimestamp columns.

    1- Please try to load your data taking out your systimestamp values, just load nothing (nulls) in there.

    2- If it works then we know for sure the problem is systimestamp related and consequently I would say formatting related. In this case please start by doing a "select systimestamp from dual;" and check what's the expected systimestamp format.

    In my case I can see "03/10/2008 2:34:15.881 PM"
    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. #6
    Join Date
    Mar 2008
    Posts
    45

    Systimestamp CheKd

    I did dat query ...'select systimest....fr..dual'

    In my case I m getting:-
    11-MAR-08 03.24.52.312664000 AM -07:00

  7. #7
    Join Date
    Mar 2008
    Posts
    45

    SQloadr

    I tried the step given by u.

    I emptied the column effect_date and the other date col. .So both the columns were null.
    Then I executed the CTL It sucessfully loaded the specific record into the
    staging table.
    Now plz tel me wat next shud I follow?

    -Himanshu

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Good, now you are certain the problem is caused by these offending dates.

    Would you mind in loading a single row with effect_date = '11-MAR-08 03.24.52.312664000 AM -07:00'... meaning, the format your database appears to be expecting?
    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.

  9. #9
    Join Date
    Mar 2008
    Posts
    45
    Pav...
    Could u tell me wat data type I shud use with this specific column?

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    systimestamp?
    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.

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