|
|
Posted by Tony Marston on 12/18/06 11:37
"Andy Hassall" <andy@andyh.co.uk> wrote in message
news:q1jbo29dpfge40jal90bh4ah12rfsc4jlo@4ax.com...
> On Sun, 17 Dec 2006 22:21:59 -0000, "Tony Marston"
> <tony@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.
>
>>[code]
>>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)
>>);
>>[/code]
>>
>>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 :: andy@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
Navigation:
[Reply to this message]
|