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  name, text 
FROM  USER_SOURCE 
WHERE name IN (
      SELECT DISTINCT name
        FROM USER_SOURCE 
       WHERE type = 'TRIGGER'
         AND UPPER(text) LIKE '%MY_TABLE%')
  AND type = 'TRIGGER'
ORDER BY LINE;

============================
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;