Wednesday, August 19, 2009

DAC Synchronize Tasks failing

MESSAGE:::Error while inserting a record!
EXCEPTION CLASS::: com.siebel.etl.gui.core.RecordManipulationException

com.siebel.analytics.etl.client.core.DACMessage.convertToRME(DACMessage.java:31)
com.siebel.analytics.etl.client.data.model.UpdatableDataTableModel.upsertNewRecord(UpdatableDataTableModel.java:141)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertTableList(InfaDacWriter.java:459)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertNodeTables(InfaDacWriter.java:397)
com.siebel.analytics.etl.infa.fileParsing.InfaDacWriter.insertNodeTables(InfaDacWriter.java:314)
com.siebel.analytics.etl.infa.fileParsing.TaskSync.sync(TaskSync.java:168)
com.siebel.analytics.etl.client.action.TaskSynchronizationAction.doOperation(TaskSynchronizationAction.java:123)
com.siebel.etl.gui.view.dialogs.WaitDialog.doOperation(WaitDialog.java:53)
com.siebel.etl.gui.view.dialogs.WaitDialog$WorkerThread.run(WaitDialog.java:85)
------------------------------------------------------------------------------------
We have a custom mapping that has been tuned to use DB Links and also commented the line that was checking Last Update Date in Oracle EBS tables. The commented line is as below.

-- AND (AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS') OR AP_INVOICES_ALL.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS'))

After this, DAC task is not synchronizing. It is throwing the error - "MESSAGE:::Error while inserting a record". After removing the line, it is just synchronizing fine.

It looks like DAC has problems avoiding the commented line in SQL Query. When I run DAC Client in interactive mode, it throws this error. So, DAC is treating MM/DD/YYYY as a table name for some reason. I can't think what the logic is but definitely wrong.

FROM W_ETL_TABLE A_W_ETL_TABLE
INNER JOIN W_ETL_OBJ_REF OBJ_REF ON OBJ_REF.OBJ_TYPE='W_ETL_TABLE'
AND OBJ_REF.SOFT_DEL_FLG='N'
AND OBJ_REF.APP_WID='PGBU_R12'
AND OBJ_REF.OBJ_WID=A_W_ETL_TABLE.ROW_WID
WHERE
((A_W_ETL_TABLE.NAME=''MM/DD/YYYY'
))
Aug 10, 2009 9:43:32 AM com.siebel.etl.engine.core.ETLUtils logException
SEVERE: MESSAGE:::ORA-00907: missing right parenthesis

EXCEPTION CLASS::: java.sql.SQLException

7 comments:

Anonymous said...

Not sure if this is still an issue for you - i got the exact same issue complete with the A_W_ETL_TABLE.NAME=''MM/DD/YYYY'

It turns out we left some commented code in the SQ in Informatica. This code remained

--WHERE MSC_FULL_PEGGING.LAST_UPDATE_DATE>TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS')

Changa Reddy said...

We no longer have this issue. It was due to the issue with commented lines in SQL in source qualifier.

prudhivi's blog said...

Hi,
i am trying to sysnchronize the task in DAC. Though it is showing that synchronization completed successfully, it is not showing the source and target tables. what is the problem??
can you tell me the cause.

Changa Reddy said...

Prudhvi,

You may be better of checking the SQL by changing the startclient.bat to run DAC on java instead of javaw. That may give you some pointers. You may have to make sure the mappings are valid. It basically runs SQL against Infa database to decode the SQL in SQ Qualifier and targets in traget transformations. Good Luck!

I did have this problem sometime and I basically addded manually as I didn't have time for all the debug. It didn't happen when I had some time. :-)

Abhijeet Barge said...

Hi Changa Reddy....

I'm facing same problem. Could You please explain me briefly what has to done.
i'm newbie in DAC.
please help

Abhijeet Barge said...

Hi Prudvi, Changa Reddy

I'm facing same issue in DAC. could please briefly elaborate what has to be done to overcome this issue.
I'm newbie in DAC.
please help
thanks

Abhijeet Barge said...

Hi Changa Reddy, Prudvi,

I'm facing same issue. could u please elaborate briefly what has to done to overcome this issue.
i'm newbie.
thanks