在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屋！