|
|
Posted by Andy Hassall on 12/17/06 23:14
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
[Back to original message]
|