This discussion is archived
3 Replies Latest reply: Nov 28, 2012 3:25 PM by sb92075 RSS

SQL loader problem

User444780-OC Newbie
Currently Being Moderated
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points