Hi,
I would like to create a MERGE statement in my Oracle IKM and based on the value of a variable, it should have an additional sql block or not. I pass the value of the variable to IKM using an option. My IKM is custom built, based on default SCD Type2 Oracle IKM.
I've created #global.v_wbls_MicrobatchIKMDeleteEnabled variable. No history, Alphanumeric type.
I've created SOFT_DELETE_ENABLED option to IKM, Type = value.
I've created a simple mapping and a package. I declare the variable, set the variable in the package and call my mapping, which uses my IKM.
The problem is, when I execute the mapping, the variable value is not correct, it still has the #variable name in it. I can see the correct value in Operator → Failed Task → Variable and Sequence Values, and also Operator → Failed Task → Code → Show Hide values .
Below is the code for my failed task in IKM. It has Target Technology as Oracle and language as SQL.
<%myOption=odiRef.getOption("SOFT_DELETE_ENABLED");
out.print("/* Option value =" + myOption + " */"); // this shows 1 with Show/Hide values in Operator %>
<%
out.print("/* Option value length =" + odiRef.getOption("SOFT_DELETE_ENABLED").length() + " */"); // this prints 41 (length of #project.variableName )
out.print("/* Option value substr =" + odiRef.getOption("SOFT_DELETE_ENABLED").substring(0,3) + " */"); // this prints #gl
<%if (odiRef.getOption("SOFT_DELETE_ENABLED").equals("1") ){
or (qry_change_cd = 'D' and (lvl.lvl = 2 or lvl.lvl = 3))
<% } %>
The bold if statement never gets correct value and you can see the printed values as comments above.
I created another step with jython technology and single line:
raise Exception(odiRef.getOption("SOFT_DELETE_ENABLED"))
and it succesfully throws the exception as 1, but SQL technology does not correctly resolve the IKM Option → Variable name → Variable value substitutions.
Can anyone help?