sql help with grant script

[jmodic]

Hm, I wonder how your database reacts when you try to grant SELECT privilege on PACKAGE,FUNCTION or PROCEDURE. Mine only allows me to grant EXECUTE on those type of objects.

Anyhow, a simple usage of an IF-ELSIF-ENDIF will solve your problem:

DECLARE
  ....
  cursor get_obj is
    select object_name,owner, object_type
    from all_objects
    where .....;
  ....

BEGIN
  ....
  IF obj_rec.object_type IN ('PACKAGE','FUNCTION','PROCEDURE') THEN
    l_sql := 'GRANT EXECUTE';
  ELSIF obj_rec.object_type = 'SEQUENCE' THEN
    l_sql := 'GRANT SELECT';
  ELSE
    l_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE';
  END IF;
  t_sql := l_sql || ' on '||obj_rec.object_name||' to '||userrole;
  l_sql := l_sql || ' on '||obj_rec.object_name||' to '||admrole;
  ....

END;