As developers, we often query the data dictionary for details about views, triggers, and other objects. Oracle stores large text data like object definitions in LONG columns, which can’t be directly used in WHERE clauses. This limitation makes it challenging to filter results by specific text content.
SQL> select count(*) from all_triggers where trigger_body like '%ora%';
select count(*) from all_triggers where trigger_body like '%ora%'
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
In this post, we’ll cover practical ways to search and work with LONG columns in Oracle. I will show three diffrent ways to handle this.
First, let’s explore the TO_LOB function with the CTAS approach, which converts LONG data to CLOB for easier querying.
SQL> create table temp_triggers
2 as
3 select owner, trigger_name, to_lob(trigger_body) trigger_body from all_triggers;
Table created.
SQL> select count(*) from temp_triggers where trigger_body like '%ora%';
COUNT(*)
----------
3
SQL> drop table temp_triggers;
Table dropped.
For the second method, we can use a PL/SQL function, but the data length cannot exceed 32K for LONG data.
SQL> with
2 function get_trigger_body(p_owner varchar2, p_trigger_name varchar2)
3 return varchar2
4 is
5 v_return varchar2(32767);
6 begin
7 select trigger_body into v_return from all_triggers
8 where owner = p_owner and trigger_name = p_trigger_name;
9
10 return v_return;
11 end;
12 select count(*)
13 from all_triggers t
14 where trigger_name = USER and
15 get_trigger_body(owner, trigger_name) like '%ora%';
16 /
COUNT(*)
----------
3
The third method uses the DBMS_XMLGEN package. However, LONG values exceeding 4000 characters are truncated.
SQL> select
2 count(*)
3 FROM XMLTABLE('/ROWSET/ROW'
4 PASSING (SELECT DBMS_XMLGEN.GETXMLTYPE('select owner, trigger_name, trigger_body from all_triggers') FROM dual)
5 COLUMNS owner VARCHAR2(100) PATH 'OWNER',
6 trigger_name VARCHAR2(100) PATH 'TRIGGER_NAME',
7 trigger_body CLOB PATH 'TRIGGER_BODY'
8 )
9 WHERE trigger_body LIKE '%ora%';
COUNT(*)
----------
3
I hope this helps.