How To Apply Patch In Oracle Application

Patching is one of the very basic task that DBA perform. Well I am not out here as DBA, though I like stuffs what they do. I believe many out there love to have there own Vision Demo instance to check out seeded functionality and explore new solutions. Vision Demo comes with basic setups and don’t carry latest patches and fixes. So we may end up having very basic functionality and not able to explore new features. That brings us here to understand how can we apply patch.

Lets get started…

There are steps involved in applying patch to Oracle Applications.

• Validate Patch In Oracle Application :- Before proceeding any further first check if the patch is already applied to the instance or not. To check if the patch is already applied follow the this post - Check Patch in Oracle Application
• Download Patch : Get the patch from Oracle Support (Best know as - Metalink) and go through the readme file. This will tell you if any pre-requisite is there and if you need to perform any activity after patching. Following screenshots will tell you how to down and go through readme file.

How To Check Patch Applied In Oracle Applications

In this section we will try to find out if a patch is applied in Oracle Application or not. I believe you all know what a patch and why we use it. Without further delay lets get stared.

There are two ways you can identify if the patch is applied or not.

• Validation From Application

To validate the patch from Oracle Application please follow the below step :-

• Navigate to System Administration / System Administrator –> Oracle Application Manager –> Patching and Utilities.

• Enter the Patch Number in Patch filed in search page that opened.
• Enter additional criteria if you want to.
• Click on GO.

• You can see the result in bottom section.

• Validation From Database

To validate the patch from Database please follow the below steps :-

• Login to database using SQL Plus or any other tool like SQL Developer / Toad etc.
• Run the below query and validate the result.

  select * from ad_bugs
  where bug_number = :pi_patch_number

  

Register Custom Table Using Script In Oracle Applications

In my last post I talked about how to register table in Oracle Application. The steps involved are too much and will take lot of time if we have to create multiple tables or a single table is having too many columns.

To Save time you can use below script which will do the job for you. I am considering the your table is already created.

DECLARE
   vc_appl_short_name   CONSTANT VARCHAR2 (40) := 'C_APPS';
   vc_tab_name          CONSTANT VARCHAR2 (32) := 'C_FND_INTERFACES';
   vc_tab_type          CONSTANT VARCHAR2 (50) := 'T';
   vc_next_extent       CONSTANT NUMBER        := 512;
   vc_pct_free          CONSTANT NUMBER        := 10;
   vc_pct_used          CONSTANT NUMBER        := 70;
BEGIN

Register Custom Table In Oracle Applications

All you technical people out there very well know the purpose of creating custom table. Its basically when we need to store data before pulling into standard table (In case of inbound interface) or putting extract of data to share with external entity (In case of outbound interface). All these activities can be accomplished without registering custom table in oracle apps.

If you have a need to use these custom tables in standard functionality in frond end like using in Alerts / Audits, you must register them. Otherwise your custom table will not be visible in front end.

Lets start this by creating a sample custom table.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as c_apps
 
SQL> 
SQL> CREATE TABLE C_FND_INTERFACES
  2  ( INTERFACE_ID            NUMBER               NOT NULL PRIMARY KEY,
  3    SERVICE_CHANNEL         VARCHAR2(64 BYTE)    NOT NULL,
  4    SOURCE                  VARCHAR2(64 BYTE) NOT NULL,
  5    ATTRIBUTE1              VARCHAR2(2000 BYTE),
  6    ATTRIBUTE2              VARCHAR2(2000 BYTE),

Register Custom Application In Oracle Applications

As a standard practice when ever you need to write custom code/forms/reports in Oracle Applications you must put then under custom application TOP. This is to avoid impact of patching on custom objects. If we put custom object under stand TOP it might create issue and after patching it may start malfunctioning. If we have all custom code at one place it easy to maintain as we know exactly how many custom objects we have and where they are.

Before staring you need to first decide what should be your application name and short name. Here we go ....

SCHEMA NAME    : C_APPS
TOP NAME       : C_APPS_TOP
Application    : Custom Application
Data Group     : Standard
Request Group  : Custom Request Group

#1 – Create directory under application top

• Login to Application Server and Source the environment variable.
  

  login as: oracle
  oracle@192.168.56.200's password:
  Last login: Sun Feb  2 12:53:38 2014 from 192.168.56.1
  [oracle@ebsapp ~]$ 
  [oracle@ebsapp ~]$ pwd
  /home/oracle
  [oracle@ebsapp ~]$ . /u01/E-BIZ/apps/apps_st/appl/APPSVIS_ebsapp.env
  [oracle@ebsapp ~]$ cd $APPL_TOP
  [oracle@ebsapp appl]$ pwd
  /u01/E-BIZ/apps/apps_st/appl
  [oracle@ebsapp appl]$