Page 1 of 1

Returning a recordset from Oracle

Posted: Fri Dec 19, 2008 2:31 pm
by FrancisC
I have found how to fetch a cursor from Oracle though a stored procedure. I am now trying to get a single record. Is that possible?

Here is essentially what the Oracle code could look like (I say 'could' because I am using someone elses' code and the functions and types called are in several different packages):

Code: Select all

 
TYPE r_data IS RECORD (field1 NUMBER, field2 NUMBER);
 
g_r_data r_data;
 
PROCEDURE p_test (p_id IN NUMBER, p_r_data OUT r_data) IS
BEGIN
--The global variable g_r_data is initialized somewhere else.
p_r_data := g_r_data;
END;
 
Now, in php, this is what I am trying to do:

Code: Select all

 
   $stmt = oci_parse($conn,"begin p_test(:id, :data); end;"); 
 
   oci_bind_by_name($stmt,":id",$id,32); 
   
   $curs = oci_new_cursor($conn);
   oci_bind_by_name($stmt,":data",$curs,-1,OCI_B_CURSOR); 
 
   $id = 5;
   
   oci_execute($stmt); 
   oci_execute($curs); 
 
   oci_fetch_all($curs, $data, null, null, OCI_FETCHSTATEMENT_BY_COLUMN);
 
I get an error on the first oci_execute stating I have the wrong number or the wrong types of arguments. Obviously, r_data is a record, not a cursor which is why I am getting the error.

Anyone have any idea how to make this work?

Thanks!

Re: Returning a recordset from Oracle

Posted: Wed Dec 24, 2008 8:53 pm
by novice4eva
maybe you should try oci_bind_array_by_name, i have tried that in single dimension array, and just went through php.net manual, somebody has already complained that it doesn't work with multi-dimensional array. And by the looks of the procedure, it will return nothing...