I am trying to generate a spool file through below anonymous block in order to find out views on a particualar table.
declare cursor c1 is select view_name,text from users_view; rt c1%rowtype; begin open c1; loop fetch c1 into rt; exit when c1%notfound; dbms_output.put_line(rt.view_name || '|' || rt.text); end loop; end;
When I run it I get an error as "numeric or value errors", however if I remove text(LONG) column from cursor defination the block goes through without any error.
I understand that we can not use LONG data type in a where clause but is it that it can't be fetched in a cursor as well? If yes, what can be the alternative in this case?