Member Menu
 Monthly JBoss newsletter:
Hibernate Books

Oracle table types (varray) in where clause

This is extension of Use Oracle Object Types with Hibernate (

As per the article I created a table type in oracle as follows


and created a class to map it with hibernate as follows

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.sql.ARRAY;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class IList implements UserType{

    private static final int[] SQL_TYPES = { Types.ARRAY };

    public int[] sqlTypes() {
        return SQL_TYPES;

    public Class returnedClass() {
        return ARRAY.class;

    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == y)
            return true;
        else if (x == null || y == null)
            return false;
            return x.equals(y);

    public Object nullSafeGet(ResultSet resultSet, String[] names, Object owner)
            throws HibernateException, SQLException {
        ARRAY result = null;
        ARRAY array = (ARRAY) resultSet.getArray(names[0]);
        if (!resultSet.wasNull())
            result = array;
        return result;

    public void nullSafeSet(PreparedStatement statement, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null)
            statement.setNull(index, SQL_TYPES[0]);
        else {
            statement.setArray(index, (ARRAY) value);

    public Object deepCopy(Object value) throws HibernateException {
        return value;

    public boolean isMutable() {
        return false;


But for me the main issue was how I can use a column of type ILIST in a table in where clause of a select query. For that I wrote following custom oracle funstion (its just a prototype)

CREATE OR REPLACE FUNCTION doesContain (collection ILIST, val varchar2) RETURN NUMBER
i INTEGER := 1;
flag NUMBER := 0;
    if collection.count()> 0 then
        if(collection(i) = val) then 
           flag := 1;
        end if;
        Exit when (flag = 1 or i = collection.count());
        i := i+1;              
        END LOOP;   
    end if;
    RETURN flag; 
END doesContain;

and then used this function in HQL like any other oracle function and it worked. For example

select bu.buName, bu.buSicCodes from BusinessUnit bu where doescontain(bu.buSicCodes, 1000)=1

Here buSicCodes is of type IList.

© Copyright 2006, Red Hat Middleware, LLC. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc. [Privacy Policy]