As mentioned in my previous post, my preferred strategy for setting the module and action settings is to store the current values, set the new values and, once my process finishes its tasks, restore the previous values.
A small package with a simple interface to perform these tasks is required. So, the list of functional requirements are:
- Set the module value, saving the existing value
- Set the action value, saving the existing value
- Restore the module value, overwriting the current value with the previous value
- Restore the action value, overwriting the current value with the previous value
- Retrieve the current module value
- Retrieve the current action value
Additionally, I’ll also specify:
- For familiarity reasons, the interface should resemble the interface that DBMS_APPLICATION_INFO provides
Thinking about the requirements it would appear to me that the solution is crying out for a traditional stack; push the current setting onto the stack, change the setting and pop off the previous value when restoring it.
The package pkg_app_info implements this. The exposed routines match the requirements listed above; set, get and restore for both module and action values. In a manner similar to DBMS_APPLICATION_INFO, the set module routines can also be used to set the action. There is one additional routine and that’s a function that returns the level to which settings are stored, i.e. how many are pushed onto the stack. This routine was very useful for testing purposes…
Behind the scenes in the package body things are quite simple too. Rather than implement two stacks, one for module values and one for action values, both are combined into a single record and a stack of this record type is implemented. So, whenever the module or action values are changed then both current values are pushed onto the stack. Restoring either action or module is a matter of restoring both values.
Since we do not know how many items we may need to push onto the stack, the stack is implemented as a nested table. Handling the nested table is the only area of complexity in the code. When an item is popped off the stack we have two options of how to handle it in our nested table; use the DELETE method or the TRIM method. The TRIM method removes the entry and also the placeholder in the nested table, whilst the DELETE method leaves the placeholder. The package uses the DELETE method and keeps track of the maximum number of items have been created in the table. The nested table only needs extending when the maximum stack depth needs increasing, not for every push operation.
Private package routines for push, pop and peek exist, which implement the traditional stack operations. Both the push and pop routines contain a call to another private routine that makes the call to update the Oracle module and action values.
Going back to the last code example in the previous post, let’s rewrite it using the new package. Other than calling the new package when setting the action we also need to restore the previous value on exit of the routines, via calls to pkg_app_info.restore_action. I’ve also inserted additional display lines between the calls to the various ETL routines to display what the module and action values are in the main routine.
The revised code looks like:
SET ECHO ON
SET SERVEROUPTUT ON
-- clear any existing settings for module and action
EXEC dbms_application_info.set_module (NULL,NULL)
DECLARE
-- a routine that obtains the module and action and outputs them
-- using dbms_output, along with a message
PROCEDURE display (p_msg IN VARCHAR2)
AS
BEGIN
dbms_output.put_line (NVL(pkg_app_info.get_module,'<>') || ':' ||
NVL(pkg_app_info.get_action,'<>') || ' - ' ||
p_msg);
END display;
-- simple stubs that do nothing except set the action
PROCEDURE read_data
AS
BEGIN
pkg_app_info.set_action ('read_data');
display ('Inside read_data');
pkg_app_info.restore_action;
END read_data;
PROCEDURE clean_data
AS
BEGIN
pkg_app_info.set_action ('clean_data');
display ('Inside clean_data');
pkg_app_info.restore_action;
END clean_data;
PROCEDURE transform_data
AS
BEGIN
pkg_app_info.set_action ('transform_data');
display ('Inside transform_data, before clean_data');
clean_data;
display ('Inside transform_data, after clean_data');
pkg_app_info.restore_action;
END transform_data;
PROCEDURE load_data
AS
BEGIN
pkg_app_info.set_action ('load_data');
display ('Inside load_data');
pkg_app_info.restore_action;
END load_data;
BEGIN
display ('Start');
pkg_app_info.set_module (p_module_name => 'ETL Process'
,p_action_name => NULL);
read_data;
transform_data;
load_data;
pkg_app_info.restore_module;
display ('End');
END;
/
The output we get is:
<>:<> - Start ETL Process:read_data - Inside read_data ETL Process:transform_data - Inside transform_data, before clean_data ETL Process:clean_data - Inside clean_data ETL Process:transform_data - Inside transform_data, after clean_data ETL Process:load_data - Inside load_data <>:<> - End PL/SQL procedure successfully completed.
If we focus on the transform_data entries in the above listing we can see that the action was set to “transform_data” then set to “clean_data” on entry into that routine before being reset back to “transform_data” once the clean_data routine finished. In other words, the action setting always reflects the specific routine that was executing… exactly as we wanted.
Of course, where you would want to put the “set” and “restore” routines in your code is a matter of choice. So long as you keep in mind what the settings are intended for and have a clear strategy then you should be okay.
Download scripts shown in this post.
Mark Hoxey 