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

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
begin
outdata := mytabletype();
for i in 1..3 loop
outdata.extend;
outdata(i) := mytype(i, 'some name');
end loop;
end;
/

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;
begin
mycreatenames1(mydata);
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)
plsql.exec()
cursor = plsql[':rc']
plsql.close
while r = cursor.fetch()
puts r
end
cursor.close()
conn.logoff

The output is:

1.0
some name
2.0
some name
3.0
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;
begin
mycreatenames1(mydata);
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);
oci_execute($s);

var_dump($empno);
var_dump($ename);

Comments:

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today