ORA-00932 – Error when I try to filter all_triggers

oracleoracle-11g-r2system-tables

I'm trying to execute the next query:

SELECT * FROM all_triggers t 
WHERE UPPER(trigger_name) like UPPER('%ABC%') 
AND UPPER(TRIGGER_BODY) LIKE '%DEF%';

But I get the following error: 

ORA-00932: inconsistent data types: expected CHAR has been obtained LONG

How can I avoid this error?

Best regards.

Best Answer

Maybe the simplest way is:

create table my_all_triggers as
select
    owner, trigger_name, trigger_type, triggering_event, table_owner, 
    base_object_type, table_name, column_name, referencing_names, when_clause, 
    status, description, action_type,
    to_lob(trigger_body) as trigger_body, --- <======== TO_LOB conversion
    crossedition, before_statement, before_row, after_row, after_statement,
    instead_of_row, fire_once, apply_server_only
from all_triggers
where upper(trigger_name) like upper('%ABC%') ;

select * from my_all_triggers t 
where upper(trigger_body) like '%DEF%';

You can find the other methods using PL/SQL or XML functions here: working with long columns

Or just use ALL_SOURCE to find the trigger:

SELECT * FROM all_source t 
WHERE type = 'TRIGGER' and UPPER(name) like UPPER('%ABC%') 
AND UPPER(text) LIKE '%DEF%';
Related Question