Re: Recompiling invalid objects with a batch file

From: DA Morgan <damorgan_at_psoug.org>
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.org
Received on Fri Jul 11 2008 - 16:45:09 CEST

Original text of this message