Creating User Hooks

Injecting custom code to Oracle Public APIs

The blog Introduction to User Hooks discussed about what is user hook, it's uses and how to identify the user hooks etc. This blog is about how create user hook in order to inject custom code to Oracle supplied public APIs.

Oracle provides user hook APIs to create, update and delete user hooks as shown below

• HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL : This API is used to create a user hook.
• HR_API_HOOK_CALL_API.UPDATE_API_HOOK_CALL : This API is used to update existing user hook.
• HR_API_HOOK_CALL_API.DELETE_API_HOOK_CALL : This API is used to create a delete existing user hook.

After deciding on what API User hook you are going to use, first create your custom code as a package procedure. Lets say its CUSTOM_PKG.CUSTOM_USER_HOOK_PROC.

For the sake of this blog lets assume that, we are planning to create user hook for SIT(Special Information Type) After Insert. Below are the steps involved in creating a user hook.

Create User Hook: 

Create a user hook using the API HR_API_HOOK_CALL_API.CREATE_API_HOOK_CALL. As we have already identified the user hook to be created, we need to get the hook id which is stored in the table HR_API_HOOKS. The hook id can be found using below query(HOOKS.API_HOOK_ID)

SELECT hooks.api_module_id, 
       hooks.api_hook_id, 
       hooks.hook_package, 
       hooks.hook_procedure 
FROM hr_api_hooks hooks, 
     hr_api_modules modules
WHERE modules.module_package = 'HR_SIT_API'
AND hooks.api_module_id = modules.api_module_id
AND hooks.hook_procedure like 'CREATE%'
AND hooks.api_hook_type = 'AP'; --After Process

After the hook id is identified, we need to call below procedure to create user hook. Commit your changes after it's executed. It inserts data into the table HR_API_HOOK_CALLS.

DECLARE
  l_api_hook_call_id      number;
  l_object_version_number NUMBER;
BEGIN
  hr_api_hook_call_api.create_api_hook_call
      (p_validate             => FALSE,
       p_effective_date       => TO_DATE('01-Jan-1951','DD-MON-YYYY'),
       p_api_hook_id          => 2889, --This is for After Insert Hook
       p_api_hook_call_type   => 'PP',
       p_sequence             => 4010, 
       p_enabled_flag         => 'Y',
       p_call_package         => 'CUSTOM_PKG',
       p_call_procedure       => 'CUSTOM_USER_HOOK_PROC',
       p_api_hook_call_id     => l_api_hook_call_id,
       p_object_version_number=> l_object_version_number);
END;

In the above API calls, all of the parameters are self explnatory except p_sequence.
Oracle recommends to use more than 2000 for sequence number as upto 2000 is being used by Oracle. You can add more than one user hook to an API, in that case make sure the sequence number is specified properly, for each hook creation, so that they execute in the order of your preference.

Generate User Hook:

After the user hook is successfully created with above call, the code needs to be generated in order to inject our custom code into the public API. There are two ways to generate hook package body

• Oracle provides an api to create user hook package body. Call the API hr_api_user_hooks_utility.create_hooks_one_module(p_api_module_id) with respective module id(in our case, it's 1283, as shown in above picture/sql). Sample code is shown below.
  

  BEGIN
    hr_api_user_hooks_utility.create_hooks_one_module(
                              p_api_module_id => 1283);
  END;
  

• Oracle provide a pre-processor script to generate hook package body. To generate the hook package body, in R12, run pre-processor script located at $PER_TOP/patch/115/sql/hrahkone.sql where as in 11i the pre-processor script is located at $PER_TOP/admin/sql/hrahkone.sql. This script asks for API_MODULE_ID parameter, so provide the API_MODULE_ID from above picture/sql.

It now injects custom code into the package procedure HR_SIT_API.CREATE_SIT_A. The package and procedure can be identified from above picture/sql. Now open this package and see if your custom code is present or not. If it's not present, then there must be something wrong so check carefully.

Note for 11i: The pre-processor script in 11i is located at $PER_TOP/admin/sql/hrahkone.sql. The remaining steps are same as above.

Introduction to User Hooks

Introduction to User Hooks

User hooks allows to add custom functionality to oracle supplied public API. Oracle EBS supply lot if public API to Insert/Update/Delete data in Oracle EBS tables. While doing so, the user might want to perform some custom validations/processing. This can be achieved with the help of User Hooks.

Why do I need a user hook if I can achieve the same using database triggers?
Oracle recommends not to use any database triggers on any of the EBS Tables as it tampers the integrity of the data because most of the tables in EBS are interrelated and an API is able to perform the operations on all related tables in a designated way.

How to identify if the API has user hook?
Run below SQL to find out if user hook is available for the API.
SELECT * FROM HR_API_MODULES
WHERE MODULE_PACKAGE LIKE '<Public API Package Name>'

Ex: Below query returns the hooks available for Special Information Types(SIT)
SELECT * FROM HR_API_MODULES
WHERE MODULE_PACKAGE LIKE 'HR_SIT_API'

If the above query returns more than one row, then it has user hooks enabled.

Can I perform custom processing on INSERT/UPDATE/DELETE operations?
Yes, it's possible but the API should have provision for the same. In the above example, it returns three rows with MODULE_NAME as CREATE_SIT, UPDATE_SIT and DELETE_SIT. So in this case it's possible to introduce custom processing for all above operations.

Like database triggers, can we execute our custom code before/after the operation?
Yes, it's possible and it's called Hooks. There are two types of hooks as shown below

• Before Process(BP): Before the processing starts, it executes custom code first.
• After Process(AP) : After the processing is completed, it executes custom code.

How to identify the hooks available for an API?

User Hooks are stored in a table called HR_API_HOOKS. This table has a column called Hook Type with values of BP(Before Process), AP(After Process) to identify type of hook.

Ex: Below query returns the hooks available for HR_SIT_API

SELECT HOOKS.* FROM HR_API_HOOKS HOOKS, HR_API_MODULES MODULES 

WHERE MODULES.MODULE_PACKAGE LIKE 'HR_SIT_API'

AND   HOOKS.API_MODULE_ID = MODULES.API_MODULE_ID;

Are user hooks upgrade safe?

Yes, custom code introduced using user hooks is upgrade safe. Might need to regenerate the user hooks if it's really required.

The next blog Creating User Hooks, discuss about how to create User Hook.