Oracle table names - not happy with the length of Depression12mo_drug_seq_summary

[rkboyce]

Hi,

While esting protocol 2 by running the MainAnalysis.R script configured for our dataset (Oracle >=11g, R 3.0) I ran into error "ORA-03291: Invalid truncate option - missing STORAGE keyword". This issue was triggered because I used a hyphen instead of an underscore in the "sourceName" variable - my bad. However, after correcting this, I found another issue that I think will require a slight change to the code. Specifically, the table prefix 'Depression12mo' leads to table names (e.g. 'Depression12mo_drug_seq_summary') that causes Oracle to trigger 'ORA-00972: identifier is too long'. The character limit on Oracle tables is 30. Going through the MainAnalysis.R and HelperFunctions.R and replacing 'Depression12mo' with 'Depr12mo' fixes this issue.

--- output before fix ---

> source("MainAnalysis.R")
Loading required package: RJDBC
Loading required package: DBI
Loading required package: rJava
Created file 'TxPath autoTranslate oracle HTN12mo.sql'
Created file 'TxPath autoTranslate oracle T2DM12mo.sql'
Created file 'TxPath autoTranslate oracle Depression12mo.sql'
Connecting using Oracle driver
  |============                                                          |  17%Error executing SQL: Error in .local(conn, statement, ...): execute JDBC update query failed in dbSendUpdate (ORA-00972: identifier is too long
ORA-06512: at line 7
)

An error report has been created at  /home/rdb20/OHDSI-code/StudyProtocols/Study 2 - Treatment Pathways 12mo/R Version/errorReport.txt
Error in value[[3L]](cond) : no loop for break/next, jumping to top level

after

> source("MainAnalysis.R")
Created file 'TxPath autoTranslate oracle HTN12mo.sql'
Created file 'TxPath autoTranslate oracle T2DM12mo.sql'
Created file 'TxPath autoTranslate oracle Depr12mo.sql'
Connecting using Oracle driver
  |======================================================================| 100%
Analysis took 19.8 secs
  |======================================================================| 100%
Analysis took 16.9 secs
  |======================================================================| 100%
Analysis took 16.8 secs
Created file 'HTN12mo_GOHDSI_summary.csv'
Created file 'HTN12mo_GOHDSI_person_cnt.csv'
Created file 'HTN12mo_GOHDSI_seq_cnt.csv'
Created file 'T2DM12mo_GOHDSI_summary.csv'
Created file 'T2DM12mo_GOHDSI_person_cnt.csv'
Created file 'T2DM12mo_GOHDSI_seq_cnt.csv'
Created file 'Depr12mo_GOHDSI_summary.csv'
Created file 'Depr12mo_GOHDSI_person_cnt.csv'
Created file 'Depr12mo_GOHDSI_seq_cnt.csv'