Re: Recompiling invalid objects with a batch file
Date: Fri, 11 Jul 2008 07:45:09 -0700
Message-ID: <1215787495.801939_at_bubbleator.drizzle.com>
santosh.gaikwad_at_gmail.com wrote:
> here is the code... Simple and easy...
>
> BEGIN
> 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 -
Why are you reinventing the wheel?
Use the built in UTL_RECOMP package.
http://www.psoug.org/reference/utl_recomp.html
as previously suggested.
The posted code, for example, won't recompile a type, type body, or view.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Jul 11 2008 - 16:45:09 CEST