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:
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.
Previous Comments:
------------------------------------------------------------------------
[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.
------------------------------------------------------------------------
[2004-01-13 13:20:02] [EMAIL PROTECTED]
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..
------------------------------------------------------------------------
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
