5.1.9 Truncate Stage Tables

This procedure performs the following functions:

• The procedure queries the FSI_DIM_LOADER_SETUP_DETAILS table to get the names of the staging table used by the Dimension Loader program.
• The MIS Date option only works to the Master Table approach (fn_STGDimDataLoader) dimension loader. It is not applicable to dimension data loaded using the standard Dimension Load Procedure (fn_drmDataLoader).

Executing the Truncate Stage Tables

You can execute this procedure either from SQL*Plus or from within a PL/SQL block or from the Batch Maintenance window within OFSAAI framework.

• To run the procedure from SQL*Plus, login to SQL*Plus as the Schema Owner. The function requires 4 parameters – Batch Run Identifier, As of Date, Dimension Identifier, Mis Date Required Flag. The syntax for calling the procedure is:

  function fn_truncateStageTable(batch_run_id varchar2, as_of_date varchar2, pDimensionId varchar2, pMisDateReqFlag char default 'Y')

  where

  • BATCH_RUN_ID is any string to identify the executed batch.
  • AS_OF_DATE in the format YYYYMMDD.
  • pDIMENSIONID dimension id.
  • pMisDateReqFlag is used to identify the data needs to be deleted for a given MIS Date. The default value is 'Y'.

  For Example

  Declare num number; Begin num := fn_truncateStageTable ('INFODOM_20100405','20100405' ,1,'Y' ); End;

• To execute the procedure from OFSAAI Batch Maintenance, create a new Batch with the Task as TRANSFORM DATA and specify the following parameters for the task:

  • Datastore Type: Select appropriate datastore from list
  • Datastore Name: Select appropriate name from the list
  • IP address: Select the IP address from the list
  • Rule Name: fn_truncateStageTable
  • Parameter List: Dimension ID, MIS-Date required Flag