Oracle timestamp in Informatica

To extract values from an oracle table column of timestamp datatype, you need to convert it to a character datatype since the Oracle Timestamp datatype is not currently supported by PowerCenter. To extract the value do the following.

  • Use SQL override and function to_char, provided by oracle, to convert the values of a field to character type before bringing it into PowerCenter mapping. For example,SQL> create table tst1 (tmstmp timestamp);Table created.

    SQL> insert into tst1 values (current_timestamp);

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQL> /

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select * from tst1;

    TMSTMP
    ————————————————-
    10-SEP-07 02.44.14.807930 PM
    10-SEP-07 02.44.16.700373 PM
    10-SEP-07 02.44.17.357597 PM
    10-SEP-07 02.44.18.163876 PM
    10-SEP-07 02.44.18.685926 PM

    SQL> select tmstmp, to_char(tmstmp,’YYYY-MM-DD HH:MM:SS.FF6′) as CHAR_TMSTMP from tst1;

    TMSTMP CHAR_TMSTMP
    —————————- ————————–
    10-SEP-07 02.44.14.807930 PM 2007-09-10 02:09:14.807930
    10-SEP-07 02.44.16.700373 PM 2007-09-10 02:09:16.700373
    10-SEP-07 02.44.17.357597 PM 2007-09-10 02:09:17.357597
    10-SEP-07 02.44.18.163876 PM 2007-09-10 02:09:18.163876
    10-SEP-07 02.44.18.685926 PM 2007-09-10 02:09:18.685926

    The resultant column CHAR_TMSTMP in the output of the query is now ready to be extracted in a mapping.

  • IMPORTANT thing to remember here is to make sure that the column for the timestamp value in the mapping, is defined as String(26).

    • Hope this helps…
    Kirtan

    September 10, 2007 at 7:41 pm by Administrator | Category: Informatica, Oracle |

    Leave a Reply