How to run sqlplus on oracle via R-CodePudding

I am running SQL-code on a oracle database. Some commands require to run them via sqlplus. Is there a way to avoid my commandline solution but directly running sqlplus via, e.g. dbSendStatement().

Pseudo code to not share any sensible information

# Via dbSendStatement ------------------------------------------------------------------------------
con <- odbc::dbConnect(odbc::odbc(), 
                       Driver = "oracle",
                       Host = "HOST", 
                       Port = "PORT",
                       SVC = "SVC", 
                       UID = Sys.getenv("USRDWH"),
                       PWD = Sys.getenv("PWDDWH"),
                       ssl = "true",
                       timeout = 10)
# Error
odbc::dbSendStatement(con, "EXEC SQL CODE")
# actual error message:
#> Error in new_result(connection@ptr, statement, immediate) : 
#>   nanodbc/nanodbc.cpp:1594: 00000: [RStudio][OracleOCI] (3000) Oracle Caller Interface: ORA-00900: invalid SQL statement


# Via system command -------------------------------------------------------------------------------
cmd <- paste0("sqlplus ", 
              Sys.getenv("USRDWH"), "/", Sys.getenv("PWDDWH"), 
              "@", "HOST", ":", "PORT", "/", "SVC", " ",
              "@", "EXEC script.sql")
cmd
#> [1] "sqlplus USR/PWD@HOST:PORT/SVC @EXEC script.sql"

# Works
system(cmd,
       intern = TRUE)

CodePudding user response：

Code like that always connects directly to the database. sqlplus is a specific client tool; it doesn't have its own API for those kind of interactions. In other words, you always connect to the database; you can't connect to sqlplus as it is not a service.

Your best option would be to convert your SQL in such a way that you can run it natively in your code using a direct database connection (i.e. don't use sqlplus). If your SQL commands cannot be adapted, then you will need to write a shell interaction to manipulate sqlplus as you did with cmd in your example.

That said, this implementation in your example is very insecure, as it will allow anyone with access to your host to see the database username, password, and connection info associated with the process while it is running. There are much more secure ways of scripting this, including the use of an auto-open Oracle Wallet to hold the credentials so you don't have to embed them in your code (which is always a bad idea, too).

Using Oracle Wallet, your cmd call would then look more like this:

sqlplus /@TNS_ALIAS @EXEC script.sql

This is still not perfect, but is a step or two in the right direction.