Over the past two years, I didn’t participate in the <insert name> (it changed from OTN to ODC to OGB) Appreciation day.
Today, I would like to take a moment and think about the days after those 2 years.
When I left EPM space and went over to CPM space, I thought I’m going to be an EPM outcast. However, when I decided to come back, I thought I’m going to be a CPM outcast. To my surprise, both didn’t happen.
The Oracle team welcomed me, and I never felt like I left the space. Everyone I know from the DEV team was happy that I returned. Now on the CPM side, they welcomed me and was happy that I could do more within the partner space.
I’m not going to name anyone. However, you know who you are.
“Thank you for making me feel good and welcomed.”
I started out writing this one as a non-technical post and found an interesting issue.
Issue
I’m creating an Essbase calc on the fly by querying an Oracle database using a Groovy calc manager rule.
When the rule gets executed, I’m getting the following error.
java.lang.RuntimeException: Invalid column index
at com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF.main(GroovyCDF.java:82)
at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.hyperion.essbase.calculator.Launcher.execute(Launcher.java:58)
I couldn’t figure out what the error was, because I do have all the columns!
My column index is also fine because I know that I do have one column in SQL, and I’m returning that column only.
One by one, I tried to figure out what the issue is. I do have other calcs that are working fine (goes to an Oracle table and creates a script).
What is wrong with this groovy code?
The code
def multipleSQL = "SELECT USAGE_MULTIPLE FROM PRIME_CONVERSION WHERE YEAR='$PRIME_Year' AND SCENARIO='$PRIME_Scenario' AND VERSION='$PRIME_Version' AND SOLD_FROM='SoldFrom_$PRIMESoldTo' AND SOLD_TO='$PRIMENextSoldTo' AND SOURCE_PRODUCT='$PRIMESrcProduct' AND TARGET_PRODUCT='$PRIMETargetProduct'"
I wrote a print statement, and it is coming correctly in CDFLogger0.log. What could be wrong?
There is something wrong with this line.
PRIME_Year='FY20'
PRIME_Scenario='Budget'
PRIME_Version='Working'
PRIMESoldTo='7096'
PRIMENextSoldTo='8096'
PRIMESrcProduct='20516789'
PRIMETargetProduct='40516789'
def multipleSQL = "SELECT USAGE_MULTIPLE FROM PRIME_CONVERSION WHERE YEAR='$PRIME_Year' AND SCENARIO='$PRIME_Scenario' AND VERSION='$PRIME_Version' AND SOLD_FROM='SoldFrom_$PRIMESoldTo' AND SOLD_TO='$PRIMENextSoldTo' AND SOURCE_PRODUCT='$PRIMESrcProduct' AND TARGET_PRODUCT='$PRIMETargetProduct'"
println multipleSQL
println multipleSQL.getClass()
There is our issue staring at me! It is a GString (not the one you are thinking ;)).
The culprit is duck-typing. You are letting Groovy decide what multipleSQL is when you use def.
What can we do to fix it? You static type. In the code below – line 9, I’m asking groovy to consider multipleSQLStr as a String.
PRIME_Year='FY20'
PRIME_Scenario='Budget'
PRIME_Version='Working'
PRIMESoldTo='7096'
PRIMENextSoldTo='8096'
PRIMESrcProduct='20516789'
PRIMETargetProduct='40516789'
def multipleSQLStr = "SELECT USAGE_MULTIPLE FROM PRIME_CONVERSION WHERE YEAR='$PRIME_Year' AND SCENARIO='$PRIME_Scenario' AND VERSION='$PRIME_Version' AND SOLD_FROM='SoldFrom_$PRIMESoldTo' AND SOLD_TO='$PRIMENextSoldTo' AND SOURCE_PRODUCT='$PRIMESrcProduct' AND TARGET_PRODUCT='$PRIMETargetProduct'" as String
println multipleSQLStr
println multipleSQLStr.getClass()
Funny thing is some SQL statements work fine (those are duck-typed ones).
What did we (or just me, you already knew this) learn?
No more duck-typed SQLs in Groovy against Oracle database.