First of all, I’m sorry but I am kind of rewriting this question with some follow up updates because, the old question cannot be edited for some reason. So, please accept my appologies.
Hello, I’m using OncoRegimenFinder recently, and getting an oracle syntaxerror…
It’s suspicious that the library OncoRegimenFinder does not work properly with oracle.
The error is the following:
Error in lowLevelQuerySql.defualt(conn,statement) :
java.sql.SQLSyntaxErrorException: ORA-00933: SQL COMMAND NOT PROPERLY ENDED
It’s suspicious that there is some comma or parenthesis dropped in a sql file, since it is a syntax error.
I tried to find any error in the sql files in OncoRegimenFinder/sql/sql_server directory, but I couldn’t.
Here is the code that I wrote in R.
connDetails<-createConnectionDetails(dbms="oracle", server="****", user="gr2338", password="****", pathToDriver="C:/Program Files/sqldeveloper/jdbc/lib", oracleDriver="thin")
create_regimens(connectionDetails=connDetails, cdmDatabaseSchema="CDM_2020_VIEW", writeDatabaseSchema="gr2338", cohortTable="COHORT", regimenTable="gr2338_regimen", regimenIngredientTable="gr2338_regimenIngredient",vocabularyTable="gr2338_voc", drug_classification_id_input=c("35804391","35804530"), date_lag_input=30, regimen_repeat=5, generateVocabTable=T)
Or is it the case that OncoRegimenFinder only works with postgresql?
I would appreciate very much for any help.
Update:
I used SqlRender library in R to translate those sql files to oracle versions, and I checked line by line to see where the error comes from. It seems that in RegimenFormat.sql file, when creating regimenIngredientTable, there comes an error of ORA-00907: ‘missing right parenthesis’. However, oddly, I found no missing right parenthesis. I’m guessing that there might have been missing whole parenthesis at somewhere but, since I’m not an expert on sql, I cannot find that position. I would try to learn some sql’s, and will try to see where the error comes from.
Update2:
It seems that in RegimenVoc.sql file, the same error of missing right parenthesis occurs. The common thing in both file is that there is ‘STUFF’ function written. But, when I searched internet, the ‘STUFF’ function does not work in oracle. And that’s why the error is happening. Or will it be the case that SqlRender cannot translate properly for some sql? What would be the problem?