php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #40186 ORA-00932: inconsistent datatypes: expected CHAR got ARRAY
Submitted: 2007-01-21 13:51 UTC Modified: 2009-03-13 23:39 UTC
Votes:6
Avg. Score:4.0 ± 1.4
Reproduced:6 of 6 (100.0%)
Same Version:5 (83.3%)
Same OS:4 (66.7%)
From: tony at marston-home dot demon dot co dot uk Assigned: sixd (profile)
Status: Not a bug Package: OCI8 related
PHP Version: 5.2CVS-2008-11-11 OS: Windows XP
Private report: No CVE-ID: None
 [2007-01-21 13:51 UTC] tony at marston-home dot demon dot co dot uk
Description:
------------
I am using the latest OCI8 extension from PECL.

I have created a database table which contains a VARRAY user-defined type as follows:

CREATE OR REPLACE TYPE t_fav_food IS VARRAY(10) OF NUMBER(2);
CREATE TABLE x_person (
  person_id varchar2(8) NOT NULL,
  first_name varchar2(20) NOT NULL,
  last_name varchar2(30) NOT NULL,
  favourite_food t_fav_food,
  PRIMARY KEY  (person_id)
);

I can write a record containing a VARRAY type, but I am unable to read it as oci_fetch_array() fails with error ORA-00932.




Reproduce code:
---------------
<?php
$dbconn = ociLogon('tony', 'tony', '//localhost/xe') or die('unable to connect to database');
$query = "TRUNCATE TABLE x_person";
$stmt  = oci_parse($dbconn, $query);
$result = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS) or die('truncate failed');
$query = "INSERT INTO x_person (person_id, first_name, last_name, favourite_food) VALUES ('AJM','Tony','Marston', t_fav_food(1,3,5))";
$stmt  = oci_parse($dbconn, $query);
$result = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS) or die('insert #1 failed');
$query = "INSERT INTO x_person (person_id, first_name, last_name, favourite_food) VALUES ('FB','Fred','Bloggs', t_fav_food(2,4,6))";
$stmt  = oci_parse($dbconn, $query);
$result = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS) or die('insert #2 failed');
$query = "SELECT * FROM x_person";
$stmt  = oci_parse($dbconn, $query);
$result = oci_execute($stmt) or die('select failed');
while ($row = @oci_fetch_array ($stmt, OCI_ASSOC+OCI_RETURN_NULLS)) {
    $array[] = array_change_key_case($row, CASE_LOWER);
} // while
if ($error_array = oci_error($stmt)) {
    echo 'Error code: ' .$error_array['code'] ."\r\n";
    echo 'Error msg : ' .$error_array['message'] ."\r\n";
    exit();
} // if
echo 'Done';
?>


Expected result:
----------------
I expect the VARRAY column to be made available in my program so that it can be processed using the oci-collection methods, similar to the way CLOBs and BLOBs are handled.

This is what I can already do with the SET type in MySQL, and the ARRAY type in PostgreSQL.

Actual result:
--------------
The call to oci_fetch_array() fails with ORA-00932: inconsistent datatypes: expected CHAR got ARRAY. This means that I am unable to read table that contains a VARRAY column.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-01-22 07:37 UTC] tony2001@php.net
Use oci_bind_array_by_name() to fetch VARRAYs.
 [2007-01-22 11:19 UTC] tony at marston-home dot demon dot co dot uk
Your response is both unusable and impractical. The example in the manual for oci_bind_array_by_name() at http://www.php.net/manual/en/function.oci-bind-array-by-name.php shows how a VARRAY is built in memory from the contents of a database table. This is not how VARRAY fields are used in the real world. In my example I have a VARRAY field on a database table, not in memory, and I can write to this table without a problem, but I cannot read from it with gettingan error.

Unless you can show how my sample code can be made to work, just like the SET datatye does with MySQL and the ARRAY datatype does with PostgreSQL, this bug will remain open.

I expect, at the very least, to be able to read a record containing VARRAY fields into memory so that I can use the oci-collection methods to manipulate their contents. This would then make it similar to the way LOB fields are currently handled.

I do NOT want a method which requires me to select the VARRAY and non-VARRAY fields with separate queries.

Idealy I would like the contents of any VARRAY field returned to my program just like it is with TOAD or Oracle's SQL Developer, which is a string where each array is enclosed in parentheses. If that software can do it then why can't yours?
 [2007-01-22 11:53 UTC] tony2001@php.net
>Unless you can show how my sample code can be made to
>work, just like the SET datatye does with MySQL and the
>ARRAY datatype does with PostgreSQL, this bug will remain open.

As long as OCI API does not provide a certain way to distinguish between collections, objects and VARRAYS in OCIDefineByPos(), oci_bind_array_by_name() will remain the only method to fetch VARRAY data and this bug will remain bogus.
If you know OCI API and can propose a patch - feel free to contact me. But I do not see any way to do it using the functions available.
OCI API limitations are not PHP problems -> bogus.
 [2007-01-22 12:12 UTC] tony at marston-home dot demon dot co dot uk
Then I suggest you provide a working example, based on the code which I supplied, which shows how I can write to and read from a VARRAY column on a database table. The example in the manual does not do this, so it is totally useless.

Both TOAD and Oracle SQL Developer can handle VARRAY columns without a hitch, so don't tell me it can't be done.
 [2007-01-22 12:21 UTC] tony2001@php.net
This is not a support forum.
 [2007-01-22 13:31 UTC] tony at marston-home dot demon dot co dot uk
No, it's a place for reporting bugs, and this is a bug. I can write a database record containing a VARRAY column but I cannot read it back again. I can do this simple task with MySQL and PostgreSQL, but not with Oracle.

Until you can prove that this simple task is possible with the aid of sample code - and do not waste my time by referring to the manual as the code samples there are next to useless - then this will continue to be a bug.

If this is a problem caused by a deficiency in Oracle's OCI API then I suggest you contact Oracle (try christopher.jones at oracle dot com) and point out this serious deficiency.

Do TOAD and Oracle SQL Developer use the OCI API? They don't have any problems with the reading and writing of VARRAY fields, so if they can do it why can't you?
 [2007-01-22 13:46 UTC] tony2001@php.net
>I can write a database record containing a VARRAY column
> but I cannot read it back again. 
Of course you can and I've already pointed you to the function you can use for that.

>Until you can prove
Sorry, I'm not going to prove anything.
Open the documentation and learn how to use it yourself.

Please do not reopen the report unless you know how to implement it.
 [2007-01-22 15:07 UTC] tony at marston-home dot demon dot co dot uk
This is a bug, and until you are able to provide a workable solution then I insst that it remains a bug. The documenation does not show how to do what I want to do, therefore the documentation is inadequate.
 [2007-01-22 16:27 UTC] tony2001@php.net
Please re-read what I said in the previous message.
 [2007-01-22 18:53 UTC] tony at marston-home dot demon dot co dot uk
Please re-read what I said in *MY* last message. Just because you are unable to fix this does not mean that it is not an error. If the fault lies with the OCI API then please report this problem to the authors of that API and leave this bug as OPEN until then. If you keep marking it as BOGUS I will will keep reopening it for the simple reason that this is a GENUINE bug report, not a fictitious one.
 [2007-01-30 11:09 UTC] tony at marston-home dot demon dot co dot uk
Just because YOU are incapable of fixing this bug does *NOT* mean that it is not a bug and can be marked as "bogus".

There is a saying "if it ain't broke don't fix it", but there is no such thing as "if it can't be fixed it ain't broke".

If the fault lies with the OCI8 interface provided by Oracle then I insist that this bug report remain OPEN until such time as the OCI8 interface is fixed, in which case you will be able to make this PHP extension function properly. Then, and only then, will I consider this bug report to be closed.
 [2007-01-30 11:10 UTC] tony at marston-home dot demon dot co dot uk
Thus is a genuine bug. Do not mark it as bogus.
 [2007-01-30 11:23 UTC] tony2001@php.net
>but there is no such thing as "if it can't be fixed it ain't broke".

Sure there is.
PHP uses countless third-party libraries and bugs in those libraries are NOT PHP bugs and this is not a place to report them.
Such bugs fall to "Bogus" category, which means they are not PHP bugs and should be reported elsewhere.

>If the fault lies with the OCI8 interface provided by
>Oracle then I insist that this bug report remain OPEN
>until such time as the OCI8 interface is fixed

You can create a feature request when it's fixed in Oracle.

>Then, and only then, will I consider this bug report to be closed.

You may consider anything you like, but this problem still does not belong to PHP bug tracker.

Please consider also changing your attitude.
You probably misunderstood something, but "shut up and do what I say!" is not correct attitude to use with a person whom you're asking to help you to solve _your problem_.
 [2007-01-30 11:43 UTC] tony at marston-home dot demon dot co dot uk
But the point is that you are not helping me to solve this problem. Your attitude is that "I can't fix it, therefore it is not my problem".

I do not care in whose code the bug exists, the fact that I cannot read a record containing a VARRAY is a bug. I can do it with MySQL and PostgreSQL, but not Oracle. That is a serious deficiency.

What you realy need is a status that says "pending update from 3rd party library" instead of the ubiquitous "bogus", which implies that the bug reporter does not know what he is talking about.
 [2007-01-30 11:59 UTC] tony2001@php.net
>Your attitude is that "I can't fix it, therefore it is not my problem".

It's impossible to fix it using existing OCI calls, therefore this is not PHP problem.
It's THAT simple.

>What you realy need is a status that says "pending update
>from 3rd party library" instead of the ubiquitous 
>"bogus", which implies that the bug reporter does not know >what he is talking about.

"Bogus" means "no PHP bug here", which can be surely applied in this case.
Please don't change the report's status anymore.
 [2007-01-30 14:43 UTC] tony at marston-home dot demon dot co dot uk
Before you start complaining about my attitude you should look in the mirror. Your own attitude has been arrogant,  dismissive and unhelpful right from the start.

If you had said "Yes, this is a bug, but the problem lies with the OCI API which is supplied by Oracle. Let me contact them to get this sorted out" that would have been a satisfactory response.

Instead what do you do? You direct me to a totally inappropriate API, and when I point out that fact you complain that this is not a support forum. Then you say "I can't fix it therefore it ain't broke" and "fix it yourself". This type of attitude does not inspire me with any confidence in your abilities. There is a problem with the PHP to Oracle interface, yet you don't seem to care and refuse to lift a finger to help. Is it any wonder that I am totally pissed off?
 [2007-01-30 15:00 UTC] tony2001@php.net
>If you had said "Yes, this is a bug,"
There is no bug.
Missing features and bugs are absolutely different things.

>but the problem lies with the OCI API which is supplied by
>Oracle. Let me contact them to get this sorted out" that 
>would have been a satisfactory response.
Please let me decide what to do.
Telling me what to say and what to do in SUCH a way does not help in motivating me to help you.
If you think this problem should be reported to Oracle - please do it yourself.

Not PHP problem -> bogus.
If you have anything else to tell me - you're welcome to write me using my e-mail.
The report is bogus, so no sense to reopen it once again.
 [2007-01-30 15:26 UTC] tony at marston-home dot demon dot co dot uk
As far as any reasonable person would be concerened this most definitely is a bug. The Oracle database has a datatype called VARRAY which is similar to the SET datatype in MySQL and the ARRAY datatpe in PostgeSQL. The problem is that with your API I can write a record containing a VARRAY column, but it is not possible to read it back again. How stupid is that? Both MySQL and PostgreSQL can handle such a simple operation, so why can't Oracle? Are their API authors more competent?

Telling me that this is a "feature" and not a bug will do nothing to calm me down. Entirely the opposite in fact. It is your job to provide an API that allows PHP programmers to read and write all the datatypes supported by the Oracle database, which you have singularly failed to do. Blaming somebody else for your failure just shows how totally unprofessional you are.
 [2007-01-30 16:20 UTC] mgf@php.net
To both Tonys:

Please calm this discussion down, I think you are both getting on your high horses, stuck in a loop repeating your respective entrenched positions, and not even attempting to find a rapprochement.

To Tony Marston:

I understand that you have been unable to read a VARRAY the way that you expected to, but so far you have shown no sign of attempting to read it the way that tony2001 has suggested.  To this extent, I sympathise with his contention that this is a support problem and not a bug.  What you are looking for can be at most a feature request, but if tony2001 is right and the OCI API currently provides no way to do this, then this is indeed bogus.

I also see your point that the example in the manual illustrates a completely different usage from the one you are looking for, but tony2001's confident assertion that this is the call you need leads me to believe that it should also do the job you want.  (He is, after all, the current maintainer of the OCI8 extension!)

Can you please try to read your records using oci_bind_array_by_name() and oci_fetch(), and contribute back here with the results of your attempt?  I believe the code you want should look something like this:

    $stmt = oci_parse($dbconn, 'SELECT favourite_food FROM x_person');
    oci_bind_array_by_name($dbconn, $foods, 10, -1, SQLT_NUM); 
    oci_execute($stmt);
    while(oci_fetch($stmt)) {
       // here foods[] is an array of integers for current record
    }

@tony2001 -- have I got that about right?  (I'm unable to test as I do not have an up-to-date enough version of Oracle available.)
 [2007-01-30 16:53 UTC] tony at marston-home dot demon dot co dot uk
The example using oci_bind_array_by_name() as shown in the manual is of no ue to me bacuse, as I have already explained in ths bug report, it shows how to construct a VARRAY in memory from the contents of a database table. It does not show how to write to a read from a table which contains a VARRAY column. In this respect it is totally useless.

Your suggestion is also useless to me as it would require accessing the VARRAY column(s) separately from all non-VARRAY columns. This state of affairs does not exist with MySQL, PostgeSQL, nor any of the Oracle DB admin tools such as TOAD or Oracle SQL Developer. If those tools can manage to read and write VARRAY and non-VARRAY columns in a single operation, then why is it unrasonable for me to expect anything less from the OCI8 API?
 [2007-01-30 18:06 UTC] mgf@php.net
Don't be so blinking pedantic and obstinately blinkered!  The code I suggested was just a test sample to see if you can read the VARRAY at all.  If that works, I'm guessing you can combine additional columns in the SELECT with regular oci_define_by_name() calls to get more columns.  Please try it.  (As  I said, I can't as I don't currently have access to up-to-date enough versions of either Oracle or OCI8.)
 [2007-01-30 18:34 UTC] tony at marston-home dot demon dot co dot uk
So just because I want to do something with Oracle that I can already do wth MySQL and PostgreSQL you call me obstinate and pedantic? Is it unreasonable of me to want to read back from the database data which I have written? I think not.

Your suggestion is impractical for me as it would require a great deal of effort to build a simple SELECT statement, one where I would have to step through every column name and process it with oci_define_by_name(). I don't have to do this with LOBs, so why should I have to do it with collections?

Why is it not possible for the OCI8 extension to detect that a column is a VARRAY and return it as a collection object which I can then process with the OCI-Collection methods? It already has this option for LOB columns. Instead of me doing it in my code why can't the extension do it automatically instead of failing with error 932?
 [2007-01-30 18:53 UTC] tony2001@php.net
>So just because I want to do something with Oracle that I
>can already do with MySQL and PostgreSQL you call me
>obstinate and pedantic?

Oracle does not have SET or ENUM datatypes.
Oracle does not support LIMIT clause.
Oracle does not convert dates to strings and vice versa automatically.
Oracle's SQL dialect is different from the dialects used in MySQL & PostgreSQL.
There are much more things you CAN do in MySQL and/or Postgres, but CAN'T do in Oracle.
Do you think they are PHP bugs too?
 [2007-01-30 19:21 UTC] tony at marston-home dot demon dot co dot uk
MySQL's SET datatype and PostgreSQL's ARRAY datatype are identical in functionality to Oracle's VARRAY datatype in that they can hold arrays of values instead of single values.

It is not unreasonable of me to expect to be able to write to a SET/ARRAY/VARRAY column and read that data back again. I can do this with the TOAD admin tool and Oracle SQL Developer, so why can't I do it with the OCI8 extension?
 [2007-01-30 19:38 UTC] tony2001@php.net
You can and I've already pointed you out how to do it, but you don't want even to try.
 [2007-01-31 10:37 UTC] tony at marston-home dot demon dot co dot uk
When I try the following:

$stmt  = ociParse($dbconn, "SELECT favourite_food FROM x_person");
oci_bind_array_by_name($stmt, 'favourite_food', $foods, 10, -1, SQLT_NUM);
oci_execute($stmt);
while(oci_fetch($stmt)) {
   print_r($foods);
}

it fails with ORA-01036: illegal variable name/number.

When I try the following:

$stmt  = ociParse($dbconn, "SELECT :favourite_food FROM x_person");
oci_bind_array_by_name($stmt, ':favourite_food', $foods, 10, -1, SQLT_NUM);
oci_execute($stmt);
while(oci_fetch($stmt)) {
   print_r($foods);
}

it fails with ORA-01484: arrays can only be bound to PL/SQL statements.
 [2008-11-10 11:25 UTC] jani@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/


 [2008-11-10 14:08 UTC] tony at marston-home dot demon dot co dot uk
I have tried loading that snapshot, but I am still getting the same error. The call to oci_fetch_array() fails with "ORA-00932: inconsistent datatypes: expected CHAR got ARRAY"
 [2009-03-13 23:36 UTC] sixd@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

I see this bug has a long history and is still of interest. The current behavior is a limitation with the implementation of the open source OCI8 extension and is expected behavior. With respect, I am marking the bug as such. 

I will be tracking the base issue for future consideration in OCI8 or PDO_OCI.
 
I recognize the usefulness of such functionality. Unfortunately there are no short term plans known to me to update this area of code. All patches to add such functionality are welcome


 [2009-03-13 23:39 UTC] sixd@php.net
Sorry for lack of fine grained closing statuses. Bogus is the best available status.
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Mon Mar 25 05:01:26 2019 UTC