How compare NUMBER with ‘TYPE IS TABLE OF NUMBER’?

I have query, where need select ID and name by ID. First I check the ID using the function. In the function, I get several values that need to be compared with the ID. When I make a check it gives an error: ORA-00932: inconsistent datatypes: expected NUMBER got ARRAY

This query:

select o.id, o.name as value from table1 o, table2 r
where r.id IN (pkg.GET_VALUE(null, 1)) --error in this line
and r.id = o.id

Type which I use for save values from function:

 TYPE "ARRAY" as table of number(10)

How right compare NUMBER and TYPE ARRAY?

Answer

Use the MEMBER OF operator:

select o.id, o.name as value
from   table1 o
       INNER JOIN table2 r
       ON ( r.id = o.id )
where r.id MEMBER OF pkg.get_value( null, 1 )

(You can also use an INNER JOIN rather than using Oracle’s legacy comma join syntax)

Advertisement

Answer