CREATE TYPE roleArrayType AS VARRAY(100) OF VARCHAR2(100);
/
CREATE TYPE OBJ_USER AS OBJECT
(
USER_NAME	            VARCHAR2(100),
ROLE_NAME                roleArrayType,
APPLICATION_NAME    VARCHAR2(100));
/

create or replace procedure role_proc(p_user in obj_user, p_application in varchar2, p_message out varchar2)
AS

ddl_statement varchar2(1000);
v_role_str varchar2(1000);

begin


FOR getRoles IN (select role_app.role_name
            from dba_role_privs role_privs, role_app_tab role_app
            where role_privs.granted_role = role_app.role_name
            and role_privs.grantee = p_user.user_name
            and role_app.application_name = p_user.application_name)

LOOP   
       v_role_str := v_role_str ||','||getRoles.role_name;
        
END LOOP;	

/*----------------------------------------------------------------------------------------------------------------------------------------------
                           <Here we need to check whether roles got changed or not>
---------------------------------------------------------------------------------------------------------------------------------------------*/

-------------------------To revoke the existing roles from the user-----------------------------

          begin
	      ddl_statement := 'REVOKE ' || substr(v_role_str,2)  || ' FROM '|| p_user.user_name;
          execute immediate ddl_statement;
                   
        exception
             when others then
                         
             p_message := 'Error: ' || substr('Role ' || substr(v_role_str,2) || ': ' || sqlcode || ' / ' || SQLERRM(sqlcode),1,255);
	         raise_application_error (-20002,'An error has occurred revoking roles from the existing user');
        end;

--------------------------To assign the new roles to the user-----------------------------

 v_role_name:= p_user.role_name;
 v_role_str := null;

  FOR i in v_role_name.FIRST .. v_role_name.LAST

 LOOP
        v_role_str := v_role_str ||','||v_role_name(i);
        
 END LOOP;

        begin
        ddl_statement := 'GRANT ' || substr(v_role_str,2) || ' TO ' || p_user.user_name;
        execute immediate ddl_statement;
              
        exception
        when others then
              
            p_message := 'Error: ' || substr('Grant ' || v_role_str|| ': ' || sqlcode || ' / ' || SQLERRM(sqlcode),1,255);
            raise_application_error (-20002,'An error has occurred updating user granting role to the user');

 end role_proc;