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 Column Name

Table Column Comment

id

Unique identifier (PK).

name

Function name (this value will be displayed in client).

description

Description of the function (this value will be displayed in client).

implementation_call

A dynamic PL/SQL block used by the transformation engine to call the function.

is_multivalue

 

 

Table Name: tr_function_parameters

Table Comment: Define all functions' parameters

Table Column Name

Table Column Comment

id

Unique identifier (PK).

tr_function_id

Extract_function unique identifier.

type

A parameter type may be: V (value), C (Condition), or M (Map).

A "Value" type means "String". A value will be extracted at runtime from a source attribute, a user value or a previous step. All string parameters will be converted to corresponding PL/SQL type inside the function.

For custom function only “V” is allowed.

name

The name of the parameter (this value will be displayed in client).

description

Description of the parameter (this value will be displayed in client).

optional

Specify if the parameter is optional or not. This is not used at the moment. So, all parameters must be mandatory.

Must be set with “0”.

order_no

Order of parameter in function call (the parameters will be displayed in the client in this order; starting with order no. 1).

default_value

Default value of the parameter that will be suggested to user in transformation rules.

string_type_default_value

The default source for a parameter having the type V (value). The string type can be U (User value), A (source attribute) or S (Previous step). This value can be changed in mc client when a function is to be used.

index_value_type

“1” – to allow using multivalue attributes

“0” – to allow only single values

Only one parameter for a function can be set with “1”.

 

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:

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk