6 Support Utilities

Some support utilities will be exposed for implementers directly in APEX, allowing you to run functions such as database cleanup without Oracle involvement. These utilities may also be used by Oracle Support when responding to Service Requests on your RAP environments.

Data Cleanup Utility

Because foundation data is always loaded first through the Retail Insights data warehouse, implementers often need to erase data from the RI tables in preparation for a new load. Database functions have been exposed to APEX to allow targeted deletion of data by table name. The deletion requires a two-step process to prevent accidental execution of the command. The first step is to request an authentication token for the utility, which will return a string value. You must then pass the token into the subsequent call to the utility to execute the data cleanup step. Executing the utility with an expired or invalid token will return an error message.

The command to run in APEX to generate tokens is:

select  ri_support_util.generate_token from dual

Take the return value from this query and copy it into the TOKEN_VALUE variable in the PL/SQL block below. Specify the schema name and table name to be truncated, then run the PL/SQL block.

DECLARE
  TOKEN_VALUE VARCHAR2(200);
  SCHEMANAME VARCHAR2(200);
  TABLENAME VARCHAR2(200);
BEGIN
    TOKEN_VALUE := 'ABCDEF';
    SCHEMANAME := 'RADM01';
    TABLENAME := 'W_RTL_SLS_TRX_IT_LC_DY_FS';
    RI_SUPPORT_UTIL.CLEAR_SELECTED_RI_TABLES(
        TOKEN_VALUE => TOKEN_VALUE,
        SCHEMANAME => SCHEMANAME,
        TABLENAME => TABLENAME
    );
END;

If the process is successful, you will see that the PL/SQL block was successfully executed with no further message or results. If the process encounters any error, it will display the error details in the results panel in APEX. For example, if the token used is not valid it will show the following error:

To quickly clean the entire database schema instead of individual tables, you may instead call the following command. This command will erase all customer data except for the calendar, system configuration tables, and seed data records. Use this command if you need to reset the environment in preparation for a new dataload using a different dataset:

DECLARE 
v_token VARCHAR2(200); 
SCHEMANAME VARCHAR2(200); 

BEGIN 
    v_token := ri_support_util.generate_token; 
    SCHEMANAME := 'RADM01'; 
    RI_SUPPORT_UTIL.CLEAR_SELECTED_RI_TABLES( 
        TOKEN_VALUE => v_token, 
        SCHEMANAME => SCHEMANAME 
    ); 
END;

Calendar removal is provided as a separate function, because you cannot remove calendar information without also erasing all partitions (which are specific to your currently loaded calendar). The function name is CLEAR_RI_MCAL_TABLES and can be called the same way as the schema clear script above, passing in the token and schema name as the inputs. Before you perform any calendar cleanup, review the following:

• Partition removal is based on the current partition configuration in C_MODULE_ARTIFACT; it will not modify tables that are not enabled for partitioning. Ensure the configuration table reflects your current cleanup needs.

• Because calendar cleanup includes partition removal, you cannot use the system for a new data load without first re-partitioning the system. Refer to the RAP Implementation Guide for the steps to reload the calendar and partition the database.