Sunday, March 6, 2011

Patch Checker : Generate SQL for bug number check

This is the fast way to find if the patches were installed or not...


Hi ALL
Oracle DBA is not a simple job. Some time I found me working day and night to give a excellent support to my customers. You know, backup here, installation  of patch there, clone and etc.
So all of Oracle DBA's trying to simplifier there work.


Me too!!!
If you is a DBA ,so you know about Oracle Baseline 11i. A lot of patches,upgrades,checks.
You need upgrade some modules, this module need pre-installation, the pre-installation need their pre-installations ---> head ache...


I decided to make me work easy :-)
Every patch in Metalink have readme file   with section pre-installation patches and looks like this:




3384350 - Patch 11i.SCM_PF.J
3262159 - Patch 11i.FND.H
3180816 - Patch 11i.AD.I
2368042 - Patch 11i.JTA.D (JTA 11.5.7.6)
2864765 - TXK Advanced Utilities Rollup A (October 2004)
3882116 - REHOST ORACLE 9iR2 JDBC DRIVERS (v9.2.0.5) FOR USE WITH ORACLE APPLICATIONS 11i
3594604 - TXK AUTOCONFIG TEMPLATE ROLLUP PATCH I (October 2004)


If I do't know that the patch installed , so I will check it using sql statement :
select * from  apps.ad_bugs where bug_number = '3384350'


I found the script that checks the version of ATG Rup and decided to create the  something like it!!!
I created a java script that parsed the source file with lines from readme file :


Source:
3384350 - Patch 11i.SCM_PF.J
3262159 - Patch 11i.FND.H
3180816 - Patch 11i.AD.I
2368042 - Patch 11i.JTA.D (JTA 11.5.7.6)
2864765 - TXK Advanced Utilities Rollup A (October 2004)
3882116 - REHOST ORACLE 9iR2 JDBC DRIVERS (v9.2.0.5) FOR USE WITH ORACLE APPLICATIONS 11i
3594604 - TXK AUTOCONFIG TEMPLATE ROLLUP PATCH I (October 2004)


*** Pay attantion, the file need to be without blank lines.
and Create a destination file with following sql:


SELECT (num), decode((bug_number)
,'3384350', 'Patch 11i.SCM_PF.J'
,'3262159', 'Patch 11i.FND.H'
,'3180816', 'Patch 11i.AD.I'
,'2368042', 'Patch 11i.JTA.D (JTA 11.5.7.6)'
,'2864765', 'TXK Advanced Utilities Rollup A (October 2004)'
,'3882116', 'REHOST ORACLE 9iR2 JDBC DRIVERS (v9.2.0.5) FOR USE WITH ORACLE APPLICATIONS 11i'
,'3594604', 'TXK AUTOCONFIG TEMPLATE ROLLUP PATCH I (October 2004)'
,'NOT_FOUND')
FROM apps.ad_bugs,
(
SELECT '3384350' num from dual
 union SELECT '3262159' num from dual
 union SELECT '3180816' num from dual
 union SELECT '2368042' num from dual
 union SELECT '2864765' num from dual
 union SELECT '3882116' num from dual
 union SELECT '3594604' num from dual
)
WHERE bug_number(+) = num
order by bug_number desc


You only need to run it in PL/SQL developer or Toad and got a results.
To download the program Click Here


If the script will help some one - I'll be glad.


See ya!!!

No comments:

Post a Comment