How to retrieve an Oracle VARRAY

[Tony Marston]

I am using PHP 4.4.4 with Oracle 10g Express Edition on Windows XP.

I am trying to emulate in Oracle the SET datatype in MySQL and the ARRAY
datatype in PostgreSQL

I have the created a user-defined datatype as follows:

CREATE OR REPLACE TYPE t_fav_food IS VARRAY(10) OF NUMBER(2);
 
CREATE TABLE 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)
);
 

If I access this table with the following code:

$array = array();
$query = 'SELECT person_id, first_name, last_name, favourite_food FROM
person';
$statement = ociParse($conn, $query);
$result = ociExecute($statement);
while (ociFetchInto ($statement, $row,
OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS)) {
$array[] = array_change_key_case($row, CASE_LOWER);
} // while
 

I get the error "ORA-00932: inconsistent datatypes, expected CHAR got ARRAY"

I have also tried the following:

$array = array();
$query = 'SELECT person_id, first_name, last_name, :favourite_food FROM
person';
$statement = ociParse($conn, $query);
$fav_food = ociNewCollection($this->dbconnect, 'T_FAV_FOOD'));
ociBindByName($statement, ':favourite_food', $fav_food, -1, OCI_B_SQLT_NTY);
$result = ociExecute($statement);
while (ociFetchInto ($statement, $row,
OCI_ASSOC+OCI_RETURN_NULLS+OCI_RETURN_LOBS)) {
$array[] = array_change_key_case($row, CASE_LOWER);
} // while
 

but I still get exactly the same error. The PHP manual does not give any
practical examples, and neither does the Oracle manual. I've searched the
net all day without finding anything which is remotely useful. Can anyone
help?

--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org

[Andy Hassall]

On Sun, 17 Dec 2006 22:21:59 -0000, "Tony Marston" <to**@NOSPAM.demon.co.uk>
wrote:

>I am using PHP 4.4.4 with Oracle 10g Express Edition on Windows XP.

I am trying to emulate in Oracle the SET datatype in MySQL and the ARRAY
datatype in PostgreSQL

Bleh, but alright then.

>

CREATE OR REPLACE TYPE t_fav_food IS VARRAY(10) OF NUMBER(2);

CREATE TABLE 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)
);

but I still get exactly the same error. The PHP manual does not give any
practical examples, and neither does the Oracle manual. I've searched the
net all day without finding anything which is remotely useful. Can anyone
help?

As far as I can see, SELECTing VARRAYs is not supported by PHP's oci8
extension. It has some support for collection types as binds into PL/SQL calls,
but I can't spot any way to get it to work for defines.

Consider the below that shows it works for binds, at least:

Given that the following has been run against your definitions above:
insert into person values (1, 'Alice', 'McExample', t_fav_food(1, 2));

<?php
$conn = oci_connect('test', 'test', 'xe_excession');

$array = array();
$query = 'begin select favourite_food into :favourite_food from person where
person_id = 1; end;';
$statement = oci_parse($conn, $query);

$col = oci_new_collection($conn, 'T_FAV_FOOD');
oci_bind_by_name($statement, 'FAVOURITE_FOOD', $col, -1, SQLT_NTY);

$result = ociexecute($statement);

for ($i = 0; $i < $col->size(); $i++)
{
print $col->getElem($i) . "<br>";
}
?>

Output:
1
2

I'm using the newer function names since I'm on PHP5 and using the latest oci8
extension; I don't know what state of collections support was present in the
version of oci8 bundled with 4.4.4 - you can upgrade the oci8 extension
separately from PECL (and this is strongly recommended given the large overhaul
it got in version 1.1).

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[Tony Marston]

"Andy Hassall" <an**@andyh.co.ukwrote in message
news:q1********************************@4ax.com...

On Sun, 17 Dec 2006 22:21:59 -0000, "Tony Marston"
<to**@NOSPAM.demon.co.uk>
wrote:

>>I am using PHP 4.4.4 with Oracle 10g Express Edition on Windows XP.

I am trying to emulate in Oracle the SET datatype in MySQL and the ARRAY
datatype in PostgreSQL

Bleh, but alright then.

>>

CREATE OR REPLACE TYPE t_fav_food IS VARRAY(10) OF NUMBER(2);

CREATE TABLE 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)
);

but I still get exactly the same error. The PHP manual does not give any
practical examples, and neither does the Oracle manual. I've searched the
net all day without finding anything which is remotely useful. Can anyone
help?

As far as I can see, SELECTing VARRAYs is not supported by PHP's oci8
extension. It has some support for collection types as binds into PL/SQL
calls,
but I can't spot any way to get it to work for defines.

Consider the below that shows it works for binds, at least:

Given that the following has been run against your definitions above:
insert into person values (1, 'Alice', 'McExample', t_fav_food(1, 2));

<?php
$conn = oci_connect('test', 'test', 'xe_excession');

$array = array();
$query = 'begin select favourite_food into :favourite_food from person
where
person_id = 1; end;';
$statement = oci_parse($conn, $query);

$col = oci_new_collection($conn, 'T_FAV_FOOD');
oci_bind_by_name($statement, 'FAVOURITE_FOOD', $col, -1, SQLT_NTY);

$result = ociexecute($statement);

for ($i = 0; $i < $col->size(); $i++)
{
print $col->getElem($i) . "<br>";
}
?>

Output:
1
2

I'm using the newer function names since I'm on PHP5 and using the latest
oci8
extension; I don't know what state of collections support was present in
the
version of oci8 bundled with 4.4.4 - you can upgrade the oci8 extension
separately from PECL (and this is strongly recommended given the large
overhaul
it got in version 1.1).

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Unfortunately that does not work as $col>size() always returns 0, even when
I know the field is not empty.

Even if this approach did work I do not like the idea of having to retrieve
the VARRAY column independently of all the other columns.

I know that I can write into this column using the name of the user-defined
type as a function name, as in

UPDATE person SET favourite_food=T_FAV_FOOD('1','2','4','10') WHERE
person_id='FB'

so would it not be possible to write a function which would do the reverse,
i.e. convert the array into a comma-delimited string? This would then enable
me to read the table with

SELECT person_id, first_name, last_name, T_FAV_FOOD_R(favourite_food) FROM
person WHERE person_id='FB'

Hopefully that should then get around the "ORA-00932: inconsistent
datatypes, expected CHAR got ARRAY"
error. What do you think?

--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org

[Andy Hassall]

On Mon, 18 Dec 2006 11:37:15 -0000, "Tony Marston" <to**@NOSPAM.demon.co.uk>
wrote:

>Unfortunately that does not work as $col>size() always returns 0, even when
I know the field is not empty.

Works for me, as demonstrated above, but as I said YMMV depending on the
version of the oci8 extension you're using.

>Even if this approach did work I do not like the idea of having to retrieve
the VARRAY column independently of all the other columns.

Indeed, it's not suitable for your needs, just a demonstration of the limited
support the oci8 extension does have in this area.

>I know that I can write into this column using the name of the user-defined
type as a function name, as in

UPDATE person SET favourite_food=T_FAV_FOOD('1','2','4','10') WHERE
person_id='FB'

so would it not be possible to write a function which would do the reverse,
i.e. convert the array into a comma-delimited string? This would then enable
me to read the table with

SELECT person_id, first_name, last_name, T_FAV_FOOD_R(favourite_food) FROM
person WHERE person_id='FB'

Hopefully that should then get around the "ORA-00932: inconsistent
datatypes, expected CHAR got ARRAY"
error. What do you think?

I think it's an abomination ;-) but that's what I think about varrays as table
columns in the first place. Without submitting a patch to the oci8 extension to
support defining varrays, then it's probably the closest you'll get, and should
work.

Probably the main reason why you're having trouble here is that hardly anyone
uses nested tables/varray columns in Oracle in the first place; that varray
column should be a separate table. Since they're not generally used like that,
then interface support in many languages is poor (as in there's full support on
Oracle's interface side, but it's not been connected in the PHP oci8
extension). But I do understand why you're trying to do this.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool