ORA-00932 -当我尝试过滤all_triggers时出错

Question

我正在尝试执行下一个查询：

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

但我得到了以下错误：

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

如何避免此错误？

诚挚的问候。

Answer 1

也许最简单的方法是：

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%';

您可以在这里找到使用PL/SQL或XML函数的其他方法：使用长列

或者只需使用全_来源查找触发器：

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