Executing a stored oracle procedure in R using ROracle

Question

I'm having trouble executing/calling an Oracle procedure in R via ROracle. I've tried many different ways of calling the procedure and I keep getting the same errors. I've had no problem doing SELECT queries but calling a procedure is proving difficult. I've used both oracleProc and dbSendQuery functions, but to no avail. Neither of them work. Roracle documentation is pathetic for examples of calling procedures.

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

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

The following works 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 error. I've tried many different ways of calling the procedure i get 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:

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 got (a bit 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;")

My procedure doesn't have any parameters. As I mentioned it works just fine when called in Oracle developer. I've run out of ideas how to get such a ridiculously simple query work in R! I am only interested in getting this work via ROracle though.

Answer 1

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

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

Then try to execute the procedure like this:

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

You're right that ROracle::oracleProc documentation is not good. This example helped me: https://community.oracle.com/thread/4058424