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?

If it isn't implemented yet, then you're probably out of luck. Have you tried dumping the output to a file and then reading the file into R? — user554546
I just didn't believe I was the only person who wanted to do this, so thought I was missing something. The file dump is an option - I was also thinking I could call the proc in java and link it to my R code with rJava. I inherited some code that has direct Sybase proc calls in R, and that works fine. Now we're moving to Oracle, and I just wanted to swap out my calls, but not that simple, apparently. — Mike
From rforge.net/RJDBC/news.html : add support for stored procedure call JDBC syntax. Currently only IN parameters are supported ... — Marmite Bomber

tbone tbone · Accepted Answer · 2012-02-07T13:08:46

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.

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

1 Answers