Achilles Run issues - Oracle

smanikonda · April 4, 2019, 1:14am

Hi Team,

I am running Achilles and am getting the below error. I was able to create the tables in the Results schema, from the WebAPI/ddl/results location. and then I ran Achilles.

Here are the steps I followed with reference to Achilles:

Installed R
Installed Achilles by following the documentation
Ran Achilles as below:

library(Achilles)
connectionDetails ← createConnectionDetails(
dbms=“oracle”,
server=“server/sid”,
user=“username”,
password=‘password’,
port=“1521”)

achilles(connectionDetails,
cdmDatabaseSchema = “c##cdm”,
resultsDatabaseSchema=“c##results”,
vocabDatabaseSchema = “c##cdm”,
numThreads = 1,
sourceName = “My Cdm”,
cdmVersion = “5.3.1”,
runHeel = TRUE,
runCostAnalysis = FALSE)

When I gave TRUE for cost analysis, it gave me some other error, hence I wanted to first test with out cost analysis. The screenshot with error is attached.

Not sure if I am missing something. Appreciate any help on this.

For now, i am trying to run it in Single Thread mode

Chris_Knoll · April 4, 2019, 2:24am

Is this really your schema name? With ## in it?

smanikonda · April 4, 2019, 2:52pm

@Chris_Knoll,

That’s correct Chris! I think thats the policy/standard here at UT-SBMI. Any user/schema will have the prefix ‘c##’. And my username is: c##smanikonda.

Do you think that is causing the issues I am facing?

smanikonda · April 4, 2019, 3:48pm

@Ajit_Londhe @Chris_Knoll,

I ran Achilles again and I still get similar error. The tables names are prefixed with ‘#.’ , which is causing the issues. Any help on how to fix this issue.

I have installed Atlas, but not able to see any of my CDM table data as I am not able to run Achilles.

Ajit_Londhe · April 4, 2019, 5:23pm

What version of Oracle?

Ajit_Londhe · April 4, 2019, 5:00pm

Hi @smanikonda,

I think I found the issue, but I can’t test since I don’t have Oracle. Essentially, I have been setting tempOracleSchema to scratchDatabaseSchema in every SqlRender translate call, which is making the temp tables become named “#.tablename”, which is invalid.

Can you uninstall Achilles completely and try installing the oraclefixes branch by using this command:

devtools::install_github("OHDSI/Achilles", ref = "oraclefixes")

smanikonda · April 4, 2019, 10:52pm

HI @Ajit_Londhe,
Thanks a lot for your quick response. We have:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Could you please let me know how I can uninstall Achilles completely. I tried to look for any documentation, but could not find. Can you please help.

I will run the command you mentioned after Uninstalling Achilles.

Ajit_Londhe · April 4, 2019, 10:56pm

Restart R, then run remove.packages("Achilles")

smanikonda · April 4, 2019, 11:25pm

Thanks a lot @Ajit_Londhe

I don’t have permissions to remove the packages. And I got the error (attached) when I try to do it. Logged a ticket for my sysadmins. Will work on it tomorrow and update you.

I really appreciate your help.

smanikonda · April 9, 2019, 4:51pm

@Ajit_Londhe,
I got the permissions finally and followed the below steps:

Uninstalled Achilles
Reinstalled using the option you provided (i.e. with this command
devtools::install_github(“OHDSI/Achilles”, ref = “oraclefixes”)
When I run it. I got the error shown in the attached file. Earlier i was able to connect, but this time it is not able to connect to Oracle i think.

Can you please help me in resolving this issue.

Dave.Barman · April 9, 2019, 5:01pm

Hi Sarath,

Which version of Java do you have installed?

-Dave Barman

smanikonda · April 9, 2019, 5:14pm

@Dave.Barman ,

This is what I have:

java version "1.6.0_41"
OpenJDK Runtime Environment (IcedTea6 1.13.13) (rhel-1.13.13.1.el7_3-x86_64)
OpenJDK 64-Bit Server VM (build 23.41-b41, mixed mode)

smanikonda · April 9, 2019, 7:51pm

@Ajit_Londhe, @Dave.Barman

What version of Java is required. We have upgraded our Java and tried. But still getting the same error.

Dave.Barman · April 9, 2019, 7:59pm

I did not have the best luck with the OpenJDK. I ended up having success with Oracle Java JDK version 11. Make sure your JAVA_HOME environment variable is set to the correct directly

smanikonda · April 10, 2019, 10:56pm

Hi @Ajit_Londhe, @Chris_Knoll

I need your help. I was able to Oracle JDK version 11 installed today and when I run it, i get the below error. Looks like the build was made for SQL Server. Appreciate if you can help me with the correct one.

Thanks and Regards

Sarath M

Chris_Knoll · April 11, 2019, 2:16am

use a lowercase ‘O’ in ‘oracle’ in your dbms paramater.

Ajit_Londhe · April 11, 2019, 2:17am

Yep, and please re-install from the oraclefixes branch of Achilles. I pushed some fixes today that should help with ensuring the tempOracleSchema is used properly.

smanikonda · April 11, 2019, 4:01am

I changed the O in oracle to lower case. This time its the Java error again. We have installed Java 11, but I am still getting the ‘…major.minor version 52.0 error’.

Could you please suggest the version of Java that we need to have installed for Achilles to work. Thanks a lot for all your help. I feel like I am almost there, but not there yet.

Chris_Knoll · April 11, 2019, 4:46am

Looks like you will need to run under JDK 8:

The issue is because of Java version mismatch. Referring to the Wikipedia Java Class Reference:

Java SE 12 = 56 (0x38 hex)

Java SE 11 = 55 (0x37 hex)

Java SE 10 = 54

Java SE 9 = 53

Java SE 8 = 52

Java SE 7 = 51

Java SE 6.0 = 50

Java SE 5.0 = 49

JDK 1.4 = 48

JDK 1.3 = 47

JDK 1.2 = 46

JDK 1.1 = 45

The version 52 is referring to JDK 8.

I’m surprised that 11 is not working for you, but the problem is definitely related to the version of the JDK.

Chris_Knoll · April 11, 2019, 4:51am

This may help you:

If you execute:

library(rJava)
.jinit()
.jcall("java/lang/System", "S", "getProperty", "java.runtime.version")

in R, what output does it give you?