Facing Issue while running Achilles

[rahulbujji45 (Rahul)]

Hi Team,

I am running Achilles Analysis in linux machine but i am getting errors while running it i am giving the errorReport details below and i am facing same issue while trying to run it for impala too

DBMS:
oracle

Error:
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
ORA-06512: at line 7

SQL:
BEGIN
EXECUTE IMMEDIATE ‘TRUNCATE TABLE #.nrvbeomgs_tmpach_Drug_cost_raw’;
EXECUTE IMMEDIATE ‘DROP TABLE #.nrvbeomgs_tmpach_Drug_cost_raw’;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;

R version:
R version 3.5.1 (2018-07-02)

Platform:
x86_64-redhat-linux-gnu

Attached base packages:

• stats
• graphics
• grDevices
• utils
• datasets
• methods
• base

Other attached packages:

• Achilles (1.6.6)
• DatabaseConnector (2.3.0)

Thanks,
Rahul.

[schuemie (Martijn Schuemie)]

Hi @rahulbujji45! What scratchDatabaseSchema argument are you using?

[rahulbujji45 (Rahul)]

@schuemie i am using the same database which i am using as cdmDatabaseSchema.

Thanks,
Rahul.

[Chris_Knoll]

@rahulbujji45

EXECUTE IMMEDIATE ‘TRUNCATE TABLE #.nrvbeomgs_tmpach_Drug_cost_raw’;

SqlRender should have removed # from the oracle temp table names. I’m pretty sure we have sqlRender test cases for dealing with temp tables. @Ajit_Londhe, do you recognize this query from the Achilles analysis? It looks related to the cost tables, but not sure if that’s something from 5.3 or 6.0?

Tagging @pavgra and @gregk for input on the question on Impala.

[rahulbujji45 (Rahul)]

@Chris_Knoll thanks for the reply, i am using connection to Achilles as below

library(Achilles)
omopConn <- createConnectionDetails(
dbms=“oracle”,
server=“hostname/sid”,
user=“omop_cdm”,
password=“omop”,
schema=“OMOP_CDM”,
port=“1521”
)

ccaeRes <- achilles(connectionDetails = omopConn,
cdmDatabaseSchema = “omop_cdm”,
resultsDatabaseSchema = “omop_cdm”,
vocabDatabaseSchema = “omop_cdm”,
sourceName = “OMOP CDM”,
cdmVersion = “5.0”,
runHeel = TRUE,
runCostAnalysis = TRUE,
dropScratchTables = TRUE, // i added this option to drop scratch tables as per Achilles documentation
createTable = TRUE) // i added this option to drop scratch tables as per Achilles documentation

Thanks,
Rahul.

[Chris_Knoll]

In case you missed this in the many other threads: your CDM and Results schema should be in separate schemas.

[Ajit_Londhe]

Hi @rahulbujji45,

For both v5 and v5+ we derive two cost staging tables, one for drug, one for procedure, before doing any of the cost analyses. So these commands are expected, just the table prefix is incorrect causing the error. Using 1 thread, all staging tables should be temp tables, and the table prefix shouldn’t be “#.”

When I debug with a dummy Oracle connection like the one you posted, I get these SQL commands:

[[1]]
[1] "BEGIN\n  EXECUTE IMMEDIATE 'TRUNCATE TABLE zue6skxns_tmpach_Drug_cost_raw';\n  EXECUTE IMMEDIATE 'DROP TABLE zue6skxns_tmpach_Drug_cost_raw';\nEXCEPTION\n  WHEN OTHERS THEN\n    IF SQLCODE != -942 THEN\n      RAISE;\n    END IF;\nEND;"

[[2]]
[1] "BEGIN\n  EXECUTE IMMEDIATE 'TRUNCATE TABLE zue6skxns_tmpach_Procedure_cost_raw';\n  EXECUTE IMMEDIATE 'DROP TABLE zue6skxns_tmpach_Procedure_cost_raw';\nEXCEPTION\n  WHEN OTHERS THEN\n    IF SQLCODE != -942 THEN\n      RAISE;\n    END IF;\nEND;"

Those appear to be valid. Can you try calling dropAllScratchTables directly?

[rahulbujji45 (Rahul)]

@Ajit_Londhe I am using dropScratchTables = TRUE parameter in Achilles analysis to drop scratch tables and if i want to drop scratch tables manually how can i figure out some table is scratch table?. @Chris_Knoll I created a new scheme for results database but still getting the below error

Error:
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character
ORA-06512: at line 7

SQL:
BEGIN
EXECUTE IMMEDIATE ‘TRUNCATE TABLE #.weqw0rois_tmpach_Drug_cost_raw’;
EXECUTE IMMEDIATE ‘DROP TABLE #.weqw0rois_tmpach_Drug_cost_raw’;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;

Thanks,
Rahul.

[Chris_Knoll]

The problem is that the table name in the ‘TRUNCATE TABLE’ statement starts with #, which is an invalid character in Oracle. For some reason, when the query was translated from the ‘default sql dialect’ into ‘oracle’, it didn’t identify the temp table with a # and strip the character.

There is a bug somewhere in the translation of this statement from OHDSI SQL to Oracle Sql.

[Ajit_Londhe]

@rahulbujji45, although you are specifying to drop scratch tables in the function call to achilles(), it would help in gathering more information if you could call the function dropAllScratchTables() directly. Although the temp table prefix Oracle generates is random, I’m curious to see if the mis-translation is consistent.

[rahulbujji45 (Rahul)]

@Ajit_Londhe sorry for asking the basic question i am newbie to OHDSI how can i call dropAllScratchTables() form the Achilles. Can you please point to some documentation how to use this function.

Thanks,
Rahul.

[Ajit_Londhe]

@rahulbujji45, this is covered in the vignette: https://github.com/OHDSI/Achilles/raw/master/vignettes/RunningAchilles.pdf in section 9.4. But here is an example:

library(Achilles)
omopConn <- createConnectionDetails(
dbms=“oracle”,
server=“hostname/sid”,
user=“omop_cdm”,
password=“omop”,
schema=“OMOP_CDM”,
port=“1521”
)
dropAllScratchTables(connectionDetails = omopConn, scratchDatabaseSchema = "omop_cdm", numThreads = 1, outputFolder = "output")

[Ajit_Londhe]

@rahulbujji45, I think i found the issue. Can you follow the steps here:

Achilles Run issues - Oracle

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")

[rahulbujji45 (Rahul)]

@Ajit_Londhe

library(Achilles) omopConn <- createConnectionDetails( dbms=“oracle”, server=“hostname/sid”, user=“omop_cdm”, password=“omop”, schema=“OMOP_CDM”, port=“1521” ) dropAllScratchTables(connectionDetails = omopConn, scratchDatabaseSchema = “omop_cdm”, numThreads = 1, outputFolder = “output”)

Hi @Ajit_Londhe i uninstalled the Achilles currently in my system and installed devtools::install_github(“OHDSI/Achilles”, ref = “oraclefixes”) but when i try to run Achilles Analysis i am getting below error

Error in SqlRender::loadRenderTranslateSql(sqlFilename = file.path(“analyses”, :
object ‘oracleTempSchema’ not found

I tried with even setting oracleTempSchema=Tempccaetable but still i am getting database not found.

Thanks,
Rahul.

[rahulbujji45 (Rahul)]

@Ajit_Londhe can you please suggest me how can i over come the error or in SqlRender::loadRenderTranslateSql(sqlFilename = file.path(“analyses”, :
object ‘oracleTempSchema’ not found if i am using package “oraclefixes”.

Thanks,
Rahul.

[Ajit_Londhe]

I pushed a few fixes to the branch. Can you remove Achilles and install from the oraclefixes branch again?

[DavePh (Dave)]

Hi,
We recently attempted to upgrade the Achilles code (we use Oracle 12) and are getting the same error mentioned in this post. I see in the last statement that some fixes had been pushed. However, we pulled from the most recent branch and are getting the error. Was there a final solution that has not been added to this thread?