DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Procedure

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    55

    Question

    How do i compile all procedures in a database using sql plus. I want to compile packages, functions and procedures

  2. #2
    Join Date
    Apr 2001
    Location
    West Sussex, England
    Posts
    15
    try this :

    set heading off
    set pagesize 0
    set feedback off

    spool xyz.sql

    select 'alter package '||object_name||' compile body;'
    from user_objects
    where object_type = 'PACKAGE BODY'
    union
    select 'alter package '||object_name||' compile;'
    from user_objects
    where object_type = 'PACKAGE'
    union
    select 'alter procedure '||object_name||' compile;'
    from user_objects
    where object_type = 'PROCEDURE'
    union
    select 'alter function '||object_name||' compile;'
    from user_objects
    where object_type = 'FUNCTION';

    spool off

    @xyz.sql

  3. #3
    Join Date
    Nov 2000
    Posts
    9

    Procedure Compile


    set feedback off
    set heading off
    set pagesize 0

    spool comp.sql
    select 'alter '||decode(object_type,'PACKAGE BODY',
    'PACKAGE',object_type)||' '||object_name||
    decode(object_type,'PACKAGE BODY',
    ' compile body;', ' compile;')
    from user_objects
    where object_type in
    ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
    and status='INVALID';

    spool off

    @comp.sql
    (OCP)

  4. #4
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    You can compile all objects that are invalid with this script:

    set heading off;
    set feedback on;
    set pages 10000;

    spool &&spoolfile

    select 'alter '||object_type, object_name
    ||' compile;'
    from user_objects
    where status = 'INVALID'
    and object_type != 'PACKAGE BODY'
    union
    select 'alter package '||NULL, object_name
    ||' compile body;'
    from user_objects
    where status = 'INVALID'
    and object_type = 'PACKAGE BODY';

    spool off

    @&&spoolfile;

    host del &&spoolfile;

    undefine spoolfile;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width