Date format ERROR while inserting package data into Oracle BAM database

Hi All,

We are getting Date format ERROR while inserting package data into Oracle BAM database. Data type for custom and system (r_creation_date, r_modify_date) attributes in BAM database is TIMESTAMP.

We can modify date format for our custom fields but how we can change date format for system fields.

Following query is used by BAM job to insert data in sdt table:

INSERT INTO EB_MTSAATPROCESSDA (ATTR7,ATTR10,ATTR9,ATTR14,ATTR15,ATTR22,ATTR12,ATTR20,ATTR21,ATTR0,ATTR17,ATTR4,ATTR8,ATTR6,ATTR5,ATTR23,ATTR11,ATTR13,ATTR2,ATTR1,ATTR19,ATTR16,ATTR18,MTSAATPROCESSDAID,INSTANCEID,ACTIVITY_ID,INSTANCENAME ,SEQ_ID) SELECT null,'umesh_test','surender_test','EMC',null,0,to_date('25/5/2011 12:00:00 AM', 'mm/dd/yyyy hh12:mi:ss am'),to_date('25/5/2011 12:00:00 AM', 'mm/dd/yyyy hh12:mi:ss am'),null,null,'4b03944780f6ce80',to_date('14/5/2011 8:22:30 PM', 'mm/dd/yyyy hh12:mi:ss am'),0,'surender_test',to_date('14/5/2011 8:23:10 PM', 'mm/dd/yyyy hh12:mi:ss am'),'0803944780f95f8b',to_date('14/5/2011 8:22:24 PM', 'mm/dd/yyyy hh12:mi:ss am'),'In Progress',null,null,'test','4d03944780005501','0903944780f95f0d','451a1572-b597-4f8f-bc8b-e5abb560bd5f','4d03944780005501','4a0394478000bd2f',' mts_aat_process_data', EB_MTSAATPROCESSDASEQ_ID.NEXTVAL FROM BAM_DUMMY WHERE NOT EXISTS (SELECT 1 FROM EB_MTSAATPROCESSDA WHERE INSTANCEID = '4d03944780005501' AND ACTIVITY_ID = '4a0394478000bd2f' AND INSTANCENAME = 'mts_aat_process_data')

This gives ORACLE-01843 error.

Please let us know if anyone has faced the same issue.

Thanks & Regards

Surender

Find more posts tagged with

Comments

DimaL

Hi,

The situation you are describing is a bit confusing:

When package is logged to dm_audittrail, the date fields are loged in a date_format that is defined in dfc.properties file or in dfc.session

When bam extracts the package, it tries to extract the format in several ways, and if it fails, it throws error.

SDT is logged in a constant format, hence when BAM extracts it, it parses this in the same way always.

It might be that you are using old versions and this logic was not applied to your installation.

Can you please clarify:

1. Is it SDT or Package that you are using?

2. Can you please send us the "create" statement for this EB_MTSAATPROCESSDA type?

3. What version of BAM and CS are you using?

elbergaliza

Process Engine saves some information in temporary XML files in content server. BAM pipe reads Audit information from Content Server AND from this temporary xml files. Try this:

1-stop BAM server

2-Clean your audit information from content server

3-Put this line in dfc.properties for your CONTENT SERVER, nor in BAM application:

dfc.date_format=MM/dd/yyyy HH:mm:ss

4-Start content server and Bam Server.

Let me know if it work.

DimaL

Hi,

Did it work out for you?

surender.mechu@daffodilsw.com

Hi DimaL

It's working fine.I have change date format in dfc.properties and it's working.