在R中调用Oracle存储过程-如何获取结果集? [英] calling Oracle stored procedures in R - how to get the result set?
问题描述
寻找一个使用R调用Oracle存储proc并返回结果集的示例.
Looking for an example for calling Oracle stored proc using R, and returning a result set.
我正在使用RJDBC库dbGetQuery来调用Sybase proc,并将结果指向一个变量,这与Oracle select stmts的工作原理相同.但是,我看不到如何从Oracle存储的proc(即从sys_refcursor out参数)返回Oracle结果集的方法.我发现从Oracle检索数据的唯一示例涉及从表中选择列".
I'm using RJDBC library, dbGetQuery to call Sybase procs and point the results to a variable, and this works the same for Oracle select stmts. However, I don't see how to get this to return Oracle result sets from an Oracle stored proc (i.e., from the sys_refcursor out param). The only examples I find for retrieving data from Oracle involve "select columns from table".
在google中搜索导致我找到" dbCallProc –调用SQL存储过程",这听起来很有希望,但是我发现它的每一个引用都表明它是"尚未实现. "
Searching in google was led me to "dbCallProc – Call an SQL stored procedure" which sounds very promising, but every ref I found to it indicates that it is "Not yet implemented."
是否有使用proc的指针或示例?非常感激.不知道为什么Oracle在检索结果集时总是要面对如此挑战....
Any pointers or examples for using procs? Greatly appreciated. Don't know why Oracle always has to be such a challenge for retrieving result sets....
谢谢, 迈克
更新:我将举一个简单地称为Oracle存储过程的示例. RJDBC当前是否仅不支持Oracle proc?
UPDATE: I'd take an example that simply called an Oracle stored proc. Are Oracle procs simply not supported currently in RJDBC?
推荐答案
对于R,我无能为力,但是您说在调用将OUT参数用作sys_refcursors的Oracle过程时遇到了问题.您还指示此功能可能尚未实现.但是,您确实说过,您可以从表中选择列"就可以了.
I can't help you specifically with R, but you say you're having issues in calling Oracle procedures that use OUT params as sys_refcursors. You also indicate this ability may not be implemented yet. You do say, however, that you can "select columns from table" just fine.
因此,我建议将过程更改为流水线函数调用,然后进行简单选择以从Oracle获取数据.一个小例子:
So, I propose changing the procedures to pipelined function calls, and then doing a simple select to get your data from Oracle. A small example:
CREATE OR REPLACE package pkg1 as
type t_my_rec is record
(
num my_table.num%type,
val my_table.val%type
);
type t_my_tab is table of t_my_rec;
function get_recs(i_rownum in number)
return t_my_tab
pipelined;
END pkg1;
包装体:
create or replace package body pkg1 as
function get_recs(i_rownum in number)
return t_my_tab
pipelined
IS
my_rec t_my_rec;
begin
-- get some data
-- implement same business logic as in procedure
for my_rec in (select num, val from my_table where rownum <= i_rownum)
loop
pipe row(my_rec);
end loop;
return;
end get_recs;
end pkg1;
用法:
select * from table(pkg1.get_recs(3));
或者:
select num, val from table(pkg1.get_recs(3));
这将返回3行数据,就像过程将返回相同的数据一样.只有这样,您才能从select语句(您似乎可以从R处理它)中获取它.
This would return 3 rows of data, just as a procedure would return the same data. Only this way you can get it from a select statement (which you seem to be able to handle from R).
希望有帮助.
这篇关于在R中调用Oracle存储过程-如何获取结果集?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!