ORACLEµÄALL_VIEWSÊý¾Ý×Öµätext×Ö¶ÎlongÀàÐÍÎÊÌâ

½ñÌìͬÊÂraiseÒ»¸öСÎÊÌ⣬ÔõôÔÚALL_VIEWSÊý¾Ý×Öµätext×ֶβéÕÒlrf×Ö·û´®£º
etl@DWTEST> select owner,view_name from all_views where instr(text,'lrf') > 0;
select owner,view_name from all_views where instr(text,'lrf') > 0
                                                  *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Òòtext×Ö¶ÎΪlongÀàÐÍ£¬´ËÊý¾ÝÀàÐÍÏÞÖƽ϶࣬¹Ê¿ÉÒԲο¼ÏÂÃæµÄPL\SQL´úÂë¶Î½â¾ö£º
DECLARE
  v_instr_view_ddl NUMBER;
BEGIN
  FOR l_ctr IN (SELECT owner,view_name,
                       sign(dbms_lob.instr(DBMS_METADATA.get_ddl('VIEW',view_name,owner),'lrf')) AS instr_view_ddl
                  from all_views) LOOP
    IF l_ctr.instr_view_ddl = 1 THEN
      dbms_output.put_line(l_ctr.owner || '.' || l_ctr.view_name);
    END IF;
  END LOOP;
END;
ÓÐʲôÎÊÌâ¿ÉÁôÑÔàÞ£¡Good luck to U£¡