Migrating Data from csv to staging table

[himanshu990]

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

[PAVB]

I assume you are talking about sqlloader.

Please copy/paste your code, control file and log.

[s.rajaram]

Check this url and search for date mask .
http://download.oracle.com/docs/cd/B...t.htm#i1008914

Regards

Raj

[himanshu990]

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)
)

[PAVB]

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"

[himanshu990]

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

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

[himanshu990]

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

[PAVB]

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?

[himanshu990]

Pav...
Could u tell me wat data type I shud use with this specific column?

[PAVB]

systimestamp?