When would isSearchable return false for an Oracle JDBC column?

Question

In what cases would a call to java.sql.ResultSetMetaData.isSearchable(int col) return false for an Oracle database? The documentation for the method doesn't really answer the question:

"Indicates whether the designated column can be used in a where clause."

I can think of only one case - when the column is the result of an aggregate function (in which case it would have to be part of a HAVING filter, not a WHERE filter).

Are there any other cases?

Answer 1

This is more related to the type of the column value, not how the column is selected. This information is stored in DatabaseMetaData#getTypeInfo(). The column SEARCHABLE can return either DatabaseMetaData.typePredNone (not searchable) or other values.

9: SEARCHABLE short => can you use "WHERE" based on this type:

• typePredNone - No support
• typePredChar - Only supported with WHERE .. LIKE
• typePredBasic - Supported except for WHERE .. LIKE
• typeSearchable - Supported for all WHERE ..

Here's a snippet which displays this information:

DatabaseMetaData databaseMetaData = connection.getMetaData();
ResultSet typeInfo = databaseMetaData.getTypeInfo();
System.out.println("type name                      | data type | searchable");
System.out.println("-------------------------------+-----------+------------");
while (typeInfo.next()) {
    String typeName = typeInfo.getString("TYPE_NAME");
    int dataType = typeInfo.getInt("DATA_TYPE");
    boolean searchable = typeInfo.getShort("SEARCHABLE") != DatabaseMetaData.typePredNone;
    System.out.printf("%-30s | %-9d | %-9s%n", typeName , dataType, searchable);
}

This yields something like following on a PostgreSQL 8.4 connection:

type name                      | data type | searchable
-------------------------------+-----------+------------
bool                           | -7        | true     
bytea                          | -2        | true     
char                           | 1         | true     
name                           | 12        | true     
int8                           | -5        | true     
bigserial                      | -5        | true     
int2                           | 5         | true     
int2vector                     | 1111      | true     
int4                           | 4         | true     
serial                         | 4         | true     
regproc                        | 1111      | true     
text                           | 12        | true     
(*snip*, it were about 270 rows, all TRUE by the way)

The data type is correlatable with ResultSetMetaData#getColumnType().