3 Replies Latest reply: Nov 28, 2012 5:25 PM by sb92075 RSS

    SQL loader problem

    User444780-OC
      Hi Gurus,

      Oracle Version : 11.2.0.3
      OS Version : Solaris.

      I am getting below error when trying to load the data .
      value used for ROWS parameter changed from 64 to 38
      Record 1: Rejected - Error on table SSP_FLOW.NCOG_ORDER_TEST, column APP_SERVER_TIME_STAMP_155.
      ORA-01830: date format picture ends before converting entire input string
      
      Record 2: Rejected - Error on table SSP_FLOW.NCOG_ORDER_TEST, column APP_SERVER_TIME_STAMP_155.
      ORA-01830: date format picture ends before converting entire input string
      Here is my control file.
      OPTIONS (SKIP=1)
      LOAD DATA
      INFILE '/dbmisc/NCOG/NcogFullOrderDataWestSIT_20121125.csv'
      BADFILE '/dbmisc/NCOG/Step03-NCOG_SampleFile1.bad'
      DISCARDFILE '/dbmisc/NCOG/Step03-NCOG_SampleFile1.dsc'
      INSERT
      INTO TABLE ssp_flow.NCOG_Order_test
      FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
      (MasterOrderNumberType_1 ,
      APP_SERVER_TIME_STAMP_155 "to_timestamp_tz (:APP_SERVER_TIME_STAMP_155,'MM/DD/YYYY HH24:MI:SS' )",
      IS_Processed )
      {code}
      
      Data in the CSV file.
      {code}
      MasterOrderNumberType_1     APP_SERVER_TIME_STAMP_155
      0G0075008     11/25/2012 14:26:04 EST
      0G0075049     11/25/2012 17:29:55 EST
      0G0074969     11/25/2012 11:22:16 EST
      0G0075023     11/25/2012 15:56:52 EST
      
      {code}
      
      table creation script.
      {code}
      CREATE TABLE ssp_flow.NCOG_Order_test (
      MasterOrderNumberType_1 varchar2(50),
      APP_SERVER_TIME_STAMP_155 date ,
      IS_Processed           varchar2(10) default 'N'
      );
      {code}
      
      Please help me how to solve my problem.
      
      Thanks & Regards,
      Venkata Poorna Prasad.S