Re: Problem in Compiling Invalid Objects in Stored Procedure.

Thanks for ur response. The schema-name is not been propogated properly to analyze/compile objects. It is coming correctly from select statement as I checked in SQL-Plus, but variable v_schema is not working properly somewhere. If I hard-code schema-name in analyze/compile statements, it is working fine. Further I can use compile_schema procedure but again schema-name is to be passed thru variable.  

Manish

bhooshan.s.prabhu_at_orbitech.co.in (Bhooshan Prabhu) wrote in message news:<16584988.0205092214.73698393_at_posting.google.com>...
> Could you be a little more specific in telling which part of the SP
> fails? Also,
> dbms_utility does provide a procedure compile_schema that takes schema
> name as a parameter and compiles all procedures, functions, packages
> and triggers in the
> specified schema. You could make use of it.
>
> Regards
> Bhooshan
>
>
> 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
Received on Fri May 10 2002 - 16:04:30 CEST