problem with dates loading in Oracle

paddu · Post by **paddu** » Wed Feb 28, 2007 5:46 pm

I have dates in the format 2/24/2007 1:18:19 PM coming from a Flatfile and to be loaded into oracle .

I get Ora-01843 error .

i tried using
OCONV(ICONV(src_out_C_CUST_MTCH.CREATE_DATE,"MTHS"),"MTHS"),the job did not abort but the result is blank in the date field.

tried using oconv(iconv(src_out_C_CUST_MTCH.CREATE_DATE,"DYMD[4,2,2]"),"D-YMD[4,2,2]") but result is blank

can anyone help me with this

thanks
paddu

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 5:52 pm

User To_Date() function before loading it into Oracle. You need to make sure the date format is compatible with Oracle, rather than Datastage to get rid of this oracle error.

paddu · Post by **paddu** » Wed Feb 28, 2007 5:55 pm

how can i apply To_date in Transformer ?

My design is sequential stage - transformer ---oracle

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 6:02 pm

Not in transformer, but in the SQL used in Oracle stage.

paddu · Post by **paddu** » Wed Feb 28, 2007 6:05 pm

i did not have to make any changes to Target oracle stage , the insert statement has To_date ()

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 6:11 pm

If that is the case, and if you are using Generated SQL, check the TO_DATE() function build automatically. And make sure the output of your job refer to that format. To test, you can write the data to an intermediate sequential file and check the out format.

paddu · Post by **paddu** » Wed Feb 28, 2007 6:25 pm

Kumar

I think i did not follow you.

My source data looks like 2/24/2007 1:18:19 PM . I am not able to load into oracle in Timestamp field. The value itself has AM or PM . i do not know how to convert this while loading in orcale .
the insert statement already has To_date .

If my source was Orcale , i would have done To_char(To_date()) in my SQl but my source is a Flat file

.

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 6:31 pm

So fine. Read the field as Char or varchar. Change the target to userdefined SQL, and modify your TO_DATE() function to fit this value.
TO_DATE(Input.File,'MM/DD/YYYY HH24:MI:SS')
If you dont want to touch the target for any chance (which is not resonable), convert the change the char field into required format (not as date but as char itself). Or past the SQL used in target stage.

chulett · Post by **chulett** » Wed Feb 28, 2007 6:32 pm

You need to split your value into its component pieces - date and time. Use IConv to convert both to internal format and then OConv to get them back in the format Oracle expects:

YYYY-MM-DD HH24:MI:SS

Both pieces are converted separately and then concatenated back together with a space between them.

There's absolutely no reason to use user-defined SQL just for this.

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 6:43 pm

Craig, I use to see some of your other posts as well where you insist not using User defined SQL. Will there be any performance improvement in using Bult in SQL or just for the sake of avoiding some generic errors?

paddu · Post by **paddu** » Wed Feb 28, 2007 6:58 pm

I am doing this

Left(src_out_C_CUST_MTCH.CREATE_DATE,10) stgvar1
Right(src_out_C_CUST_MTCH.CREATE_DATE,10) stgvar2

OCONV(ICONV(stgvar1,"DYMD[4,2,2]"),"D-YMD[4,2,2]") : " " : oconv(iconv(stgvar2,"MTS"),"MTS")

can you please tell me where i am wrong.

i still get ora-01843 as the error.

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 7:01 pm

You Iconv function need to be changed.
Iconv(stgvar1,"D/MDY[2,2,4]")

chulett · Post by **chulett** » Wed Feb 28, 2007 7:08 pm

kumar_s wrote:Craig, I use to see some of your other posts as well where you insist not using User defined SQL. Will there be any performance improvement in using Bult in SQL or just for the sake of avoiding some generic errors?

No, it's not about performance - it's about standards and maintenance. My stance on user-defined sql is firm: only use it when you have no other choice. Otherwise, let the stage generate the sql for you. Those who come after you will thank you. I use 'Column Generated' rather than the SQL Builder approach, btw. Can't stand the SQL Builder.

Generated sql ensures several things, amongst them:

1) All variables are properly bound
2) Columns selected match columns defined in the stage
3) Changes automatically get reflected in the sql

I've seen too many problems where someone defines columns A,B,C,D in the stage but select A,C,B,D in the SQL. The stage won't care - but your job will. I've also seen five columns selected with four defined in the stage (the last one is just lost) or the opposite where the last coumn is always null. The job doesn't catch the error and will happily screw things up for you.

I hear lots of excuses. It's too hard for me to read. It's easier if I just paste the query in from TOAD. Yada yada yada. Tough. Knock it off.

paddu · Post by **paddu** » Wed Feb 28, 2007 7:09 pm

kumar ,

i corrected ICONv function . date did load into Oracle but no timestamp attached.

Is my time function wrong too? The result i see in oracle is 2/24/2007

chulett · Post by **chulett** » Wed Feb 28, 2007 7:18 pm

How did you check the result? Just because you loaded it with a time doesn't mean you have to select the time when you select the date.

If you are not sure your transforms are correct, write them to a flat file along with the source field so you can check the results outside of Oracle. You can remove the link and file once you're confident in the result.