-- @searchlong
--
-- Description: Columns - Search Long Column
--
--
accept var1 prompt 'String to search: '
DECLARE
    cid INTEGER;
    nRowsReturned integer;
    lLongField varchar2(2000);
    real_length integer;
    num_rows integer;
    search_string varchar2(255):='&&var1';
    name varchar2(255);
    var1 integer:=0;
    printname boolean:=false;
BEGIN
	dbms_output.enable(1000000);
	cid := DBMS_SQL.OPEN_CURSOR;
	DBMS_SQL.PARSE(cid, 'select text,view_name from dba_views where view_name like ''MFDFOLDER_%''', dbms_sql.v7);
	nRowsReturned := dbms_sql.execute_and_fetch(cid);
	dbms_sql.define_column_long (cid,1);
	dbms_sql.define_column(cid,2,name, 200); 
	while DBMS_SQL.fetch_rows(cid) > 0 loop
	   printname :=false;
      num_rows := num_rows + 1 ;
      dbms_sql.column_value_long(cid,1,2000,0,lLongField,real_length);
		dbms_sql.column_value(cid, 2, name); 
      if real_length> 0 then 
	      if real_length > 2000  then
	         dbms_output.put_line('Line longuer than 2000 '||to_char(real_length));
	      end if;
	      if instr(lLongField,search_string) > 0 then
	         printname :=true;
	         	--dbms_output.put_line ('TRUE SHE is cute');
	         	--dbms_output.put_line(to_char(real_length));
   			var1:= floor(real_length/255)+1;
   			for i in 1..var1 
   			loop
   				dbms_sql.column_value(cid, 2, name);
   			--dbms_output.put_line(rtrim(substr(lLongField,(i-1)*255+1,255)));
   			end loop;
   			--dbms_output.put_line('-------------------------------------------------');
	   	end if;
      end if;
      if printname then
       	dbms_output.put_line(name);
      end if;
	end loop;
	DBMS_SQL.CLOSE_CURSOR(cid);
END;
/