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.