将PHP数组传递到Oracle Stored Proc(PLS-00306:错误的参数数目或类型) [英] passing PHP array to Oracle Stored Proc (PLS-00306: wrong number or types of arguments)
问题描述
我使用PHP 5.3.2和Oracle 11G,试图将数组从PHP传递到oracle存储的proc中.这是我的PL/SQL:
Using PHP 5.3.2 and Oracle 11G, I'm trying to pass an array from PHP into an oracle stored 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;
查询:
$sql = "begin txa.upsert_txa_compliance_slct(:my_array); end;";
还有我尝试绑定数组并执行的PHP代码:
And the PHP Code I've tried to bind the array and execute :
第一:
<?
$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);
?>
哪个会产生此错误:
PLS-00306:调用"UPSERT_TXA_COMPLIANCE_SLCT"时参数的数量或类型错误
PLS-00306: wrong number or types of arguments in call to 'UPSERT_TXA_COMPLIANCE_SLCT'
我显然要传递1个arg.那么,这是什么问题/如何解决类型问题?
I'm clearly passing 1 arg. So, what's wrong with/how do I fix the type issue?
另外我发现了
http://www.oracle.com/technetwork/articles/seliverstov-multirows-098120.html
并使用oci收集以旧方式进行尝试,如下所示:
And tried it the old way using oci collection like so:
$collection = oci_new_collection($this->conn,"NUM_ARRAY");
将我的oracle类型更改为此后:
After I changed my oracle type to this:
create or replace type NUM_ARRAY as varray(100) of number;
我收到此错误:
oci_new_collection():ORA-22318:输入类型不是数组类型
oci_new_collection(): ORA-22318: input type is not an array type
任何帮助将不胜感激.
EDIT美国东部时间2014年8月14日晚上7:08
EDIT 7:08PM ET Aug 14, 2014
我将我的php oci_bind函数调用更改为使用SQLT_NUM作为类型.这没有影响.然后,我将包裹更改为包括:
I changed my php oci_bind function call to use SQLT_NUM as the type. This had no impact. Then I changed my package to include:
type num_array is table of number index by binary_integer;
(我也从架构中删除了原始的num_array)
( i also dropped the original num_array from my schema )
此更改使将数组传递给存储的proc成为可能,但是这样我就不能将数组用作嵌套表,就像这样:
This change made it possible to pass my array to the stored proc, but then I can't use the array as a nested table like so:
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 that statement in it:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
所有文档都告诉我要返回架构级别类型吗?但是,当我这样做时,我会遇到其他错误.我知道我可以找到另一种方法,可以在我的pl/sql数组上使用循环,但是我真的很希望能够使用该架构级别类型.
And all the documentation tells me to return to the schema level type? But when I do I get that other error. I know I can find another way to do this using a loop over my pl/sql array, but I would really love to be able to use that schema level type.
推荐答案
答案是这样的.您不能使用全局创建的或架构级别的类型作为存储过程的参数. PHP的oci_bind_array_by_name似乎不适用于全局创建的类型,但是您需要全局创建的类型才能将数组用作子选择中的嵌套表.所以....这就是我如何使它工作的.我非常高兴听到其他解决方案!!但是现在,这就是我所做的.
The answer is this. You can't 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 seem to work with globally created types, but you need the globally created type to be able to use your array as a nested table in subselects. So.... here is how I got this to work. I'm MORE THAN HAPPY TO HEAR OTHER SOLUTIONS!! but for 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));
这篇关于将PHP数组传递到Oracle Stored Proc(PLS-00306:错误的参数数目或类型)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!