Capture ODI Error Message using Jython Variable

Capture ODI Error Message using Jython Variable:

Hi Friends,

Today we will discuss on capturing  multi-line error message from operator. Most of the time, you will be thinking to use substring function and putting that in a refresh variable. Does it really work for you??? May be (10%), may not be (90%).

 

If you fall in to the 90% category, then you are on right place ­čÖé . It is no more a challenging task now.

 

Root cause: Length limit, Multiple lines (/n character), Single quotes etc.

 

In java you cannot find any data type that can handle multiple lines. You have to split the strings and then concatenate it and finally store it into a string variable which is really irritating.

However if you will go for Python, you will get the opportunity to work with multiline strings. Below, I am trying to demonstrate a small example which will capture multiline strings and insert it into oracle table.

To capture the error message, you can use odi substitution api.  The error message will be replaced during run time. Create a procedure with Jython Jechnology. On Command on Source, select the relational schema of auditing tables. On Command on Target, use below codes.

Download procedure
Keep in mind that you have one interface prior to this procedure. Otherwise you will see errors like
No previous Step,Exception getPrevStepLog(“ERROR_COUNT”) : No previous Step)”\n’))┬á

I am creating a table to capture the error messages. If you want to capture more than 4000 characters go for CLOB.

CREATE TABLE SCOTT.ODI_JOB_EXECUTION_DETAILS
(
SESSION_NO NUMBER(30),
SCENARIO_NAME VARCHAR2(100 BYTE),
CONTEXT_NAME VARCHAR2(20 BYTE),
ERR_MESSAGE VARCHAR2(4000 BYTE),
INSERT_COUNT NUMBER(10),
ERROR_COUNT NUMBER(10)
)

Codes to insert the error message into above table.

import java.sql as sql
import java.lang as lang

text = """
ODI-1226: Step Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails after 1 attempt(s).
ODI-1240: Flow Staging_DS_to_WCDS_DS_PORTALUSER_COMPANY_INF fails while performing a Control operation. This flow loads target table CDS_PORTALUSER_COMPANY.
ODI-1228: Task CDS_PORTALUSER_COMPANY (Control) fails on the target ORACLE connection WCDS_DS.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "
SUB"."SOURCE_SYSTEM_ID": invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

"""
textsubstr=text[:10]

sql_text="insert into ODI_JOB_EXECUTION_DETAILS values(<%=snpRef.getSession("SESS_NO")%>,’#CURRDATE’,’<%=odiRef.getContext( "CTX_NAME" )%>’,’"+textsubstr+"’,<%=odiRef.getPrevStepLog("INSERT_COUNT")%>,<%=odiRef.getPrevStepLog("ERROR_COUNT")%>)"

try:
# Source Connection
ConSrc = snpRef.getJDBCConnection( "SRC" )

stmtTrg = ConSrc.createStatement()
readTrg = stmtTrg.executeUpdate(sql_text)
finally:
stmtTrg.close()

 

After execution you can see them inserted in to oracle table. Let me know if you face any issues with above codes.

Thanks!!

0

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

1 comments

Hi , I have imported the this procedure and alos create the table ODI_JOB_EXECUTION_DETAILS – but the procedure is aborting with the reason – “name stmtTrg is not defined” – Can you please guide me on this- Thanks

Leave a replay to Ram Cancel reply

required*

Are you a human? *