Consider the following script:
CREATE TYPE t1 AS TABLE OF VARCHAR2(10);
/
CREATE TYPE t2 AS VARRAY(10) OF VARCHAR2(10);
/
CREATE TABLE t (
id NUMBER(10),
t1 t1,
t2 t2
)
NESTED TABLE t1 STORE AS t1_nt;
INSERT INTO t VALUES (1, NULL, NULL);
INSERT INTO t VALUES (2, t1('abc'), t2('abc'));
SELECT * FROM t WHERE 'abc' MEMBER OF t1;
SELECT * FROM t WHERE 'abc' MEMBER OF t2;
The output of the last two SELECT
statements is
ID T1 T2
-------------------
2 [abc] [abc]
ORA-00932: inconsistent datatypes: expected UDT got
SQL_XQMZQAMSETXZLGIEEEEBUTFWF.T2
A member_condition is a membership condition that tests whether an element is a member of a nested table. The return value is TRUE if expr is equal to a member of the specified nested table or varray.
What am I doing wrong?
It is a documentation bug, see this AskTom question and answer.
A workaround is to run this query:
SELECT *
FROM t
WHERE EXISTS (
SELECT 1 FROM TABLE(t2) WHERE column_value = 'abc'
)
I've written up a blog post showing emulations of all the multiset conditions and operators, in case someone else finds this useful.