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.