Executing an oracle stored procedure in R using ROracle

Question

I am having problems executing / calling an Oracle procedure in R via ROracle. I have tried many different ways to call a procedure, and I keep getting the same errors. I had no problems executing SELECT queries, but the procedure call was difficult. I used the oracleProc and dbSendQuery functions, but to no avail. None of them work. The Roracle documentation is pathetic for sample call procedures.

Let's say the Oracle procedure is called MYPROC in MYSCHEMA. The procedure is very simple with NO parameters (it includes reading several tables and writing to the table)

When I execute the procedure directly in Oracle Developer, there is no problem:

The following steps are performed in Oracle Developer (but not in R)

EXEC MYSCHEMA.MYPROC; 

Then I try to call the same procedure from R (via ROracle) and gives me an error. I tried many different ways to call a procedure, I get the same errors:

  # This didn't work in R > require(ROracle) > LOAD_query <- oracleProc(con1, "BEGIN EXEC MYSCHEMA.MYPROC; END;") 

This is the error I get:

The error in .oci.oracleProc (conn, statement, data = data, prefetch = prefetch,:

 # Then i tried the following and it still didn't work > LOAD_query <- oracleProc(con1, "EXEC MYSCHEMA.MYPROC;") 

This is the error I received (slightly different from the one above):

Error in .oci.oracleProc (conn, statement, data = data, prefetch = prefetch ,: ORA-00900: invalid SQL statement

 # so then i tried dbSendQuery which works perfectly fine with any SELECT statements but it didn't work > LOAD_query <- dbSendQuery(con1, "BEGIN EXEC MYSCHEMA.MYPROC; END;") 

This is the error I get (same as the first one):

Error in .oci.SendQuery (conn, statement, data = data, prefetch = prefetch,:

 # I even tried the following to exhaust all possibilities. And still no luck. I get the same error as above: > LOAD_query <- oracleProc(con1, "BEGIN EXEC MYSCHEMA.MYPROC(); END;") 

There are no parameters in my procedure. As I mentioned, it works great when called in by an Oracle developer. I ran out of ideas on how to get such a ridiculously simple query in R! I'm only interested in this work through ROracle.

Answer 1

Did you first create (compile) the procedure? For instance:

 dbGetQuery(con, "CREATE PROCEDURE MYPROC ... ") 

Then try the procedure as follows:

 oracleProc(con, "BEGIN MYPROC(); END;") 

You are right that the documentation of ROracle::oracleProc not very good. This example helped me: https://community.oracle.com/thread/4058424

Answer 2

Have you tried using the DBI package (not ROracle)?

 driver <- dbDriver("Oracle") connection <- dbConnect(driver, ... = <connection_parameters>) result <- dbGetQuery(connection, "<QUERY>")