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

Thread: sql

  1. #1
    Join Date
    Dec 2000
    Posts
    62

    sql

    need to compose a dynamic sql statement to grab whatever invalid objects existing in a instance and compose the compile statements for those invalid objects as the output file? Basically what we need is to run this script to generate the compile script to recompile those invalid database objects.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Need to use PL/SQL, cursor for loop and dynamic SQL to do what you need to do. Cursor to collect object (their name and type, select object_name, object_type from dba/all/user_objects where status = 'INVALID' and object_type in (view, package, package body, trigger, procedure, function) that have a status of invalid. For loop to go thru each record using dynamic SQL to issue the 'alter object_type object_name compile' command (after you string the stings together), all wrapped inside a PL/SQL block. May want to do a quick test at the beginning to see if it is even necessary. May need to repeat and/or check for dependencies.

    Code:
    SQL> select object_name, object_type
      2  from user_objects
      3  where status = 'INVALID';
    
    no rows selected
    Last edited by stecal; 10-07-2003 at 12:34 PM.

  3. #3
    Join Date
    Jan 2001
    Posts
    3,134
    Couldn't you just spool something like this?


    SQL> Spool c:\my_bad_arse_sql;
    SQL>
    SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||'COMPILE;'
    FROM DBA_OBJECTS
    where status = 'INVALID'
    and owner not in ('SYS','SYSTEM');
    Spool off;
    Last edited by Mr.Hanky; 10-07-2003 at 12:46 PM.
    I remember when this place was cool.

  4. #4
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    Try...

    SELECT 'ALTER '||
    decode(object_type,'PACKAGE BODY','PACKAGE',object_type)||
    ' ' ||
    owner||
    '.'||
    object_name||
    ' compile'||
    decode(object_type,'PACKAGE BODY',' BODY') ||
    ';'
    FROM dba_objects
    WHERE status = 'INVALID'
    and object_type in ('PACKAGE',
    'PACKAGE BODY',
    'PROCEDURE',
    'VIEW',
    'TRIGGER')

  5. #5
    Join Date
    Dec 2000
    Posts
    62
    when i m runing this select statment to check any invalid objects, i get 0row selected does that mean i dont have any invalid objects in instance?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    $OH/rdbms/admin/utlrp.sql
    Jeff Hunter

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