Execute Oracle Argus Insight Custom Routines

The ETL Routines can be executed at two levels:

Before starting the incremental ETL.
After executing the incremental ETL.

The following are the steps to execute the custom routine:

Select the custom routine (PRE or POST), and enter an Oracle stored procedure name in the Value text box relevant to that custom routine. This Routine searches the database object that matches the procedure name in the schema APR_MART during Incremental ETL execution.

Note:

To view or modify the Value of a custom routine, refer to the View Oracle Argus Insight Custom Routines.
If the procedure is found, the application executes the ETL.
1. If the custom routine executes without any errors, then the application moves to the next step of the Incremental ETL process.
2. If the Custom routine executes with errors, then the application logs the error(s) in the table MART_DATA_INSERT_LOG and exits.
Note:
- ETL does not process any data before execution of the pre-incremental ETL custom routine.
- ETL commits the data before the execution of the post-incremental ETL routine.
- You should manually execute the post-incremental ETL routine, if it fails as it cannot be resumed.
To track the error when a custom routine fails, refer to the example as explained below:
1. Connect to the schema APR_MART, and create:
  A table PRE_POST_ETL_CHK with one column col1 varchar2(100).
  
  A procedure P_PRE_ETL to insert a row in the table with less than 100 characters.
  
  A procedure P_POST_ETL to insert a row with more than 100 characters.
2. Update the Values of the custom routines.
  For key PRE_INCREMENTAL_ETL_TASK, set the value to P_PRE_ETL.
  
  For key POST_INCREMENTAL_ETL_TASK, set the value to P_POST_ETL.
  
  The ETL will show an error while executing the post-incremental custom procedure as we try to insert large value than the column's length.
  
  Note:
  
  To view or modify the Value of a custom routine, refer to the View Oracle Argus Insight Custom Routines.
3. Run the incremental ETL.
4. Since P_POST_ETL procedure fails to insert a row, error occurs at the end of the ETL execution.
5. To verify the error, view the table MART_DATA_INSERT_LOG.
  
  The actual error text that is displayed in the column ORA_ERR_DESC is as below:
```
"ORA-20010: ORA-12899: value too large for column "APR_MART"."PRE_POST_ETL_CHK"."COL1" (actual: 105, maximum: 100)
ORA-06512: at "APR_MART.P_POST_ETL", line 3
ORA-06512: at line 1
ORA-06512: at "APR_MART.PKG_PWR_UTIL", line 3306
 -- ERROR while processing p_pre_post_incr_etl_tasks at 25-jun-2013 12:
ORA-06512: at "APR_MART.PKG_AIR_STOM", line 313
ORA-06512: at "APR_MART.PKG_PWR_UTIL", line 3323
ORA-06512: at "APR_MART.PKG_DBMS_JOB", line 1659
```
6. To ensure that a row is inserted from the custom routine before incremental ETL - P_PRE_ETL, view table PRE_POST_ETL_CHK.
If the procedure (or database object) is not found, then the application logs the error(s), and fails the ETL.
In this case, the ETL may be executed if you have explicitly created an exception-handling for such cases to absorb any exceptions, and go to the next step in the ETL process.

Optionally, to resolve this issue, create a procedure of that name, provide an existing procedure name, or remove the configuration.
Note:
- You should not modify the existing names of the database objects of Oracle Argus Insight, though additional objects can be created as part of customization as per your business needs.
- The changes related to custom routines should be tested in a test environment before implementing in the production environment.

Parent topic: ETL Extensibility