Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Using an Oracle CLOB in the WHERE clause of a SELECT query via JDBC?

    Anyone know how to use a CLOB in the WHERE clause of a SELECT statement in Oracle? I'm trying to do a query by CLOB value via JDBC. For most databases a prepared statement like:

    SELECT non_clob_column FROM table_with_clob_column WHERE clob_column = ?

    would work fine. One would fill in the ? using a setString() method and get back the result as intended. With Oracle though, you get a datatype mismatch error.

    I've found on the web plenty of information on how to write to and read from Oracle CLOBs via JDBC, but I haven't been able to find how to compare them for equality in WHERE clauses. Anyone know?

    -Craig

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Using an Oracle CLOB in the WHERE clause of a SELECT query via JDBC?

    Originally posted by cvieregg
    Anyone know how to use a CLOB in the WHERE clause of a SELECT statement in Oracle? I'm trying to do a query by CLOB value via JDBC. For most databases a prepared statement like:

    SELECT non_clob_column FROM table_with_clob_column WHERE clob_column = ?

    would work fine. One would fill in the ? using a setString() method and get back the result as intended. With Oracle though, you get a datatype mismatch error.

    I've found on the web plenty of information on how to write to and read from Oracle CLOBs via JDBC, but I haven't been able to find how to compare them for equality in WHERE clauses. Anyone know?

    -Craig
    You cannot search in clob. But you can convert clob to char (max 4000 chars).

    SELECT non_clob_column FROM table_with_clob_column WHERE to_char(clob_column) = ?

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Re: Using an Oracle CLOB in the WHERE clause of a SELECT query via JDBC?

    Originally posted by ika
    You cannot search in clob. But you can convert clob to char (max 4000 chars).

    SELECT non_clob_column FROM table_with_clob_column WHERE to_char(clob_column) = ?
    Thanks a lot for the info, ika. We'll use your workaround for now.

    Let me know if anyone thinks of another workaround though, as it looks like that one will fail if ANY of the entries in the table are more than 4k. Also, I think it will have to do a full table scan, so it won't scale well.

  4. #4
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Using an Oracle CLOB in the WHERE clause of a SELECT query via JDBC?

    Originally posted by cvieregg
    Thanks a lot for the info, ika. We'll use your workaround for now.

    Let me know if anyone thinks of another workaround though, as it looks like that one will fail if ANY of the entries in the table are more than 4k. Also, I think it will have to do a full table scan, so it won't scale well.
    1. No, query will never fail. Large values than 4K will be trimmed to 4K.
    2. You could create functional index on clob column to prevent full table scans. For example:

    CREATE INDEX IDX_CHAR_CLOB ON MYTABLE
    (to_char("MY_CLOB_COLUMN") ASC)
    Last edited by ika; 04-16-04 at 18:37.

  5. #5
    Join Date
    Apr 2004
    Posts
    4
    Ah, thanks again! That certainly does improve things, and should be sufficient for our needs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •