Implementing a custom tranformation function

Overview

The transformation engine in migration-center comes with a predefined set of transformation functions that cover many general use cases, e.g. GetValue, Concatenate, GetPathLevel, MapValue, SplitStringRegex, Substring, etc.

In certain situations, it might be necessary or convenient to implement a custom tranformation function, in order to perform a certain data transformation during the migration process. For example, if you need the same functionality in many different transformation rules or if it is not possible to implement a desired tranformation with the existing rules.

In this article we will guide you step-by-step through the process of implementing a custom transformation function that can be used in your migration sets.

Prerequisites

You should have basic knowledge about Oracle PL/SQL since all of migration-center's tranformation functions are implemented as PL/SQL stored procedures in the Oracle database.

Also, a PL/SQL development tool like Oracle SQL Developer is very useful for implementing and testing your custom tranformation functions.

The migration-center data model

Implementing a new transformation function in migration-center involves adding it to the list of available transformation functions in the migration-center database. Hence we will describe the underlying data model below.

The migration-center's data model for transformation function comprises two database tables: The table tr_functions contains the basic definition of a transformation function and the table tr_function_parameters contains the definitions of the function's input parameters.

See below the description of these tables and their columns.

Table Name: tr_functions

Table Comment: Define all functions available in the transformation engine

Table Name: tr_function_parameters

Table Comment: Define all functions' parameters

Implementing the custom tranformation function

Suppose you want to implement a Custom_Substring function that takes start and end index values of the substring - instead of start index and length of the substring as in the default Substring function.

First, we will create a new PL/SQL package custom_tr_functions that will contain our custom transformation function (or functions in case you want to implement several functions) and define our custom function custom_substring in it:

CREATE OR REPLACE PACKAGE FMEMC.custom_tr_functions IS
FUNCTION custom_substring(i_params ty_fme_scollection)
RETURN tr_parameter_sources.VALUE%TYPE;
END custom_tr_functions;
/

A custom transformation function must return a VARCHAR2 value and must have one input parameter of type TY_FME_SCOLLECTION. TY_FME_SCOLLECTION is a custom nested table type defined in migration-center's FMEMC database schema.

Second, we will implement our custom_substring function:

CREATE OR REPLACE PACKAGE BODY FMEMC.custom_tr_functions IS

/******************************************************************************
Name: custom_substring
Purpose: Extract substring from a given string using a custom function.
Each custom function must have a single parameter having the type
ty_fme_scollection. At the runtime, each collection value correspond
to an parameter defined in TR_FUNCTION_PARAMETERS table taking care the order
of parameters defined in file ORDER_NO

Input:function parameters as a TY_FME_SCOLLECTION
i_params(1) - contains the source string
i_params(2) - contains the "from" index
i_params(3) - contains the "to" index. This can be null.

Output: The extracted value
******************************************************************************/
FUNCTION custom_substring(i_params ty_fme_scollection)
RETURN tr_parameter_sources.VALUE%TYPE IS
v_result tr_parameter_sources.VALUE%TYPE;
v_from PLS_INTEGER;
v_to PLS_INTEGER;

BEGIN
IF NOT i_params.EXISTS(3)
THEN
raise_application_error(mc_err.code_tr_extraction_err,
'Wrong number of parameters calling function custom_substring!');
END IF;
BEGIN
-- v_from must be a non null number
v_from := to_number(nvl(i_params(2), 'x'));
v_to := to_number(i_params(3));
EXCEPTION
WHEN value_error THEN
-- if the parameters are invalid return null;
RETURN NULL;

END;
IF v_to IS NULL
THEN
v_result := substr(i_params(1), nvl(v_from, 1));
ELSE
v_result := substr(i_params(1), nvl(v_from, 1), v_to);
END IF;

RETURN v_result;
END custom_substring;

END custom_tr_functions;
/

Installing the function in the migration-center database

After we created the custom package with its custom transformation functions in it, we need to add appropriate entries in the tr_functions and tr_function_parameters tables in order to make the new functions available in the migration-center client:

-- define the function in tr_functions table
INSERT INTO TR_FUNCTIONS ( ID, NAME, DESCRIPTION, IMPLEMENTATION_CALL,
IS_MULTIVALUE) VALUES (
1000, 'Custom_substring', 'Extract part of a string using a custom function', 'begin :result := custom_tr_functions.custom_substring( :a ); end; '
, '0');

-- insert the function parameters
INSERT INTO TR_FUNCTION_PARAMETERS ( ID, TR_FUNCTION_ID, TYPE, NAME, DESCRIPTION, OPTIONAL,
ORDER_NO, DEFAULT_VALUE, STRING_TYPE_DEFAULT_VALUE, INDEX_VALUE_TYPE ) VALUES (
1001, 1000, 'V', 'Source String', 'Source string', '0', 1, NULL, 'A', '1');
INSERT INTO TR_FUNCTION_PARAMETERS ( ID, TR_FUNCTION_ID, TYPE, NAME, DESCRIPTION, OPTIONAL,
ORDER_NO, DEFAULT_VALUE, STRING_TYPE_DEFAULT_VALUE, INDEX_VALUE_TYPE) VALUES (
1002, 1000, 'V', 'Start_Index', 'Index to start with', '0', 2, NULL, 'U', '0');
INSERT INTO TR_FUNCTION_PARAMETERS ( ID, TR_FUNCTION_ID, TYPE, NAME, DESCRIPTION, OPTIONAL,
ORDER_NO, DEFAULT_VALUE, STRING_TYPE_DEFAULT_VALUE, INDEX_VALUE_TYPE ) VALUES (
1003, 1000, 'V', 'End_Index', 'Index to end with', '0', 3, NULL, 'U', '0');
COMMIT;

Using the custom tranformation function

After creating and installing the custom function in the migration-center database, you can use it as any of the pre-defined transformation functions in the transformation rules section of a migration set: