PHP and Oracle: Christopher Jones

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