Calling Oracle stored procedures in R - how to get a result set?

Question

We are looking for an example to call an Oracle stored procedure using R and returning a result set.

I use the 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 do not see how to force this to return the Oracle result sets from the Oracle stored procedure (i.e., from the sys_refcursor out parameter). The only examples I find for retrieving data from Oracle include "select columns from table".

A google search led me to "dbCallProc - calling the SQL stored procedure", which sounds very promising, but every ref I found indicates that it is "not yet implemented."

Any pointers or examples of using procs? Very much appreciated. I don't know why Oracle should always be such a problem to retrieve result sets ....

Thanks Mike

UPDATE . I would give an example that is simply called the Oracle stored program. Are Oracle procs simply not currently supported in RJDBC?

Answer 1

I cannot help you specifically with R, but you say that you are having problems calling Oracle procedures that use OUT parameters as sys_refcursors. You also indicate that this ability is not yet implemented. However, you really say that you can "select columns from the table correctly."

So, I suggest changing the procedures to pipelined function calls, and then make a simple choice to get 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; 

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; 

Using:

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

Or:

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

This will return three rows of data, just like a procedure will return the same data. Only in this way can you get it from the select statement (which you seem to be able to handle with R).

Hope this helps.