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. Where PostgreSQL differs from Oracle, the PostgreSQL-specific steps are called out explicitly.

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.

PostgreSQL notes

• Use PL/pgSQL instead of PL/SQL.
• A development tool like pgAdmin is useful for implementing and testing your custom 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

PostgreSQL notes

• Tables live in the fmemc schema: fmemc.tr_functions and fmemc.tr_function_parameters.
• `implementation_call` is a dynamic PL/sql (oracle) or pl/pgsql (postgres), typically a SELECT, not an anonymous block
• `is_multivalue` must be set to `0` (multivalue custom transformation rules are not supported in PostgreSQL).

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;
/

PostgreSQL notes

• PostgreSQL does not use packages. Create a schema to group custom functions.
• The function signature takes a single parameter of type `fmemc.ty_fme_scollection` and returns `varchar`.
• Use array indexing with `i_params[1]` (not `i_params(1)`).

CREATE SCHEMA IF NOT EXISTS custom_tr_functions
    AUTHORIZATION fmemc;

-- FUNCTION: custom_tr_functions.custom_string(fmemc.ty_fme_scollection)

-- DROP FUNCTION IF EXISTS custom_tr_functions.custom_string(fmemc.ty_fme_scollection);

CREATE OR REPLACE FUNCTION custom_tr_functions.custom_string(
    i_params fmemc.ty_fme_scollection)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    STABLE PARALLEL UNSAFE
AS $BODY$
DECLARE
    v_from      int;
    v_to        int;
BEGIN
    IF NOT COALESCE(CARDINALITY(i_params), 0) = 3 THEN
        RAISE EXCEPTION '%', 'Wrong number of parameters passed to function custom_string!'
            USING ERRCODE = mc_err.get_code('code_tr_extraction_err');
    END IF;

    BEGIN
        v_from := i_params[2]::int;

        -- handle '' as null
        v_to   := NULLIF(i_params[3], '')::int;
    EXCEPTION
        WHEN OTHERS THEN
            -- if the parameters are invalid return null
            RETURN NULL;
    END;

    IF COALESCE(i_params[1], '') = '' THEN
        -- if source string is empty or null then return null
        RETURN NULL;
    END IF;

    RETURN SUBSTRING(
        i_params[1],
        v_from,
        COALESCE(v_to, LENGTH(i_params[1]))
    );
END;

$BODY$;

ALTER FUNCTION custom_tr_functions.custom_string(fmemc.ty_fme_scollection)
    OWNER TO fmemc;

COMMENT ON FUNCTION custom_tr_functions.custom_string(fmemc.ty_fme_scollection)
    IS 'Name: custom_tr_functions.custom_string
Purpose: Extract substring from a given string using a custom function.
Each custom function must have a single parameter having the type
fmemc.ty_fme_scollection. At the runtime, each collection value correspond
to an parameter defined in fmemc.TR_FUNCTION_PARAMETERS table taking care the order
of parameters defined in file ORDER_NO

Input:function parameters as a fmemc.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';

Optional test calls (PostgreSQL):

-- expected 'hello'
select * from custom_tr_functions.custom_string(array['hello world', '1', '5']::fmemc.ty_fme_scollection);

-- expected 'hello world'
select * from custom_tr_functions.custom_string(array['hello world', '1', null]::fmemc.ty_fme_scollection);

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;

PostgreSQL notes

• Use the fmemc schema and a `SELECT` in `implementation_call`.

-- define the function in tr_functions table
INSERT INTO fmemc.tr_functions(id, name, description, implementation_call, is_multivalue)
    VALUES (10000, 'CustomSubString', 'Extract part of a string using a custom function', 'SELECT custom_tr_functions.custom_string( $1 );', '0');

-- insert the function parameters
INSERT INTO fmemc.tr_function_parameters(id, tr_function_id, type, name, description, optional, order_no,
                default_value, string_type_default_value, index_value_type)
    VALUES (10001, 10000, 'V', 'Source String', 'Source string', '0', '1', null, 'A', '1');

INSERT INTO fmemc.tr_function_parameters(id, tr_function_id, type, name, description, optional, order_no,
                default_value, string_type_default_value, index_value_type)
    VALUES (10002, 10000, 'V', 'Start Index', 'Index to start with', '0', '2', null, 'U', '0');

INSERT INTO fmemc.tr_function_parameters(id, tr_function_id, type, name, description, optional, order_no,
                default_value, string_type_default_value, index_value_type)
    VALUES (10003, 10000, 'V', 'End Index', 'Index to end with', '0', '3', null, 'U', '0');

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: