ID: 26738
Updated by: [EMAIL PROTECTED]
Reported By: mbaranidharan at yahoo dot com
-Status: Open
+Status: Assigned
Bug Type: OCI8 related
Operating System: *
PHP Version: 4.3.4
-Assigned To:
+Assigned To: tony2001
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[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..
------------------------------------------------------------------------
[2004-01-12 23:54:28] mbaranidharan at yahoo dot com
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);
------------------------------------------------------------------------
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
