使用ROracle在R中执行存储的oracle过程 [英] Executing a stored oracle procedure in R using ROracle

问题描述

我无法通过ROracle在R中执行/调用Oracle过程.我尝试了许多不同的方法来调用该过程，但我不断遇到相同的错误. 我执行SELECT查询没有问题，但事实证明调用过程很困难.我同时使用了oracleProc和dbSendQuery函数，但无济于事.他们都不工作. Roracle文档对于调用过程的示例是可悲的.

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.

比方说，Oracle过程在MYSCHEMA中称为MYPROC.此过程非常简单，没有参数(涉及读取一些表并写入表)

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)

当我直接在Oracle Developer中执行该过程时，没有问题:

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

以下内容适用于Oracle Developer(但不适用于R)

The following works in Oracle Developer (but not in R)

 EXEC MYSCHEMA.MYPROC;

然后，我尝试从R调用相同的过程(通过ROracle)，并给我错误.我尝试了许多不同的调用过程的方式，但都得到了相同的错误:

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;")

这是我得到的错误:

.oci.oracleProc中的错误(conn，语句，数据=数据，预取= 预取，:

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):

.oci.oracleProc中的错误(conn，语句，数据=数据，预取= prefetch ，: ORA-00900:无效的SQL语句

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):

.oci.SendQuery中的错误(conn，语句，数据=数据，预取= 预取，:

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;")

我的过程没有任何参数.正如我提到的那样，在Oracle开发人员中调用它时，它工作得很好. 我已经耗尽了如何在R中获得如此荒谬的简单查询工作的想法！ 不过，我只对通过ROracle获得这项工作感兴趣.

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.

推荐答案

您是否首先创建(编译)了该过程?例如:

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;")

您说对了，ROracle::oracleProc文档不好.这个例子对我有帮助: https://community.oracle.com/thread/4058424

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

这篇关于使用ROracle在R中执行存储的oracle过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！