Re: Recompiling invalid objects with a batch file
Date: Fri, 11 Jul 2008 07:20:47 -0700 (PDT)
Message-ID: <d3735f15-e595-48ea-8575-659db0cc0800_at_f63g2000hsf.googlegroups.com>
here is the code... Simple and easy...
BEGIN
[Quoted] FOR cur_rec IN (SELECT object_name,
object_type, DECODE(object_type, 'PACKAGE', 2, 'PACKAGE BODY', 3, 1) AS recompile_order FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') AND status != 'VALID' ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' || cur_rec.object_name || ' COMPILE'; ELSIF cur_rec.object_type = 'PACKAGE BODY' THEN EXECUTE IMMEDIATE 'ALTER PACKAGE ' || cur_rec.object_name || ' COMPILE BODY'; ELSIF cur_rec.object_type = 'FUNCTION' OR cur_rec.object_type = 'PROCEDURE' THEN EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' || cur_rec.object_name || ' COMPILE'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.object_name);
END;
END LOOP;
END ;
/
On Jun 2, 11:09 am, Ubiquitous <web..._at_polaris.net> wrote:
> In article <1196834485.490..._at_bubbleator.drizzle.com>, damor..._at_psoug.org
> wrote:
>
>
>
>
>
> >Ubiquitous wrote:
> >> Is there a way to write a sqlplus batch file to "recompile all invalid
> >> objects", or at least compile a specific object? I know the capacity
> >> exists in TOAD but I have no idea what's going on behind the scenes...
>
> >No reason to make it difficult on yourself:
>
> >SQL> _at_?/rdbms/admin/utlrp
> >http://www.psoug.org/reference/files.html
>
> >or if you prefer:
>
> >UTL_RECOMP
> >http://www.psoug.org/reference/utl_recomp.html
> >which allows you to compile in parallel or serial fashion.
>
> >or if you prefer:
>
> >DBMS_UTILITY.COMPILE_SCHEMA
> >http://www.psoug.org/reference/dbms_utility.html
>
> >No need to reinvent the wheel.
>
> Thank you, but this appears to be for ORACLE 11.
> Is there a similar version for ORACLE 8.1.7?
>
> Thanks again!- Hide quoted text -
>
> - Show quoted text -
Received on Fri Jul 11 2008 - 16:20:47 CEST