ODI-17517: Error during task interpretation

MaheshBAug 10 2016

Hi,

I am executing ODI interface and i am getting error.

I checked for physical schema and logical schema in context. Context have assigned both Physical & Logical schemas mappings. So issue is not with schemas. Detailed error as below.

"ODI-1217: Session SIL_PartyLocationDimension.WC_PARTY_LOCATION_D (5216520) fails with return code 7000.

Caused By: com.sunopsis.tools.core.exception.SnpsSimpleMessageException: ODI-17517: Error during task interpretation.

Task: 15

java.lang.Exception: BeanShell script error: Parse error at line 2, column 1. Encountered: } BSF info: Update existing rows at line: 0 column: columnNo

at com.sunopsis.dwg.codeinterpretor.SnpCodeInterpretor.transform(SnpCodeInterpretor.java:489)

at com.sunopsis.dwg.dbobj.SnpSessStep.createTaskLogs(SnpSessStep.java:737)

at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:465)

at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:376)

at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:309)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:301)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:865)

at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:137)

at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)

at java.lang.Thread.run(Thread.java:662)

Caused by: java.lang.Exception: BeanShell script error: Parse error at line 2, column 1. Encountered: } BSF info: Update existing rows at line: 0 column: columnNo

at com.sunopsis.dwg.codeinterpretor.SnpCodeInterpretor.transform(SnpCodeInterpretor.java:476)

... 11 more

Caused by: org.apache.bsf.BSFException: BeanShell script error: Parse error at line 2, column 1. Encountered: } BSF info: Update existing rows at line: 0 column: columnNo

at bsh.util.BeanShellBSFEngine.eval(Unknown Source)

at bsh.util.BeanShellBSFEngine.exec(Unknown Source)

at com.sunopsis.dwg.codeinterpretor.SnpCodeInterpretor.transform(SnpCodeInterpretor.java:471)

... 11 more

Text: BeanShell script error: Sourced file: inline evaluation of: ``out.print("<$ if (bypassFlow.equals(\"N\")) { $>\n/* DETECTION_STRATEGY = OUTER . . . '' Token Parsing Error: Lexical error at line 4, column 44. Encountered: "\n" (10), after : "\")<?}?>,": <at unknown location>

BSF info: Update existing rows at line: 0 column: columnNo

out.print("<$ if (bypassFlow.equals(\"N\")) { $>\n/* DETECTION_STRATEGY = OUTER */\nupdate\t#ETL_HINT_UPDATE\t<?=snpRef.getObjectName(\"L\", \"WC_PARTY_LOCATION_D\", \"DW_BIAPPS11G\", \"\", \"D\") ?> T\nset \t(\n\tT.GEO_WID,\n\tT.ST_ADDRESS1,\n\tT.ST_ADDRESS2,\n\tT.ST_ADDRESS3,\n\tT.ST_ADDRESS4,\n\tT.CITY,\n\tT.COUNTY,\n\tT.POSTAL_CODE,\n\tT.STATE_PROV_CODE,\n\tT.COUNTRY_CODE,\n\tT.C_CONTINENT_CODE,\n\tT.C_REGION_CODE,\n\tT.W_COUNTRY_CODE,\n\tT.C_COUNTRY_REGION_CODE,\n\tT.C_STATE_PROV_CODE,\n\tT.C_COUNTY_CODE,\n\tT.C_CITY_CODE,\n\tT.CREATED_BY_WID,\n\tT.CHANGED_BY_WID,\n\tT.CREATED_ON_DT,\n\tT.CHANGED_ON_DT,\n\tT.INTEGRATION_ID,\nT.ROW_WID,\n\tT.CUSTOMER_WID,\n\tT.SRC_EFF_FROM_DT,\n\tT.SRC_EFF_TO_DT,\n\tT.EFFECTIVE_FROM_DT,\n\tT.EFFECTIVE_TO_DT,\n\tT.W_INSERT_DT,\n\tT.W_UPDATE_DT,\n\tT.DATASOURCE_NUM_ID,\n\tT.ETL_PROC_WID\n\t) =\n\t\t(\n\t\tselect\t<?if(\"NUMBER\".contains(\"CHAR\")){?>SUBSTR(S.GEO_WID,0,10)<?} else {?>S.GEO_WID<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.ST_ADDRESS1,0,255)<?} else {?>S.ST_ADDRESS1<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.ST_ADDRESS2,0,255)<?} else {?>S.ST_ADDRESS2<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.ST_ADDRESS3,0,255)<?} else {?>S.ST_ADDRESS3<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.ST_ADDRESS4,0,255)<?} else {?>S.ST_ADDRESS4<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.CITY,0,120)<?} else {?>S.CITY<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.COUNTY,0,120)<?} else {?>S.COUNTY<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.POSTAL_CODE,0,120)<?} else {?>S.POSTAL_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.STATE_PROV_CODE,0,120)<?} else {?>S.STATE_PROV_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.COUNTRY_CODE,0,120)<?} else {?>S.COUNTRY_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.C_CONTINENT_CODE,0,120)<?} else {?>S.C_CONTINENT_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.C_REGION_CODE,0,120)<?} else {?>S.C_REGION_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.W_COUNTRY_CODE,0,120)<?} else {?>S.W_COUNTRY_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.C_COUNTRY_REGION_CODE,0,120)<?} else {?>S.C_COUNTRY_REGION_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.C_STATE_PROV_CODE,0,120)<?} else {?>S.C_STATE_PROV_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.C_COUNTY_CODE,0,120)<?} else {?>S.C_COUNTY_CODE<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.C_CITY_CODE,0,120)<?} else {?>S.C_CITY_CODE<?}?>,\n\t\t\t<?if(\"NUMBER\".contains(\"CHAR\")){?>SUBSTR(S.CREATED_BY_WID,0,10)<?} else {?>S.CREATED_BY_WID<?}?>,\n\t\t\t<?if(\"NUMBER\".contains(\"CHAR\")){?>SUBSTR(S.CHANGED_BY_WID,0,10)<?} else {?>S.CHANGED_BY_WID<?}?>,\n\t\t\t<?if(\"DATE\".contains(\"CHAR\")){?>SUBSTR(S.CREATED_ON_DT,0,7)<?} else {?>S.CREATED_ON_DT<?}?>,\n\t\t\t<?if(\"DATE\".contains(\"CHAR\")){?>SUBSTR(S.CHANGED_ON_DT,0,7)<?} else {?>S.CHANGED_ON_DT<?}?>,\n\t\t\t<?if(\"VARCHAR2\".contains(\"CHAR\")){?>SUBSTR(S.INTEGRATION_ID,0,80)<?} else {?>S.INTEGRATION_ID<?}?>,\n<?if(\"NUMBER\".contains(\"CHAR\")){?>SUBSTR(WC_PARTY_LOCATION_D_SEQ.") ;

out.print(odiRef.getObjectName("L","),0,10","D")) ;

out.print(".NEXTVAL<?} else {?>WC_PARTY_LOCATION_D_SEQ.") ;

out.print(odiRef.getObjectName("L",")<?}?>,

<?if("NUMBER".contains("CHAR")){?>SUBSTR(0,0,10)<?} else {?>0<?}?>,

<?if("DATE".contains("CHAR")){?>SUBSTR(TO_DATE(SUBSTR('#LOW_DATE',0,19),'YYYY-MM-DD HH24:MI:SS'),0,7)<?} else {?>TO_DATE(SUBSTR('#LOW_DATE',0,19),'YYYY-MM-DD HH24:MI:SS')<?}?>,

<?if("DATE".contains("CHAR")){?>SUBSTR(TO_DATE(SUBSTR('#HI_DATE',0,19),'YYYY-MM-DD HH24:MI:SS'),0,7)<?} else {?>TO_DATE(SUBSTR('#HI_DATE',0,19),'YYYY-MM-DD HH24:MI:SS')<?}?>,

<?if("DATE".contains("CHAR")){?>SUBSTR(#LOW_DATE,0,7)<?} else {?>#LOW_DATE<?}?>,

<?if("DATE".contains("CHAR")){?>SUBSTR(#HI_DATE,0,7)<?} else {?>#HI_DATE<?}?>,

<?if("DATE".contains("CHAR")){?>SUBSTR(SYSDATE,0,7)<?} else {?>SYSDATE<?}?>,

<?if("NUMBER".contains("CHAR")){?>SUBSTR(#DATASOURCE_NUM_ID,0,10)<?} else {?>#DATASOURCE_NUM_ID<?}?>,

<?if("NUMBER".contains("CHAR")){?>SUBSTR(#ETL_PROC_WID,0,10)<?} else {?>#ETL_PROC_WID<?}?>

from <?=snpRef.getObjectName("L", "%INT_PRF<$=X$>", "W") ?> S

where

","D")) ;

out.print(".NEXTVAL\n\nwhere\t()\n\tin\t(\n\t\tselect\t\n\t\tfrom\t<?=snpRef.getObjectName(\"L\", \"%INT_PRF<$=X$>\", \"W\") ?>\n\t\twhere\tIND_UPDATE = 'U'\n\t\t)\n\n\n<$ } else { $>\n/* Full load flow bypass */\n<$ } $>") ;

****** ORIGINAL TEXT ******

<$ if (bypassFlow.equals("N")) { $>

/* DETECTION_STRATEGY = OUTER */

update #ETL_HINT_UPDATE <?=snpRef.getObjectName("L", "WC_PARTY_LOCATION_D", "DW_BIAPPS11G", "", "D") ?> T

set (

T.GEO_WID,

T.ST_ADDRESS1,

T.ST_ADDRESS2,

T.ST_ADDRESS3,

T.ST_ADDRESS4,

T.CITY,

T.COUNTY,

T.POSTAL_CODE,

T.STATE_PROV_CODE,

T.COUNTRY_CODE,

T.C_CONTINENT_CODE,

T.C_REGION_CODE,

T.W_COUNTRY_CODE,

T.C_COUNTRY_REGION_CODE,

T.C_STATE_PROV_CODE,

T.C_COUNTY_CODE,

T.C_CITY_CODE,

T.CREATED_BY_WID,

T.CHANGED_BY_WID,

T.CREATED_ON_DT,

T.CHANGED_ON_DT,

T.INTEGRATION_ID,

T.ROW_WID,

T.CUSTOMER_WID,

T.SRC_EFF_FROM_DT,

T.SRC_EFF_TO_DT,

T.EFFECTIVE_FROM_DT,

T.EFFECTIVE_TO_DT,

T.W_INSERT_DT,

T.W_UPDATE_DT,

T.DATASOURCE_NUM_ID,

T.ETL_PROC_WID

) =

(

select <?if("NUMBER".contains("CHAR")){?>SUBSTR(S.GEO_WID,0,10)<?} else {?>S.GEO_WID<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.ST_ADDRESS1,0,255)<?} else {?>S.ST_ADDRESS1<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.ST_ADDRESS2,0,255)<?} else {?>S.ST_ADDRESS2<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.ST_ADDRESS3,0,255)<?} else {?>S.ST_ADDRESS3<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.ST_ADDRESS4,0,255)<?} else {?>S.ST_ADDRESS4<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.CITY,0,120)<?} else {?>S.CITY<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.COUNTY,0,120)<?} else {?>S.COUNTY<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.POSTAL_CODE,0,120)<?} else {?>S.POSTAL_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.STATE_PROV_CODE,0,120)<?} else {?>S.STATE_PROV_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.COUNTRY_CODE,0,120)<?} else {?>S.COUNTRY_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.C_CONTINENT_CODE,0,120)<?} else {?>S.C_CONTINENT_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.C_REGION_CODE,0,120)<?} else {?>S.C_REGION_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.W_COUNTRY_CODE,0,120)<?} else {?>S.W_COUNTRY_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.C_COUNTRY_REGION_CODE,0,120)<?} else {?>S.C_COUNTRY_REGION_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.C_STATE_PROV_CODE,0,120)<?} else {?>S.C_STATE_PROV_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.C_COUNTY_CODE,0,120)<?} else {?>S.C_COUNTY_CODE<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.C_CITY_CODE,0,120)<?} else {?>S.C_CITY_CODE<?}?>,

<?if("NUMBER".contains("CHAR")){?>SUBSTR(S.CREATED_BY_WID,0,10)<?} else {?>S.CREATED_BY_WID<?}?>,

<?if("NUMBER".contains("CHAR")){?>SUBSTR(S.CHANGED_BY_WID,0,10)<?} else {?>S.CHANGED_BY_WID<?}?>,

<?if("DATE".contains("CHAR")){?>SUBSTR(S.CREATED_ON_DT,0,7)<?} else {?>S.CREATED_ON_DT<?}?>,

<?if("DATE".contains("CHAR")){?>SUBSTR(S.CHANGED_ON_DT,0,7)<?} else {?>S.CHANGED_ON_DT<?}?>,

<?if("VARCHAR2".contains("CHAR")){?>SUBSTR(S.INTEGRATION_ID,0,80)<?} else {?>S.INTEGRATION_ID<?}?>,

<?if("NUMBER".contains("CHAR")){?>SUBSTR(WC_PARTY_LOCATION_D_SEQ.<%=odiRef.getObjectName("L","),0,10","D")%>.NEXTVAL<?} else {?>WC_PARTY_LOCATION_D_SEQ.<%=odiRef.getObjectName("L",")<?}?>,