|
|
Posted by Tony Marston on 12/17/06 22:21
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:
[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]
If I access this table with the following code:
[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
[/code]
I get the error "ORA-00932: inconsistent datatypes, expected CHAR got ARRAY"
I have also tried the following:
[code]
$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
[/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?
--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
Navigation:
[Reply to this message]
|