The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

  • php
    November 30, 2006

Casting PL/SQL arrays as REF CURSORS for Ruby (and PHP)

Christopher Jones
Senior Principal Product Manager

Jason Vogel has been using Ruby with his company's existing PL/SQL packages.  One problem he faced was calling a procedure that returned a collection type.

PHP's oci8 extension oci_bind_array_by_name() can bind collections but there is no equivalent in ruby-oci8.

Jason outlines a solution on his blog Ruby: Invoking a PL/SQL Package with Array args

His example relies on some company infrastructure so I came up with this standalone example.  This first SQL*Plus script sets up the scenario.  The procedure mycreatenames1() returns the collection in the parameter.  Each element of the collection is a number and a name. For this example the names are all hardcoded to 'some name'.  

create or replace type mytype as 
object (empno number, ename varchar2(20));

create or replace type mytabletype as table of mytype;

create or replace procedure
mycreatenames1(outdata out mytabletype) as
outdata := mytabletype();
for i in 1..3 loop
outdata(i) := mytype(i, 'some name');
end loop;

Mycreatenames1() is the procedure you can't call with ruby-oci8. What you can do is create a wrapper function to convert the array to a REF CURSOR:

create or replace procedure 
mywrapper1(rcemp out sys_refcursor) as
mydata mytabletype;
open rcemp for
select * from table(cast(mydata as mytabletype));
end mywrapper1;

You can then call this from Ruby:

require 'oci8'
conn = OCI8.new('hr', 'hr', '//localhost/XE')
plsql = conn.parse("begin mywrapper1(:rc); end;")
plsql.bind_param(':rc', OCI8::Cursor)
cursor = plsql[':rc']
while r = cursor.fetch()
puts r

The output is:

some name
some name
some name

You can also use this wrapper trick in PHP and fetch the REF CURSOR (see the Underground PHP and Oracle Manual in the Links). However you may find it faster to return two collections and call oci_bind_array_by_name() twice:

The PL/SQL wrapper could be created:

create or replace procedure 
mywrapper2(pempno out dbms_sql.NUMBER_table,
pename out dbms_sql.VARCHAR2_table) as
mydata mytabletype;
select empno, ename
bulk collect into pempno, pename
from table(cast(mydata as mytabletype));
end mywrapper2;

The PHP code to call it would be:

$c = oci_connect("hr", "hr", "//localhost/XE");
$s = oci_parse($c, "begin mywrapper2(:empno, :ename); end;");
oci_bind_array_by_name($s, ":empno", $empno, 10, -1, SQLT_INT);
oci_bind_array_by_name($s, ":ename", $ename, 10, 20, SQLT_CHR);


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.