SQL & PL/SQL

Hi Gurus,

background :

After a upgrade from 8.1.7 to 9.0.2 we found that some custom views have ',,' in them, but they are still showing in the 'VALID' status.

Problem:

In order to find all bad views I am trying to run something like

SQL/> select view_name from all_views where text like '%,,%'
but it fails with
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I tried running some thing like

----------------------------------------------------
set serveroutput on size 10000
declare
cursor c_dbv is
select owner,view_name,text
from all_views ;
searchstring varchar2(100) := ',,';
begin
for ct in c_dbv loop
dbms_output.put_line(ct.text);
if instr(lower(ct.text),lower(searchstring)) > 0 then
dbms_output.put_line(ct.owner||'.'||ct.view_name);
else
dbms_output.put_line ('No match' );
end if;
end loop;
end;
/
--------------------------------------------------------


it is also not helping ; could some one pleas suggest an alternative way


Regards