Using Custom DB Function

Calling custom DB functions is supported only for ORACLE, DB2, Oracle ODBC and MSSQL. User Defined Function (UDF) is a programming construct that accepts parameters, does actions and returns the response of that action.

Steps to define DB function and create custom DB macros.
  1. Define DB function The function name should not use campaign reserved keywords/function names like below,
    • ADD_MONTHS
    • DATABASE_FUNCTION
    • DB2_DAYS
    • SUBSTR
    • SUBSTRING
    • LCLNUM
    • DAYOF
    • MONTHOF
    • YEAROF
    • CURRENT_DATE
    • LTRIM
    • RTRIM
  2. Define custom macro that uses custom DB function as defined by user
  3. Macro would be visible in formula editor.
  4. Use the macro.
    Note: Campaign will not do any syntax check. If arguments not passed as expected, will be runtime error.

Custom DB functions can be used in custom macro, derived field, expressions as part of select, extract, segement process box. For example, If you have defined custom DB function as MyCustomFunction, then to invoke the function user can write $MyCustomFunction (Arg1, Arg2, ... ).

Execution of these function are on database only. Hence, it is faster in execution as compared to other macros.

To create and use custom macro, refere Campaign User guide.

Configure CustomMacroSchema to specify the DB schema on which the custom DB function is invoked.
Note: CustomMacroSchema could be different schema than the one specified in OwnerForTableDisplay. Multiple datasources could use same CustomMacroSchema, so as to use custom db function from a common schema.

CustomMacroSchema

Configuration category

Campaign|partitions|partition[n]| dataSources|dataSourcename

Description

Specifies the schema used for calling user defined database functions.

The default value is blank.

For all data sources, set this property to the user of the database to which they are trying to call user defined database function.

Default value - No default value defined

Limitations:

Campaign supports UDF which returns a single value.

Campaign processes return value of custom db function as string upto 255 characters.