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！