Passing an array from PHP to an Oracle stored procedure

When I wrote this about a year ago, there was no documentation on this subject anywhere. I seemed to have figured it out through a combination of trial, error, and luck. I don’t work with Oracle anymore, but hopefully someone else will find this useful:

1.) First I created my own myarray datatype in Oracle. This was just an array of 256 varchar fields.

2.) In the stored procedure, I defined one input parameter of type myarray. The values of the myarray variable can be accessed just like any other array in PL/SQL: myarray(1), myarray(2), etc.

3.) You can’t just pass the array into the stored procedure. You have to tell PHP that you’re working with a custom Oracle datatype first. Here’s how I did that:

$myarray = ocinewcollection($oracle_connection, 'MYARRAY', $database_name);

4.) Next I populated myarray:

$myarray->append('test1');
$myarray->append('test2');
$myarray->append('test3');

5.) When you bind the myarray type from PHP, you have to use a few extra parameters (which I couldn’t find any good documentation on, so don’t ask me what they mean, because I honestly don’t remember how I figured them out ;-):

ocibindbyname($statement, ':myarray', 32, OCI_B_SQLT_NTY);

And thats it! It looks like it’s possible to pass multidimensional arrays and other more complex structures in a similar way, but I never got a chance to attempt that.