Oracle 9i timestamp(6) feature and error ORA-01830

ketfos · Post by **ketfos** » Fri Jun 09, 2006 5:23 pm

Hi,
I am using DataStage 7.5 Server Edition with Oracle 9i.
I have source table A, column PostingDate defined as timestamp(6) e.g
2000-08-31-20.49.03.932430

I ahve target table B, column PostingDate1 defined as timestamp(6)

When I look in the target stage, I have defined it as column generated SQL and the Sql statement looks like
INSERT INTO TEMP_table (POSTINGATE1) VALUES (TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS'))

When I run the job, it gives OracLe error
ORA-01830: date format picture ends before converting entire input string

Thks
Ketfos

kris007 · Post by **kris007** » Fri Jun 09, 2006 5:40 pm

You need to trim the milliseconds(I think its not even milliseconds, it much less than that, but I shall say it as milliseconds) part if you have to load it into Oracle. That is the reason it giving you that error. If you want milliseconds as part of your output then use user-defined sql and then declare your timestamp as

Code: Select all

TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS:FF')

Else, If you don't need it, you can use FIELD function to trim of the trailing seconds part from your input.

IHTH

chulett · Post by **chulett** » Fri Jun 09, 2006 7:10 pm

You may need to use TO_TIMESTAMP instead of TO_DATE for that field type. Or not.

DSguru2B · Post by **DSguru2B** » Fri Jun 09, 2006 10:07 pm

Try to search for the error "ORA-01830" in google. It will return you a lot of posts, why the error is caused and how to fix it. Its the time part thats actually creating the problem. You are converting one way and the data base expects in a different way. Maybe HH instead of HH24.

chulett · Post by **chulett** » Fri Jun 09, 2006 10:41 pm

Nope, it's the milliseconds which the current mask does not include. Hence the message about running out of 'format picture' before running out of timestamp.

DSguru2B · Post by **DSguru2B** » Sat Jun 10, 2006 6:25 pm

Aye Aye Captain

sb_akarmarkar · Post by **sb_akarmarkar** » Mon Jun 12, 2006 2:39 am

Target data type must be timestamp ....
Use TO_TIMESTAMP(:1,'YYYY-MM-DD HH24:MI:SS:FF')

Thanks,
Anupam

prasanna2100 · Post by **prasanna2100** » Mon Jun 12, 2006 11:40 pm

how do u want it in the target ?
the input date with timestamp is in one formate and the target is in another form.
to_date(':1,YYYY-MM-DD HH24:MI:SS')
you have this millisecond in ur source .
so its better to use To_TIMESTAMP()

ketfos · Post by **ketfos** » Wed Jun 14, 2006 12:00 pm

Hi,
Thanks everybody for help.
I was able to insert the timestamp datatype in the target table using user defined sql suggested in the earlier posts correctly.

INSERT INTO TEMP_table(POSTING_DTS) VALUES (TO_timestamp(:1, 'YYYY-MM-DD HH24:MI:SS:FF'))

I can view the data in the target table correctly using sql command or sqlplus command or TOAD.

But when I view in the datastage job i.e in the target stage, it only shows

2000-08-31 20:49:03 although using TOAD it gives me complete value as

2000-08-31-20.49.03.932430

Why it is truncated when I view in datastage.
The target column type is timestamp length is 26.

Ketfos

kris007 · Post by **kris007** » Wed Jun 14, 2006 12:42 pm

What is the display tab set to..I guess default is 20.Change it to 26 and you will be fine.

IHTH

ketfos · Post by **ketfos** » Thu Jun 15, 2006 10:48 am

Hi,
The display is set to 26.
When I view in datastage it is trucnated as
2000-08-31 20:49:03

WhenI query the table, it shows full 26 char as
2000-08-31-20.49.03.932430

Thks

Ketfos

ray.wurlod · Post by **ray.wurlod** » Thu Jun 15, 2006 5:20 pm

Perhaps that's just a "feature" of View Data ?