PHP: Binding variable to table type output parameter

Question

Ok I'm having an issue binding output param which is to return a table from an Oracle db.

here's an example:

procedure_name(
    p_first IN NUMBER,
    p_second IN VARCHAR2,
    x_table_name OUT some_table_type,
    x_row_count OUT NUMBER
);

Everything works fine in oracle working with this procedure.

I come over to PHP I try this and no go:

$first = 55;
$second = 'Hello';

$stm = oci_parse($conn, "begin procedure_name(:p_first, :p_second, :x_table_name, :x_row_count)); end;"); 
oci_bind_by_name($stm, ":p_first", $first, 11, SQLT_INT);
oci_bind_by_name($stm, ":p_second", $second, 11, SQLT_INT);
oci_bind_by_name($stm, ":x_table_name", $table_output, -1, OCI_B_NTY);
oci_bind_by_name($stm, ":x_row_count", $table_row_count, 11, SQLT_INT);

oci_execute($stm);

And as a result I Get back: ORA-01008: not all variables bound.

Now I do still need to figure out how to fetch the variable $table_output as an array of objects or just an assoc array, but haven't gotten that far yet :/

You can't use placeholders for variable names. They can only be used in places where expressions are expected. — Barmar
It doesn't make sense to use these parameters on the procedure definition. You should parametrize the CALL procedure_name(:p_first, :p_second, :x_table_name, :x_row_count) query. — Barmar

Jubair Jubair · Accepted Answer · 2015-01-15T18:44:57

The issue was resolved by using:

 $table_output = oci_new_collection($conn,'some_table_type','schema');

before binding.

A great resource has been page 204 to 212 of: http://www.oracle.com/technetwork/database/database-technologies/php/201212-ug-php-oracle-1884760.pdf

It has plenty of examples of how to fetch custom field and table type data from Oracle procedures and functions.

PHP: Binding variable to table type output parameter

1 Answers