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.