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.


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(
at oracle.jdbc.driver.T4CTTIoer.processError(
at oracle.jdbc.driver.T4C8Oall.processError(
at oracle.jdbc.driver.T4CTTIfun.receive(
at oracle.jdbc.driver.T4CTTIfun.doRPC(
at oracle.jdbc.driver.T4C8Oall.doOALL(
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(
at oracle.jdbc.driver.OraclePreparedStatement.execute(
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(


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")%>)"

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

stmtTrg = ConSrc.createStatement()
readTrg = stmtTrg.executeUpdate(sql_text)


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


About Bhabani 86 Articles
Bhabani has 12 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.

1 Comment

  1. 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

Comments are closed.