Prev: PHP and Oracle Client
Next: slow forms on win 2003 terminal server
From: Gerard H. Pille on 10 Jun 2008 17:00
gazzag schreef:
>
> Sorry, in my previous post "ALTER <object_type> <object_name>
> RECOMPILE;" should be "ALTER <object_type> <object_name> COMPILE;"
>
> COMPILE *not* RECOMPILE.
>
> Apologies.
>

Plus you should cater for package bodies, where the syntax is

alter package amehoela compile body;
From: santosh.gaikwad on 11 Jul 2008 10:20
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...(a)polaris.net> wrote:
> In article <1196834485.490...(a)bubbleator.drizzle.com>, damor...(a)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> @?/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 -

From: DA Morgan on 11 Jul 2008 10:45
santosh.gaikwad(a)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...(a)polaris.net> wrote:
>> In article <1196834485.490...(a)bubbleator.drizzle.com>, damor...(a)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> @?/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(a)x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
 | 
Pages: 1
Prev: PHP and Oracle Client
Next: slow forms on win 2003 terminal server