Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.php.net/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to "Open".

Thank you for your interest in PHP.

Iam trying to call a package in oracle 9i db which returns me out parameters of type table.When i try to bind the out parameter and execute iam getting error. This error occured bcoz table type in oracle again internally an array which php is not able to identify.I get the same error with both ORA and OCI.

Can you tell us what error exactly do you get?
Are there any error messages? Currently I can't understand what are you talking about, sorry.

Below i have given the package and package body created in oracle database.
*********************************************************
CREATE PACKAGE Employee_Pkg 
AS 
TYPE tblEmpNo IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER; 
TYPE tblEName IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; 
TYPE tblJob IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER; 

PROCEDURE EmployeeSearch 
   (i_EName IN VARCHAR2, 
    o_EmpNo OUT tblEmpNo, 
    o_EName OUT tblEName, 
    o_Job OUT tblJob); 
END Employee_Pkg;
/
CREATE PACKAGE BODY Employee_Pkg 
AS 
PROCEDURE EmployeeSearch 
   (i_EName IN VARCHAR2, 
    o_EmpNo OUT tblEmpNo, 
    o_EName OUT tblEName, 
    o_Job OUT tblJob) 
IS
CURSOR cur_employee (curName VARCHAR2) IS 
      SELECT empno, 
             ename, 
             job 
      FROM emp 
      WHERE UPPER(ename) LIKE '%' || UPPER(curName) || '%' 
      ORDER BY ename; 
      RecordCount NUMBER DEFAULT 0;
BEGIN 
      FOR curRecEmployee IN cur_employee(i_EName) LOOP 
         RecordCount:= RecordCount + 1; 
         o_EmpNo(RecordCount):= curRecEmployee.empno; 
         o_EName(RecordCount):= curRecEmployee.ename; 
         o_Job(RecordCount):= curRecEmployee.job; 
      END LOOP; 
   END EmployeeSearch; 
END Employee_Pkg;
/
*********************************************************
Below is my sql block calling the package

SQL="declare 
    o_EmpNo Employee_pkg.tblEmpNo; 
    o_EName Employee_pkg.tblEName; 
    o_Job Employee_pkg.tblJob; 
Begin Employee_pkg.EmployeeSearch('e',:o_EmpNo,:o_EName,:o_Job);
End;"
when i call this package from php using OCI i should get the results as below
o_EmpNo o_EName o_job
7499    ALLEN   SALESMAN
7698    BLAKE   MANAGER
7900    JAMES   CLERK
7566    JONES   MANAGER
7934    MILLER  CLERK
7844    TURNER  SALESMAN

but iam getting error msg as
Warning: ociexecute(): OCIStmtExecute: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EMPLOYEESEARCH' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EMPLOYEESEARCH' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EMPLOYEESEARCH' ORA-06550: line 1, column 7: PL/SQL: Statement ignored in c:\apache group\apache\htdocs\

here is the code that i executed

$sql = "declare o_EmpNo Employee_pkg.tblEmpNo;o_EName Employee_pkg.tblEName;o_Job Employee_pkg.tblJob;"; 
	$sql = "Begin Employee_pkg.EmployeeSearch('e',:o_EmpNo,:o_EName,:o_Job);End;";

	$conn	= OCILogon("scott","tiger");
	$stmt	= OCIParse($conn,$sql);

   OCIBindByname($stmt,':o_Empno',$o_array1);
   OCIBindByname($stmt,':o_EName',$o_timestamp);
   OCIBindByname($stmt,':o_Job',$o_array2);
   OCIExecute($stmt);
      
   OCILogoff($conn);

First, you need to allocate new descriptor with ocinewdescriptor(). Your stored proc will return cursor, using this descriptor. After this your should fetch data from this cursor, as you usually do with query results.
And second:
after these two lines your variable contains only "Begin.... etc.". Use $sql .= in second line.
---
#1 sql = "declare o_EmpNo Employee_pkg.tblEmpNo;o_EName
Employee_pkg.tblEName;o_Job Employee_pkg.tblJob;"; 
#2 $sql = "Begin Employee_pkg.EmployeeSearch('e',:o_EmpNo,:o_EName,:o_Job);End;";
---

This doesn't look like a bug, bogusifying it..

my procedure does not return any cursor here it will return a pl/sql table. Its using cursor to fetch data and populate into pl/sql table. Basically the error come when i try to bind an array to variable. Look at my procedure below
PROCEDURE EmployeeSearch 
   (i_EName IN VARCHAR2, 
    o_EmpNo OUT tblEmpNo, 
    o_EName OUT tblEName, 
    o_Job OUT tblJob); 
END Employee_Pkg;
it takes in one parameter and returns 3 out parameters which is of table type not cursor.

Aha, I got your point at least. It seems, that you need to use ocinewcollection() and other ocicoll*() functions to work with these user-defined types.

but in my case iam trying to return a plsql table type from the procedure. So i cant create a type of plsql table outside which oracle wont allow.
So can't use ocinewcollection and ocicoll* functions.
pls try to create the packages i have send and check.
-
Thanks

This can be definitely done with collections.
First, you need to create name type:
create type employee(id INTEGER, name VARCHAR2, job VARCHAR2); -- for example
then in PL/SQL:
PROCEDURE EmployeeSearch 
   (i_EName IN VARCHAR2, 
    employee_var OUT employee
    ) 
IS
BEGIN 
--seeking for 
--matching emplyees...
   employee_var := employee(123,"name","job");
END EmployeeSearch;

and in PHP you should do smthng like this:
<?php
$connection = oci_connect("user","pass","server");
$statement = oci_parse($connection,"
			declare
			Begin
				Package.EmployeeSearch('e',:empl);
			End;
		");
	
$empl = oci_new_collection($connection,"EMPLOYEE"));
oci_bind_by_name($statement,":var1", $var1, -1, OCI_B_SQLT_NTY);

oci_execute($statement);
echo $empl->getelem(0);
//etc...
?>

Currently oci_collection_element_get() doesn't support subcollections, so I assign this bug to myself.
Will add this possibility soon.

according to my requirement iam not supposed to modify any existing package or procedure.It is strict that my out parameter is a plsql table type. When u try creating a type like that below is the result.

SQL> create type tblEmpNo IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
  2  /

Warning: Type created with compilation errors.

SQL> show error
Errors for TYPE TBLEMPNO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/18     PLS-00355: use of pl/sql table not allowed in this context

one more thing i would like to say is bcoz of this prob i created a wrapper for this package in oracle which will take in plsql table and return me a ref cursor which works fine with PHP. But this i have kept is as a temporary solution. I need to solve it from PHP front. 

In OCIBindByname binding an array type is giving the problem. I checked in the other forums and i saw the same problem with other people too and this is open from year 2000 and is not solved.

Hi is there a solution available for this prob. Pls let me know.
I have a c++ code which uses oracle OCI library to call the package and get me the result as array. Is there any detailed example available to create php extension in c++ which returns a array. Pls let me know.
Thanks

Hello,

I have the code like this:

"
$stmt = ociParse($conn,  "BEGIN ".
                          "  UTL_PC_CA_PATCH.patch_raport('TST', 21511, :data); ".
                          "END;");
$data = ocinewcollection($conn, 'VARCHAR2TABTYPE');
ociBindByName($stmt, ":data", &$data, -1, OCI_B_NTY);
ociExecute($stmt, OCI_DEFAULT);
echo "SIZE: ".$data->size."<BR>";
echo $data->getelem(1);
ocilogoff($conn);
"

The result is:
"
SIZE: 
"

Can you explain me why $data is empty?
1. Type VARCHAR2TABTYPE is defined on users schema.
2. I'm sure that PL/SQL procedure returns table containg several elements in ":data".

A some more:

The function names in code above comes from PHP4, because i tried it also with PHP4.

Please *do not* add any comments to the report if they are not related to the current bug. Either create a new report (hint: use var_dump() in your code instead of echo()) or ask your questions somewhere else. 
This system is not a support forum and this report is not a forum topic.

Absolutely agree this is a serious problem. The way I am using to overcome this - temporary tables. But this requires another statement/cursor/parse and overhead. Many other languages HAVE the possibility. I cant understand why PHP doesn't. There is a great need for the way to pass structured data directly from PL/SQL environment to PHP (more preferrable, to native PHP array, but not fetchable Oracle-native). If I get such volume of structured data that can be allocated in RAM, why sholud I use cursors and other DBMS-related features? Why cant I get it in native PHP data type and forget about DBMS and release its resources?

Fixed since OCI8 release 1.2 and PHP release 5.1.2.
You can use oci_bind_array_by_name() to bind PL/SQL arrays.