Question
Cognizant
US
Last activity: 7 Jun 2017 11:03 EDT
InCorrect Value for pxCreateDateTime for Oracle DB
Hello Team,
We are using oracle 12 version as DB for our pega environment, we identified that pxCreateDateTime is not holding milli seconds as the column type in DB is DATE(if the type is TIMESTAMP it holds full time stamp).
In our application, we have requirement to send complete work object created time stamp with milli seconds to 3rd party system along with assignmentID, but due this we are unable to send milli seconds part.
Can you please help by letting me know whether pega has any plan to update the column type for oracle DB in future. Mean time, can you also please help me by suggesting work around.
Thanks,
Rohith
Accepted Solution
Pegasystems Inc.
US
Pega does now support Oracle TIMESTAMP as of 7.1.8. I do not believe that there is any plans at the moment to explicitly convert all OOB DATE columns to TIMESTAMP on an Oracle database. If you need the milliseconds then you should be able to alter the column to be TIMESTAMP. You would then need to run the column population tool to repopulate the column from the blob for any existing rows.
Here are some articles that talk about support for Oracle TIMESTAMP column.
http://community.pega.com/node/139666
https://community.pega.com/support/support-articles/version-error-during-upgrade-6x-71x
Pegasystems Inc.
US
What version of Pega are you running on? Support for Oracle's timestamp column is available in 7.2.2 and maybe a few versions back.
Cognizant
US
Hi Celeste,
Our requirement is to send pxcreateDatetime with full time stamp i.e., included with milli seconds. we are fetching the results with report definition & it's getting the time stamp from independent columns instead of blob which not included with milli seconds part, if we use obj method we can get the milli seconds too as it will get from blob. I came to know from support team that, this is the issue only with Oracle DB & for other vendor DB's it will work as required(included with milli seconds).
Here, my question is, Is pega having any plan to update the Oracle DB columns to TIMESTAMP instead of DATE for future versions. ?
Thanks,
Rohith
Techmahindra
IN
Hello Rohith,
Unfortunately this is a limitation with the Oracle product. Decimal value is read correctly when retrieving from a BLOB and not directly.
However some works arounds are being provide to use as desired in some use cases
If you look at pxCreateDateTime from the blob instead of the exposed column, you can avoid the db-type-dependent millisecond behavior. One way to access pxCreateDateTime from the blob is by using obj-open-by-handle.
Another way is to use executeRDB and specify an asterisk for the indicator of which columns to read. Pega treats asterisk as special and when it sees it, it reads from the blob.
Scope for updating based on one single database behavior may not be considered.
Regards,
Shanthini Charles
Accepted Solution
Pegasystems Inc.
US
Pega does now support Oracle TIMESTAMP as of 7.1.8. I do not believe that there is any plans at the moment to explicitly convert all OOB DATE columns to TIMESTAMP on an Oracle database. If you need the milliseconds then you should be able to alter the column to be TIMESTAMP. You would then need to run the column population tool to repopulate the column from the blob for any existing rows.
Here are some articles that talk about support for Oracle TIMESTAMP column.
http://community.pega.com/node/139666
https://community.pega.com/support/support-articles/version-error-during-upgrade-6x-71x