|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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.
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Thu Nov 21 16:01:29 2024 UTC |
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.)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.