Problem in Compiling Invalid Objects in Stored Procedure.

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 Received on Thu May 09 2002 - 23:54:25 CEST