set serveroutput on
set serveroutput on size 1000000
DECLARE

vcount1 number := 0;
vcount2 number := 0;
v_tablename varchar2(32);
v_viewname varchar2(32);
vschema varchar2(30) := ‘&schema’;
vrole varchar2(30) := ‘&role’;
# — vprivilege varchar2(30) := ‘&privilege’;

BEGIN

vschema := UPPER(vschema);
vrole := UPPER(vrole);

for ctable in ( select owner, table_name from dba_tables where owner like vschema )
loop
vcount1:= vcount1+1;
v_tablename :=ctable.table_name;
begin
–dbms_output.put_line(ctable.owner||’.’||ctable.table_name);
execute immediate ‘grant select,update, insert,delete on ‘||ctable.owner||’.’||ctable.table_name||’ to ‘||vrole;
EXCEPTION — exception handlers begin
WHEN OTHERS THEN — handles all other errors
dbms_output.put_line(‘err_num: ‘||SQLCODE);
dbms_output.put_line(‘err_msg: ‘||SUBSTR(SQLERRM, 1, 100));
dbms_output.put_line(‘table_name: ‘||v_tablename);
vcount1:= vcount1-1;
END;
end loop;

dbms_output.put_line(‘—– ‘);
dbms_output.put_line(‘Counter Tables is: ‘||vcount1);
dbms_output.put_line(‘—– ‘);

for cview in ( select owner, view_name from dba_views where owner like vschema )
loop
vcount2:= vcount2+1;
v_viewname :=cview.view_name;
begin
–dbms_output.put_line(cview.owner||’.’||cview.view_name);
execute immediate ‘grant select on ‘||cview.owner||’.’||cview.view_name||’ to ‘||vrole;
EXCEPTION — exception handlers begin
WHEN OTHERS THEN — handles all other errors
dbms_output.put_line(‘err_num: ‘||SQLCODE);
dbms_output.put_line(‘err_msg: ‘||SUBSTR(SQLERRM, 1, 100));
dbms_output.put_line(‘view_name: ‘||v_viewname);
vcount2:= vcount2-1;
END;
end loop;
dbms_output.put_line(”);
dbms_output.put_line(‘Counter Views is: ‘||vcount2);
EXCEPTION — exception handlers begin
WHEN OTHERS THEN — handles all other errors
dbms_output.put_line(‘err_num: ‘||SQLCODE);
dbms_output.put_line(‘err_msg: ‘||SUBSTR(SQLERRM, 1, 100));
dbms_output.put_line(‘view_name: ‘||v_viewname);

END;
/