#26738 [Com]: PHP-OCI binding error

ID: 26738 Comment by: lukasz608 at o2 dot pl Reported By: mbaranidharan at yahoo dot com Status: Assigned Bug Type: Feature/Change Request Operating System: * PHP Version: 4.3.4 Assigned To: tony2001 New Comment: 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". Previous Comments: ------------------------------------------------------------------------ [2004-03-05 08:05:08] mbaranidharan at yahoo dot com 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 ------------------------------------------------------------------------ [2004-01-21 23:49:13] mbaranidharan at yahoo dot com 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. ------------------------------------------------------------------------ [2004-01-21 10:05:13] tony2001@php.net 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. ------------------------------------------------------------------------ [2004-01-19 01:12:32] mbaranidharan at yahoo dot com 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 ------------------------------------------------------------------------ [2004-01-15 08:40:39] tony2001@php.net 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. ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/26738 -- Edit this bug report at http://bugs.php.net/?id=26738&edit=1