ID: 26738
User updated by: mbaranidharan at yahoo dot com
Reported By: mbaranidharan at yahoo dot com
Status: Assigned
Bug Type: OCI8 related
Operating System: *
PHP Version: 4.3.4
Assigned To: tony2001
New Comment:
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
Previous Comments:
------------------------------------------------------------------------
[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] [EMAIL PROTECTED]
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] [EMAIL PROTECTED]
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.
------------------------------------------------------------------------
[2004-01-13 23:16:56] mbaranidharan at yahoo dot com
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.
------------------------------------------------------------------------
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
