Tuesday, February 21, 2017

Query to Get Invalid Objects from Database

SELECT a.object_name,
       DECODE (a.object_type,
               'PACKAGE', 'Package Spec',
               'PACKAGE BODY', 'Package Body',
               a.object_type
              ) TYPE,
       (SELECT    LTRIM
                     (RTRIM (SUBSTR (SUBSTR (c.text,
                                             INSTR (c.text, 'Header: ')
                                            ),
                                     INSTR (SUBSTR (c.text,
                                                    INSTR (c.text, 'Header: ')
                                                   ),
                                            ' ',
                                            1,
                                            1
                                           ),
                                       INSTR (SUBSTR (c.text,
                                                      INSTR (c.text,
                                                             'Header: '
                                                            )
                                                     ),
                                              ' ',
                                              1,
                                              2
                                             )
                                     - INSTR (SUBSTR (c.text,
                                                      INSTR (c.text,
                                                             'Header: '
                                                            )
                                                     ),
                                              ' ',
                                              1,
                                              1
                                             )
                                    )
                            )
                     )
               || ' - '
               || LTRIM (RTRIM (SUBSTR (SUBSTR (c.text,
                                                INSTR (c.text, 'Header: ')
                                               ),
                                        INSTR (SUBSTR (c.text,
                                                       INSTR (c.text,
                                                              'Header: '
                                                             )
                                                      ),
                                               ' ',
                                               1,
                                               2
                                              ),
                                          INSTR (SUBSTR (c.text,
                                                         INSTR (c.text,
                                                                'Header: '
                                                               )
                                                        ),
                                                 ' ',
                                                 1,
                                                 3
                                                )
                                        - INSTR (SUBSTR (c.text,
                                                         INSTR (c.text,
                                                                'Header: '
                                                               )
                                                        ),
                                                 ' ',
                                                 1,
                                                 2
                                                )
                                       )
                               )
                        )
          FROM dba_source c
         WHERE c.owner = a.owner
           AND c.NAME = a.object_name
           AND c.TYPE = a.object_type
           AND c.line = 2
           AND c.text LIKE '%$Header%') "File Version",
       b.text "Error Text"
  FROM dba_objects a, dba_errors b
 WHERE a.object_name = b.NAME(+)
   AND a.object_type = b.TYPE(+)
   AND a.owner = 'APPS'

   AND a.status = 'INVALID'

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect