OGB Appreciation Day: GroovySQL and Oracle Database


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.

Leave a comment

Your email address will not be published. Required fields are marked *