[OAUGNetDBA]-Re: SQL statement to get the applied patches list ?

["GUERIN Jean-Luc" <GUERINJL@ESSILOR.fr>]

Thank you Dave

Finally I have used 
alter system set events '10046 TRACE NAME CONTEXT 
FOREVER, LEVEL 4';
to catch the following OAM SQL:

SELECT * 
  FROM (SELECT   patch_name, merged_patches, max (appl_top_name), LANGUAGE, 
                 applied_patch_id, appl_top_id, count (*) drivers_applied, 
                 to_char (max (end_date), 
                          'dd-MM-yyyy hh24:mi:ss' 
                         ) completion_date, 
                 max (end_date), patch_drv_id 
            FROM (SELECT ap.patch_name patch_name, 
                         DECODE 
                            (merged_driver_flag, 
                             'Y', ad_pa_validate_criteriaset.get_concat_mergepatches 
                                                           (pd.patch_driver_id), 
                             '' 
                            ) merged_patches, 
                         AT.NAME appl_top_name, l.LANGUAGE LANGUAGE, 
                         ap.applied_patch_id, pr.appl_top_id, pr.end_date, 
                         pd.patch_driver_id patch_drv_id 
                    FROM ad_appl_tops AT, 
                         ad_patch_driver_langs l, 
                         ad_patch_runs pr, 
                         ad_patch_drivers pd, 
                         ad_applied_patches ap 
                   WHERE pr.appl_top_id = AT.appl_top_id 
                     AND AT.applications_system_name = :1 
                     AND pr.patch_driver_id = pd.patch_driver_id 
                     AND pd.applied_patch_id = ap.applied_patch_id 
                     AND pd.patch_driver_id = l.patch_driver_id 
                     AND ap.patch_name = :2 
                     AND (pr.start_date >= :3 
                     AND pr.end_date <= :4) 
                  UNION 
                  SELECT ap.patch_name patch_name, 
                         DECODE 
                            (merged_driver_flag, 
                             'Y', ad_pa_validate_criteriaset.get_concat_mergepatches 
                                                           (pd.patch_driver_id), 
                             '' 
                            ) merged_patches, 
                         AT.NAME appl_top_name, l.LANGUAGE LANGUAGE, 
                         ap.applied_patch_id, pr.appl_top_id, pr.end_date, 
                         pd.patch_driver_id patch_drv_id 
                    FROM ad_appl_tops AT, 
                         ad_patch_driver_langs l, 
                         ad_applied_patches ap, 
                         ad_patch_drivers pd, 
                         ad_patch_runs pr 
                   WHERE pr.patch_run_id IN ( 
                              SELECT prb.patch_run_id 
                                FROM ad_patch_run_bugs prb, ad_bugs b 
                               WHERE b.bug_id = prb.bug_id 
                                     AND b.bug_number = :5) 
                     AND pr.appl_top_id = AT.appl_top_id 
                     AND AT.applications_system_name = :6 
                     AND pr.patch_driver_id = pd.patch_driver_id 
                     AND pd.applied_patch_id = ap.applied_patch_id 
                     AND pd.patch_driver_id = l.patch_driver_id 
                     AND (pr.start_date >= :7 AND pr.end_date <= :8)) 
        GROUP BY applied_patch_id, 
                 patch_name, 
                 appl_top_id, 
                 merged_patches, 
                 LANGUAGE, 
                 patch_drv_id 
        ORDER BY 9 DESC, 1 DESC, 2 DESC, 3 DESC, 4 DESC) 
 WHERE ROWNUM < 201 
 
Regards
Jean-Luc

-----Message d'origine-----
De : OAUG Net DBA listserver [mailto:OAUGNetDBA@oaug.com] De la part de dave
Envoyé : lundi 18 août 2008 20:03
À : OAUG Net DBA listserver
Objet : [OAUGNetDBA]-Re: SQL statement to get the applied patches list ?

I've experienced issues with OAM detecting whether or not a patch has been applied.  Personally I use ad_bugs.

So one way would be to create a database link to the databases  and query against that table.

Quick and dirty way I guess..

On Mon, Aug 18, 2008 at 4:27 AM, GUERIN Jean-Luc <GUERINJL@essilor.fr> wrote:
> Hello
>
> We would like to compare the fine patch level of different EBS 11i 
> environments.
> We need the sql statement to perform it (including the ones applied as 
> merged patches).
> One way would be to trap the SQL behind the report generated by OAM.
> Does someone know how to get it?
> Thank you in advance.
> Regards.
>
> Jean-Luc GUERIN
> ESSILOR INTERNATIONAL
>
> #############################################################
> This message is sent to you because you are subscribed to the mailing list <OAUGNetDBA@oaug.com>.
> To unsubscribe, E-mail to: <OAUGNetDBA-off@oaug.com> To switch to the 
> FEED mode, send any message to <OAUGNetDBA-feed@oaug.com> To switch to 
> the DIGEST mode, E-mail to <OAUGNetDBA-digest@oaug.com> To switch to 
> the INDEX mode, E-mail to <OAUGNetDBA-index@oaug.com> Send 
> administrative queries to  <OAUGNetDBA-request@oaug.com>
>
>

#############################################################
This message is sent to you because you are subscribed to the mailing list <OAUGNetDBA@oaug.com>.
To unsubscribe, E-mail to: <OAUGNetDBA-off@oaug.com> To switch to the FEED mode, send any message to <OAUGNetDBA-feed@oaug.com> To switch to the DIGEST mode, E-mail to <OAUGNetDBA-digest@oaug.com> To switch to the INDEX mode, E-mail to <OAUGNetDBA-index@oaug.com> Send administrative queries to  <OAUGNetDBA-request@oaug.com>


#############################################################
This message is sent to you because you are subscribed to the mailing list <OAUGNetDBA@oaug.com>.
To unsubscribe, E-mail to: <OAUGNetDBA-off@oaug.com>
To switch to the FEED mode, send any message to <OAUGNetDBA-feed@oaug.com>
To switch to the DIGEST mode, E-mail to <OAUGNetDBA-digest@oaug.com>
To switch to the INDEX mode, E-mail to <OAUGNetDBA-index@oaug.com>
Send administrative queries to  <OAUGNetDBA-request@oaug.com>