set serveroutput on

--TABLAS----------------------------------------------------------------------
declare 
	err_num          NUMBER;
	err_msg          VARCHAR2 (255);

	CURSOR s_cur IS
	SELECT table_name 
	FROM DBA_TABLES where owner = upper('&1');

	sqlstr  VARCHAR2(200); 
BEGIN
	FOR s_rec IN s_cur LOOP
		sqlstr := 'GRANT SELECT ON &1..' || s_rec.table_name || ' TO &2';
		EXECUTE IMMEDIATE sqlstr;
	END LOOP; 
EXCEPTION
	WHEN OTHERS THEN
		err_num := SQLCODE;
		err_msg := SQLERRM;
		DBMS_OUTPUT.PUT_LINE('Error en bloque de tablas.'|| substr('ERROR:' || ERR_NUM || '-' || ERR_MSG, 1, 255));
END;
/

--VISTAS----------------------------------------------------------------------
declare 
	err_num          NUMBER;
	err_msg          VARCHAR2 (255);
	CURSOR s_cur2 IS
	SELECT VIEW_NAME 
	FROM DBA_VIEWS
	WHERE OWNER = upper('&1');

	sqlstr  VARCHAR2(200); 
 
BEGIN
	FOR s_rec IN s_cur2 LOOP
		sqlstr := 'GRANT SELECT ON &1..' || s_rec.VIEW_name || ' TO &2';
		EXECUTE IMMEDIATE sqlstr;	
	END LOOP; 
EXCEPTION
	WHEN OTHERS THEN
		err_num := SQLCODE;
		err_msg := SQLERRM;
		DBMS_OUTPUT.PUT_LINE('Error en bloque de vistas.'|| substr('ERROR:' || ERR_NUM || '-' || ERR_MSG, 1, 255));
END;
/


--VISTAS MATERIALIZADAS----------------------------------------------------------------------
declare 
	err_num          NUMBER;
	err_msg          VARCHAR2 (255);
	CURSOR s_cur2 IS
	SELECT MVIEW_NAME 
	FROM DBA_MVIEWS
	WHERE OWNER = upper('&1');

	sqlstr  VARCHAR2(200); 
 
BEGIN
	FOR s_rec IN s_cur2 LOOP
		sqlstr := 'GRANT SELECT ON &1..' || s_rec.MVIEW_NAME || ' TO &2';
		EXECUTE IMMEDIATE sqlstr;	
	END LOOP; 
EXCEPTION
	WHEN OTHERS THEN
		err_num := SQLCODE;
		err_msg := SQLERRM;
		DBMS_OUTPUT.PUT_LINE('Error en bloque de vistas materializadas.'|| substr('ERROR:' || ERR_NUM || '-' || ERR_MSG, 1, 255));
END;
/