MQSeries.net :: View topic

mquser1 · Posted: Wed Feb 02, 2005 4:42 am Post subject:

Hi all,

We have a requirement where we are supposed to insert each message, along with the current timestamp into an Oracle table. Our first message flow essentially does the insertion. We have a second message flow that is supposed to select all the messages that are younger than a particular timestamp, for further processing.

All this was working fine if we used a db2 table.

Msgflow1 does:
=============================
INSERT INTO Database.table1(orderNum, insertTime, rec010, rec050) VALUES
-- (OutputRef.DefaultToSTONumber, current_timestamp, Record10, Record50);
=============================

and Msgflow2 does
=============================
SET Environment.Variables.TMP.OrderNumArray[] = PASSTHRU('SELECT DISTINCT ordernum FROM table1 WHERE inserttime < ?', ctime);
=============================

However this does not work if we create the same table in Oracle.

Essentially it looks like Oracle timestamp object is different from that of the Broker. I started trying several things like converting the broker timestamp to orracle timestamp using the to_date function in oracle. With this I was able to do the insertion ok. However it bombs out when i try to select the rows from the table. It appears like Broker is unable to understand the oracle timestamp column. I keep getting an exception that says:

----------------------------------
Database exception: Expected %s got %s
-----------------------------------

Has anyone run into this situation before? Any ideas on how to proceed further on this?

Thank you very much and warm regards.