Oracle DB: Convert LONG to VARCHAR on the fly

Use the following code in order to search in a LONG field:

SELECT * FROM sys.trigger$ /* table, not view */ 
WHERE sys.dbms_metadata_util.long2varchar( 4000 ,'SYS.TRIGGER$','ACTION#', ROWID) LIKE '%rvtrig_upd%';

Note, that for the statement above, you can not use ALL_TRIGGERS or DBA_TRIGGERS, because this function does not work with views (which do not have rowid)

Leave a Reply