I need to grant a user the ability to recompile invalid views on
another schema.

For procedures/functions/packages is is simple:
grant alter any procedure to xxx

But this does not include views, and I see no way to grant a similar
privilege for views. Even
grant create any view to xxx
is not good enough

Only
grant alter any table
is sufficient, but that is a little bit too much for recompiling an
invalid view

TIA

How about creating a definers-rights procedure in the schema that owns
the views, to accept the name of a view and 'execute immediate' a
recompile of it.

Then grant execute on the procedure to the other user.

I would do anything to avoid this execute immediate stuff.
I work hard to reduce parse/compile count, replace dynamic SQL with
bind variables and so on.
What you are suggesting would definitely slow down the server

<ford_desperado (AT) yahoo (DOT) com> wrote

you know you cant bind with DDL right? and an alter view is ddl.

There is *nothing* wrong with what Dave said and wont slow down anything.

What it will do is provide you a perfect, secure way of doing what you want

Now whats the problem?

ford_desperado (AT) yahoo (DOT) com wrote:
I have been using this script for a long time. You can convert this
into a procedure (User_Objects to All_Objects etc) as suggested by
David and further emphasised by Dave. If you want to run DDL from
PL/SQL, then you have to use Dynamic Sql... there is no other way
around it.

SET HEADING OFF
SELECT 'There are [ ' || TO_CHAR(count(*)) ||
' ] invalid objects. Recompiling objects, Please wait . . .'
FROM user_objects
WHERE status = 'INVALID';
SET HEADING ON
DECLARE
obj_name_ User_Objects.object_name%TYPE;
obj_type_ User_Objects.object_type%TYPE;
str_run_ VARCHAR2(200);
cid_ INTEGER;
ret_ INTEGER;
CURSOR Invalid_Objects_ IS
SELECT object_name, object_type
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type ASC;
BEGIN
FOR Get_Rec_ IN Invalid_Objects_ LOOP
BEGIN
obj_name_ := Get_Rec_.object_name;
obj_type_ := Get_Rec_.object_type;
IF (obj_type_ = 'FUNCTION') THEN
str_run_ := 'ALTER FUNCTION ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'JAVA SOURCE') THEN
str_run_ := 'ALTER JAVA SOURCE "' || obj_name_ || '"
COMPILE';
ELSIF (obj_type_ = 'JAVA CLASS') THEN
str_run_ := 'ALTER JAVA CLASS "' || obj_name_ || '"
RESOLVE';
ELSIF (obj_type_ = 'PACKAGE') THEN
str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'PACKAGE BODY') THEN
str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE
BODY';
ELSIF (obj_type_ = 'PROCEDURE') THEN
str_run_ := 'ALTER PROCEDURE ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'TRIGGER') THEN
str_run_ := 'ALTER TRIGGER ' || obj_name_ || ' COMPILE';

ELSIF (obj_type_ = 'VIEW') THEN
str_run_ := 'ALTER VIEW ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'MATERIALIZED VIEW') THEN
str_run_ := 'ALTER MATERIALIZED VIEW ' || obj_name_ || '
COMPILE';
ELSIF (obj_type_ = 'DIMENSION') THEN
str_run_ := 'ALTER DIMENSION ' || obj_name_ || ' COMPILE';
ELSIF (obj_type_ = 'TYPE') THEN
str_run_ := 'ALTER TYPE ' || obj_name_ || ' COMPILE';
END IF;
--
cid_ := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid_, str_run_, DBMS_SQL.NATIVE);
ret_ := DBMS_SQL.EXECUTE(cid_);
DBMS_SQL.CLOSE_CURSOR(cid_);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF (DBMS_SQL.IS_OPEN(cid_)) THEN
DBMS_SQL.CLOSE_CURSOR(cid_);
END IF;
END;
END;
END LOOP;
END;
/
SET HEADING OFF
SELECT 'There are [ ' || TO_CHAR(count(*)) ||
' ] invalid objects after recompile.'
FROM user_objects
WHERE status = 'INVALID';
SET HEADING ON

Regards
/Rauf

Yup, bit of confusion here. Execute Immediate is not incompatible with
the use of bind variables ...
http://download-west.oracle.com/docs...ynam.htm#13131
.... but as Dave says DDL (conveniently wrapped in "execute immediate")
is incompatible with them.

David, Dave, and Rauf,

thank you for your suggestions. David, I originally misunderstood you,
sorry for that.

I needed to enable automatic recompilation of invlaid views during
select, not recompiling all the invalid objects in a schema. I have
some buggy module that invalidates objects from time to time. I enabled
automatic recompilation of procedures, functions, and packages by
granting alter procedure.

I have just found a solution for my problem. I enabled automatic
recompilation of views by
1. granted create view
2. I recreated all the views by another user:
- connected as schema_user
- create view schema_owner.view_name as
that done, schema_user has the privilege to automatically recompile the
views it created in schema_owner