Compare string from long column

Why it does not work?

Direct compare of long as varchar is not allowed in oracle 10 and previous.

What is that you see?

So if you do this

SELECT *
FROM all_triggers
WHERE trigger_name = 'Trigger_Name' AND trigger_body LIKE '%content%';

Here the long column is trigger_body

You will see a error like this
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

How do you solve this?

To solve this you need to do this.
Create a function like this. What this does is create a PL SQL function that does the compare for you, if you pass in the trigger name and the content that you are looking to find in there.

CREATE OR REPLACE FUNCTION t_instr (
p_trigname IN VARCHAR2,
p_lookfor IN VARCHAR2
)
RETURN NUMBER
AS
l_str LONG;
BEGIN
SELECT trigger_body
INTO l_str
FROM all_triggers
WHERE trigger_name = p_trigname;
RETURN INSTR (l_str, p_lookfor);
END;
/

And then do this to find if the value is present or not

SELECT decode(t_instr ('Trigger_name', 'Content that you are looking for'),0,'NOT Present','Present') status
FROM DUAL;

Finally do this to kick the function out.

DROP FUNCTION t_instr;

What is the useful link!

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:55212348054