In this article different methods of getting information about applied Oracle EBS 12.2 patches are reviewed.

1. Oracle Applications Manager

Login to Oracle EBS as a user that has access to Oracle Applications Manager. For purposes of this tutorial Enginatics EBS 12.2.8 demo instance has been used.

Picture 1: Launching OAM Patching and Utilities feature

Populate the fields such as patch number, period of application and language and click ‘Go’ button. If the patch was applied you will get the results as on the screenshot below.

Picture 2: Checking applied patch

2. Database query

Another popular method of checking applied patches is querying Oracle EBS Database. As stated in MOS Doc ID 1963046.1 AD_BUGS table can not be used anymore for that purpose in release 12.2.x. It will still return information, but you can not rely on it. Some patches reported to be applied actually might be applied and then patching cycle was aborted before finalizing the application.
As an alternative AD_PATCH.IS_PATCH_APPLIED function is provided. You should login as APPS user to execute the statements below.

decode(ad_patch.is_patch_applied('&ebs_release',&appltop_id,&patch_num,'&lang'),'EXPLICIT','APPLIED','NOT_APPLIED','NOT APPLIED')
from dual;

While executing the above statement you will be prompted for required parameters. Example of executing the statement is presented on the picture below:

Picture 3: AD_PATCH.IS_PATCH_APPLIED function usage

3. Blitz report

Final method reviewed in this post is Blitz report. It allows executing query directly from EBS forms interface without navigating to OAM page. Also you don’t need to login to the database to run the query. You can create your custom report using AD_PATCH.IS_PATCH_APPLIED function and save it for future quick access. Please refer to Blitz Report™ User Guide for required steps to create a custom report.

The screenshots below contain details of an example report “AD Applied Patches R12.2”. It uses the same query as was used in the second method demonstration. Lexical parameters are used as placeholders that are substituted with the parameter SQL text at run-time. ebs_release and lang parameters have char data type, appltop_id and patch_number have number type. Please note that SQL Text also contains commas before with bind variables. It’s required to separate parameters in the function after the lexical parameters are replaced. Also the first three parameters has ‘Required’ flag ticked because they are mandatory for the function execution.

Picture 4: AD Applied Patches R12.2 report definition part 1

Picture 5: AD Applied Patches R12.2 report definition part 2

To run the report, find it in the list of available reports.

Picture 5: Finding the required report

Provide necessary parameters for report execution and click ‘Run’ button.

Picture 6: Providing the report parameters

Excel file with the output will be automatically downloaded and opened.

Picture 7: Results of the report execution


Each method reviewed in this post has its advantages.
While OAM is a convenient way to get the required information it has some disadvantages too. You may not have access to OAM or it is possibly not convenient to switch from the EBS forms interface to just query a patch.
The method of querying the database is widely used among DBAs. But to use it you need APPS user access and basic knowledge of SQL. Also you have to be able to connect to the database.
You can run Blitz report directly from EBS forms interface and quickly get results in the form of excel file. You also can use Blitz report to create any custom report.