ID: 26738
User updated by: mbaranidharan at yahoo dot com
Reported By: mbaranidharan at yahoo dot com
Status: Open
Bug Type: OCI8 related
Operating System: *
PHP Version: 4.3.4
New Comment:
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);
Previous Comments:
------------------------------------------------------------------------
[2004-01-12 19:27:36] [EMAIL PROTECTED]
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.
------------------------------------------------------------------------
[2004-01-04 23:21:17] mbaranidharan at yahoo dot com
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.
------------------------------------------------------------------------
[2004-01-02 11:00:55] [EMAIL PROTECTED]
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.
------------------------------------------------------------------------
[2003-12-29 04:56:56] mbaranidharan at yahoo dot com
Description:
------------
Trying to call a package in oracle which will return a parameter of
type table. When i try calling the procedure from PHP using OCI iam
getting wrong types or parameters.
Reproduce code:
---------------
$sql="declare var1 xxx.tbl_date;var2 xxx.tbl_varchar;";
$sql=$sql."begin xxx(3,:var1,:var2,:var3);end;";
$conn = OCILogon("usr","pwd","servername");
$stmt = OCIParse($conn,$sql);
OCIBindByname($stmt,'var1',$var1);
OCIBindByname($stmt,'var2',$var2);
OCIBindByname($stmt,'var3',$var3);
$rs = OCIExecute($stmt);
Expected result:
----------------
The result should be stored in the recordset which later can be
iterated and get the values from each array variable var1,2 and 3.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=26738&edit=1
