使用PHP 5.3.2和Oracle 11G,我试图通过一个数组从PHP到Oracle存储过程。
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代码我试图绑定数组并执行:
:
<?
$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'时参数的数量或类型错误
我显然传递了1个参数。那么,有什么问题/我如何解决类型问题?
另外我发现了这个
http://www.oracle.com/technetwork/articles/seliverstov -多行的html
098120. -并尝试使用oci集合的旧方法,如下所示:
$collection = oci_new_collection($this->conn,"NUM_ARRAY");
我把oracle的类型改成这样:
create or replace type NUM_ARRAY as varray(100) of number;
我得到这个错误:
oci_new_collection(): ORA-22318:输入类型不是数组类型
任何帮助都将非常感激。
2014年8月14日东部时间下午7:08
我改变了我的php oci_bind函数调用使用SQLT_NUM作为类型。这没有影响。然后我修改了我的包,包括:
type num_array is table of number index by binary_integer;
(我还从模式中删除了原始的num_array)
这个更改使得可以将我的数组传递给存储过程,但是我不能像这样将数组用作嵌套表:
delete
from my_table
where id not in (select column_value from table(v_compl_id_array));
当我尝试编译包含该语句的包体时,我得到这个错误:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
所有的文档都告诉我返回到模式级类型?但当我这样做时,我得到了另一个错误。我知道我可以找到另一种方法,使用循环遍历我的pl/sql数组,但我真的希望能够使用模式级别的类型。
答案是:不能将全局创建的类型或模式级别的类型用作存储过程的参数。PHP的oci_bind_array_by_name似乎不适用于全局创建的类型,但您需要全局创建的类型才能将数组用作子选择中的嵌套表。所以…我是这样做的。我很高兴听到其他的解决方案!!但现在,这是我所做的。
-- 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));