ORA-00932: inconsistent datatypes. How to read from a LONG column.

============================
General
============================
It is not possible to do a SELECT of a text from a field defined as LONG.

For example, USER_TRIGGERS.trigger_body as defined as LONG.
An attempt to run following SQLs would result in same error:

SELECT * FROM ALL_TRIGGERS WHERE trigger_body LIKE 'MY_TABLE';
or
SELECT TO_CHAR(trigger_body) FROM ALL_TRIGGERS;
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

============================
Solution
============================
Options A - Limited to sqlplus window
Options B - Limited to USER_TRIGGERS
Options C - General solution.

============================
Option A.
============================
in sqlplus window
set LONG 10000
SELECT trigger_name, trigger_body from USER_TRIGGERS;

============================
Option B.
============================
Use USER_SOURCE instead of USER_TRIGGERS

SELECT DISTINCT name FROM (

  SELECT  name, text 
    FROM  USER_SOURCE 

   WHERE type = 'TRIGGER'
     AND name IN (
     SELECT DISTINCT name
       FROM USER_SOURCE 
      WHERE type = 'TRIGGER'
        AND UPPER(text) LIKE '%SOME_TEXT%'

     )
)

============================
Option C.
============================
The general Solution - use BLOB instead of LONG.

CREATE TABLE ADMIN_ALL_TRIGGERS_SOURCE AS
SELECT owner, trigger_name, trigger_type, TO_LOB(trigger_body) AS trigger_body
FROM ALL_TRIGGERS;


SELECT TO_CHAR(trigger_body) FROM ADMIN_ALL_TRIGGERS_SOURCE 
WHERE UPPER(trigger_body) LIKE '%MY_TABLE%';

DROP TABLE ADMIN_ALL_TRIGGERS_SOURCE;