calling Oracle stored procedures in R - how to get the result set?

Question

Looking for an example for calling Oracle stored proc using R, and returning a result set.

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".

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."

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....

Thanks, Mike

UPDATE: I'd take an example that simply called an Oracle stored proc. Are Oracle procs simply not supported currently in RJDBC?

Answer 1

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.

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;

The package body:

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;

Usage:

select * from table(pkg1.get_recs(3));

Or:

select num, val from table(pkg1.get_recs(3));

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

Hope that helps.