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.

Slily

I don't know ROracle but I can tell you EXECUTE is a SQL*Plus command. It is neither valid in SQL nor PL/SQL calls. Try LOAD_query <- oracleProc(con1, "MYSCHEMA.MYPROC"). My guess is you don't need the semi-colon either but maybe you do. – Ringent

Thanks @APC, that was very helpful!! While your suggestion "MYSCHEMA.MYPROC" didn't work, i tried different versions of running it wihout the EXEC and i found the solution: "BEGIN MYSCHEMA,MYPROC; END;" Woohoo. PS- you would need ; at the end of the procedure call, which is different from sendQuery, which shouldn't have the ; at the end. So much inconsistency! >;-{ PSS- EXEC or EXECUTE both work in PLSQL for executing the procedure – Slily

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

Idolum

I definitely didn't need to use dbGetQuery. the procedure was already created and I don't have permission to create procedures on the particular schema anyway. But your suggestion with formatting the query and removing the EXEC worked: > LOAD_query <- oracleProc(con1, "BEGIN MYSCHEMA.MYPROC; END;") – Slily