Re: Problem in Compiling Invalid Objects in Stored Procedure.

manish1000_at_hotmail.com (Manish) wrote in message news:<9d90ea9a.0205091354.24d6bb40_at_posting.google.com>...
> Hi
>
> I am writing a stored procedure to compile invalid objects and analyze
> schema as follows. The Schema name is coming from a table at runtime
> and the table is containing one record only.
>
> CREATE OR REPLACE PROCEDURE Comp_inv_obj AS
> v_schema VARCHAR2(30) ; v_obj_type VARCHAR2(30) ; v_object
> VARCHAR2(30) ;
> v_cmd VARCHAR2(200); v_text CHAR(1) := 'X' ;
> CURSOR c1 is SELECT object_type, object_name FROM dba_objects
> WHERE owner IN (v_schema) AND object_type IN
> ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','TRIGGER') ;
> BEGIN
> SELECT upper(schema_nm) INTO v_schema FROM param_table ;
> dbms_utility.analyze_schema(v_schema,'COMPUTE');
> OPEN c1 ;
> WHILE v_text = 'X' LOOP
> FETCH c1 INTO v_obj_type, v_object ;
> EXIT WHEN c1%NOTFOUND ;
> IF v_obj_type = 'PACKAGE BODY' THEN
> v_cmd := 'alter package '||v_object||' compile body ' ;
> else
> v_cmd := 'alter '||v_obj_type||' '||v_object||' compile ' ;
> END IF ;
> EXECUTE IMMEDIATE v_cmd ;
> END LOOP ;
> CLOSE c1 ;
> EXCEPTION
> WHEN others THEN NULL ;
> END Comp_inv_obj ;
>
> The problem is the procedure is not working as it is supposed to. Can
> any one help me for this ?
>
> Thanks in advance.
>
> Manish

Tom Kyte has an excellent implementation of a procedure to compile invalid objects on his homepage (http://asktom.oracle.com). It uses invokers rights and temporary tables. I took the code almost as it is and have used it for a long time now - works fine! There is no analyze in it, but you could easily add that part.

Regards
Kurt Received on Fri May 10 2002 - 10:47:13 CEST