create or replace procedure chk_lowercase
is

-- create table lowercase_log (tableName varchar2(30),columnName Varchar2(30),foundcount number)

cursor c0 IS
	select table_name
	from   user_tables;

cursor c1 (table_name_in VARCHAR2) is
	SELECT  table_name,column_name	
	FROM    user_tab_columns
	WHERE   table_name = table_name_in and
	data_type = 'VARCHAR2';

sqlbuffer varchar2(2000);
mycount number;
cur1 INTEGER;
cur2 INTEGER;
ret  INTEGER;

BEGIN

cur1 := dbms_sql.open_cursor;
cur2 := dbms_sql.open_cursor;

FOR xx in C0 LOOP
 FOR x in c1 (xx.table_name)  LOOP
   sqlbuffer := 'SELECT COUNT(*) FROM '||x.table_name||' where '||
	x.column_name||' != upper('||x.column_name||')';


   dbms_sql.parse(cur1,sqlbuffer,dbms_sql.v7);
   dbms_sql.define_column(cur1,1,mycount);
   ret := dbms_sql.execute(cur1);

   IF dbms_sql.fetch_rows(cur1)>0 THEN
	dbms_sql.column_value(cur1,1,mycount);
   END IF;

   IF mycount > 0 THEN
      insert into lowercase_log values (x.table_name,x.column_name,mycount);
      commit;
   END IF;   
 END LOOP;
END LOOP;
END;
/
