Passing PHP Array to Oracle Stored Proc (PLS-00306: Wrong Number or Types of Arguments)
Using PHP 5.3.2 and Oracle 11G, I am trying to pass an array from PHP to oracle stored in proc. Here is my PL / SQL:
create or replace type NUM_ARRAY as table of number;
create or replace package txa as
procedure upsert_txa_compliance_slct( v_compl_id_array in num_array);
end txa;
create or replace package body txa as
procedure upsert_txa_compliance_slct(v_compl_id_array in num_array)
is
begin
.
. -- sql code removed for brevity. package and body compile no errors
.
end upsert_txa_compliance_slct;
end;
Request:
$sql = "begin txa.upsert_txa_compliance_slct(:my_array); end;";
And the PHP code I tried to bind to the array and executed:
Firstly:
<?
$this->conn = ociplogon($dbuser, $dbpass, $dbname);
$this->commit_mode = OCI_COMMIT_ON_SUCCESS;
$this->sth = @ociparse($this->conn, $sql);
oci_bind_array_by_name($this->sth,
':my_array',
$my_array,
count($my_array),
-1,
SQLT_CHR);
$r = @ociexecute($this->sth, $this->commit_mode);
?>
Which generates this error:
PLS-00306: Incorrect number or types of arguments when calling 'UPSERT_TXA_COMPLIANCE_SLCT'
I am passing 1 argument clearly. So what's wrong with / how can I fix the type problem?
Also I found this
http://www.oracle.com/technetwork/articles/seliverstov-multirows-098120.html
And tried it the old way using the oci collection:
$collection = oci_new_collection($this->conn,"NUM_ARRAY");
After I changed my oracle type to this:
create or replace type NUM_ARRAY as varray(100) of number;
I got this error:
oci_new_collection (): ORA-22318: input type is not array type
Any help would be much appreciated.
EDIT 7:08 pm ET Aug 14, 2014
I changed my php function call oci_bind to use SQLT_NUM as the type. It had no effect. Then I modified my package to include:
type num_array is table of number index by binary_integer;
(I also removed the original num_array from my schema)
This change allowed my array to be passed to the stored process, but then I cannot use the array as a nested table, e.g .:
delete
from my_table
where id not in (select column_value from table(v_compl_id_array));
I get this error when I try to compile the package body with this expression in it:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
And all the documentation tells me to go back to the schema level type? But when I do that, I get this other error. I know I can find another way to do this, using a loop over my pl / sql array, but I would really enjoy using this schema level type.
source to share
The answer is this. You cannot use a globally created or schema-level type as a parameter to a stored procedure. PHP's oci_bind_array_by_name just doesn't work with globally created types, but you need a globally created type to be able to use your array as a nested table in subqueries. So ... this is how I got it to work. I AM MORE HAPPY TO LISTEN TO OTHER SOLUTIONS! but now, here's what i did.
-- globally create a type table of number
create or replace type num_array is table of number;
-- in my package i created an internal type table of number
type i_num_array is table of number index by binary_integer;
-- i then used i_num_array (internal type) as the type for my IN parameter to the procedure
upsert_TXA_compliance_slct( v_compl_id_array in i_num_array)
-- in my procedure i also created a variable that is the type of my globally created type
v_num_array num_array := num_array();
-- then i populated that variable in a loop inside my procedure with the values in my IN param
for i in 1 .. v_compl_id_array.count
loop
v_num_array.extend(1);
v_num_array(i) := v_compl_id_array(i);
end loop;
-- then i used v_num_array as my nested table so this now works:
delete from my_table where id in (select * from table(v_num_array));
source to share