Re: Recompiling invalid objects with a batch file

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)

      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);

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> @?/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 - 09:20:47 CDT