使用php执行oracle函数，php将自定义数组作为参数

Question

我有一个oracle函数，它有一个数组作为参数，如下所示：

addGroups($empGroupId, $employees);

它增加了员工组但这个数组参数在oracle数据库中被定义为自定义类型，如下所示：

CREATE OR REPLACE TYPE EMPLOYEES."ARR_VAR2"  is table of varchar2(32000);

我试着在php中调用这个函数，并按如下方式传递这个数组：

$postdata = file_get_contents("php://input");
$request = json_decode($postdata);
//recieving parameters
$empGroupId= $request->ID;
$employees= $request->EMPLOYEES;
//establish connection
$conn = oci_connect("EMPLOYEES", "****", "ip/orcl");
//test connection
if ($conn)
{
if($empGroupId!=NULL && $employees!=NULL)
{
$stmt =  "begin :resu :=addGroups(:empGroupId,:employees, :result ); end;";
$stid=oci_parse($conn,$stmt);
oci_bind_by_name($stid, ":empGroupId", $empGroupId);
oci_bind_array_by_name($stid, ":employees", $employees, 250, 250, SQLT_VCS );
oci_bind_by_name($stid, ":result", $result, 40);
oci_bind_by_name($stid, ":resu", $resu, 100);
oci_execute($stid);
oci_free_statement($stid);
$result1['output'] = array("result" => $result);
return $result1;
else
{
//connection failed
echo 500;
}

但它返回这个错误：

ORA-01458:变量字符串内的长度无效

我还尝试了以下oci新系列：

$stmt =  "begin :resu :=addGroups(:empGroupId,:employees, :result ); end;";
$stid=oci_parse($conn,$stmt);
oci_bind_by_name($stid, ":empGroupId", $empGroupId);
$IDs= oci_new_collection($conn, 'ARR_VAR', 'EMPLOYEES');
foreach($employees as $emp)
{
$IDs->append($emp);
}
oci_bind_by_name($stid, ":employees", $IDs, -1, SQLT_CHR );
oci_bind_by_name($stid, ":result", $result, 40);
oci_bind_by_name($stid, ":resu", $resu, 100);
oci_execute($stid);
oci_free_statement($stid);
$result1['output'] = array("result" => $result);
return $result1;
else
{
//connection failed
echo 500;
}

它给了我一个错误：

警告：oci_new_collection((：ORA-22318:输入类型不是数组类型

致命错误：未捕获错误：调用布尔上的成员函数append((

有什么办法解决这个吗

提前感谢

Answer 1

非常感谢christopher，这对我的问题很有帮助，而且我似乎应该在创建新集合后放入pl/sql语句，如下所示：

$conn = $this->conn;
$IDs = oci_new_collection($conn, 'ARR_VAR');
for ($i = 0; $i < count($employees ); ++$i)
{
$IDs->append($employees[$i]);
}
$stmt =  "begin :resu :=addGroups(:empGroupId,:employees, :result ); end;";
$stid=oci_parse($conn,$stmt);
oci_bind_by_name($stid, ":empGroupId", $empGroupId);
oci_bind_by_name($stid, ":employees", $IDs, -1, OCI_B_NTY );
oci_bind_by_name($stid, ":result", $result, 40);
oci_bind_by_name($stid, ":resu", $resu, 100);
oci_execute($stid);
oci_free_statement($stid);
$result1['output'] = array("result" => $result);
return $result1;