To find the count occurred of a text within a string:

CREATE OR REPLACE FUNCTION count_occurs (text VARCHAR2, txtsearch VARCHAR2)
RETURN INTEGER
IS
v_count BINARY_INTEGER := 0;
v_pos BINARY_INTEGER := 1;
v_exists BINARY_INTEGER := 1;
BEGIN
WHILE v_exists != 0
LOOP
SELECT INSTR (text, txtsearch, v_pos)
INTO v_exists
FROM DUAL;

IF v_exists != 0
THEN
v_count := v_count + 1;
v_pos := v_exists + 1;
END IF;
END LOOP;

RETURN v_count;
END;
/