Have you ever tried to use LONG columns in the WHERE clause of your SQL statement? Something
like ‘WHERE long_column like ‘%SEARCH%’? If so, you know this does not work! (You get ‘ORA-00932: inconsistent datatypes’) This tip shows how you can use database functions to avoid this limitation, and query by LONG columns anyway.

Consider the following table:

LONG_WHERE
==========
Name Null? Type
——————- ——– —-
LONG_ID NOT NULL NUMBER
LONG_DESC LONG

If you tried to select by the long column, you might use something like:

SQL> SELECT * FROM Long_Where
2 WHERE long_desc like ‘%SEARCH%’;
WHERE long_desc like ‘%SEARCH%’
*
ERROR at line 2:
ORA-00932: inconsistent datatypes

Which does not work! One way around this limitation is to write a database function ‘wrapper’ for the long column. This function would accept the PK of the table as an input parameter, and return the LONG column’s value, converted to a VARCHAR2. Here is an example of such a function:

CREATE OR REPLACE FUNCTION vc_desc(p_ID IN NUMBER) RETURN VARCHAR2 IS
v_desc VARCHAR2(2000);
v_Long LONG;
BEGIN
SELECT long_desc INTO v_Long
FROM Long_where
WHERE long_id = p_ID;
v_Desc := SUBSTR(v_Long,1,2000);
RETURN(v_Desc);
END;

Now you can use this function in the where clause instead of using the LONG column directly. For example:

SQL> SELECT long_id, long_desc
2 FROM Long_Where
3 WHERE vc_desc(long_id) like ‘%SEARCH%’
4 /

LONG_ID LONG_DESC
——— ——————————————————-
2 Another bunch of text to
place into a long value. Search for SEARCH2 somewhere

4 Search for SEARCH3 in here

You are limited to searching the first 2000 characters of the long column, but this is often good enough.

How to use an Oracle function to query a LONG datatype

Leave a Reply

Your email address will not be published. Required fields are marked *

+ forty six = fifty three