I have DataStage v6.0 and a table named TABLE1 with FIELD1 TIMESTAMP(6) with Oracle 9i. In Sqlplus, I can execute this query successfully:
insert into table1 (field1) values (TIMESTAMP '2003-09-22 18:50:26.000000');
commit;
However in DataStage, I can't simulate this in an ORAOCI9 stage.
Is there any solution for this?
I have tried:
- The type TIMESTAMP
INSERT INTO TABLE1 (FIELD1) VALUES (TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS')) -> this is not a TO_DATE data.
- The User-Defined SQL:
INSERT INTO TABLE1 (FIELD1) VALUES (TIMESTAMP :1) -> it brings me problems with the symbols '.
Try this user defined SQL;
insert into table () values (to_timestamp(:1, 'YYYY-MM-DD HH24:mi:ss.ff6').
Make sure the bind variable column (:1) data is in the desired format. You don't have to go all the way to microseconds. It can be any valid date up to ff6.
Sorry, I'm one paren short of a full load. It's the closing paren giving you the very informative missing comma error.
Try
insert into temp (field1) values(to_timestamp('2003-09-24 17:59:00.123456','YYYY-MM-DD HH24:mi:ss.ff6'));
Thanks for the tip. I didn't realized that the "missing comma" was indeed a "missing parenthesis". Never mind.
I added the parenthesis in the User Defined SQL and it works. However, instead of using the User Defined, there isn't a way of doing it with the option "Insert rows without clearing"?
I am not aware of any. The problem is that DataStage will not recognize the data type. I've got the same problem with CLOB. I have to invoke the to_char() everytime I deal with a CLOB field via DS. Another method I've seen is to make the datatype of the timestamp field a varchar2 since it appears you are putting a value into it. Is there some reason it must be timestamp? Also, you could define the field in Oracle as a timestamp and default it to systimestamp. Remove it from the generated SQL completely and let Oracle put the systimestamp into the field for you upon an insert.
