from:"mbaranidharan at yahoo dot com"

#26738 [Asn]: PHP-OCI binding error

2004-03-05 Thread mbaranidharan at yahoo dot com

 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=26738edit=1

#26738 [Asn]: PHP-OCI binding error

2004-01-21 Thread mbaranidharan at yahoo dot com

 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=26738edit=1

#26738 [Fbk-Opn]: PHP-OCI binding error

2004-01-18 Thread mbaranidharan at yahoo dot com

 ID:   26738
 User updated by:  mbaranidharan at yahoo dot com
 Reported By:  mbaranidharan at yahoo dot com
-Status:   Feedback
+Status:   Open
 Bug Type: OCI8 related
 Operating System: *
 PHP Version:  4.3.4
 New Comment:

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


Previous Comments:


[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
7499ALLEN   SALESMAN
7698BLAKE   MANAGER
7900JAMES   CLERK
7566JONES   MANAGER
7934MILLER  CLERK
7844TURNER  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

#26738 [Bgs-Opn]: PHP-OCI binding error

2004-01-13 Thread mbaranidharan at yahoo dot com

 ID:   26738
 User updated by:  mbaranidharan at yahoo dot com
 Reported By:  mbaranidharan at yahoo dot com
-Status:   Bogus
+Status:   Open
 Bug Type: OCI8 related
 Operating System: *
 PHP Version:  4.3.4
 New Comment:

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.


Previous Comments:


[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
7499ALLEN   SALESMAN
7698BLAKE   MANAGER
7900JAMES   CLERK
7566JONES   MANAGER
7934MILLER  CLERK
7844TURNER  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);



[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

#26738 [Fbk-Opn]: PHP-OCI binding error

2004-01-04 Thread mbaranidharan at yahoo dot com

 ID:   26738
 User updated by:  mbaranidharan at yahoo dot com
 Reported By:  mbaranidharan at yahoo dot com
-Status:   Feedback
+Status:   Open
 Bug Type: OCI8 related
 Operating System: windows/linux
 PHP Version:  4.3.4
 New Comment:

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.


Previous Comments:


[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=26738edit=1

#26738 [NEW]: PHP-OCI binding error

2003-12-29 Thread mbaranidharan at yahoo dot com

From: mbaranidharan at yahoo dot com
Operating system: windows/linux
PHP version:  4.3.4
PHP Bug Type: OCI8 related
Bug description:  PHP-OCI binding error

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 bug report at http://bugs.php.net/?id=26738edit=1
-- 
Try a CVS snapshot (php4):  http://bugs.php.net/fix.php?id=26738r=trysnapshot4
Try a CVS snapshot (php5):  http://bugs.php.net/fix.php?id=26738r=trysnapshot5
Fixed in CVS:   http://bugs.php.net/fix.php?id=26738r=fixedcvs
Fixed in release:   http://bugs.php.net/fix.php?id=26738r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=26738r=needtrace
Need Reproduce Script:  http://bugs.php.net/fix.php?id=26738r=needscript
Try newer version:  http://bugs.php.net/fix.php?id=26738r=oldversion
Not developer issue:http://bugs.php.net/fix.php?id=26738r=support
Expected behavior:  http://bugs.php.net/fix.php?id=26738r=notwrong
Not enough info:http://bugs.php.net/fix.php?id=26738r=notenoughinfo
Submitted twice:http://bugs.php.net/fix.php?id=26738r=submittedtwice
register_globals:   http://bugs.php.net/fix.php?id=26738r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=26738r=php3
Daylight Savings:   http://bugs.php.net/fix.php?id=26738r=dst
IIS Stability:  http://bugs.php.net/fix.php?id=26738r=isapi
Install GNU Sed:http://bugs.php.net/fix.php?id=26738r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=26738r=float

#26738 [Asn]: PHP-OCI binding error

#26738 [Asn]: PHP-OCI binding error

#26738 [Fbk-Opn]: PHP-OCI binding error

#26738 [Bgs-Opn]: PHP-OCI binding error

#26738 [Fbk-Opn]: PHP-OCI binding error

#26738 [NEW]: PHP-OCI binding error

6 matches

Site Navigation

Mail list logo

Footer information