The string concatenation SQL file

IBM OpenPages® includes a template SQL file (field_concat_template.sql). Use a copy of this file to specify the parameters to submit with the field_concat command.

Important: Many of the parameters specified in the SQL file have requirements, restrictions, and cautions noted. These are important for a successful concatenation.

See Running string concatenation .

Parameters

Table 1. field_concat_template.sql parameters
Parameter	Description
`l_actor_name`	The user name making the change. The user must log in as an administrator. The script puts the system into System Administration Mode (SAM) prior to concatenating any fields.
`l_field_group_name_src<#>`	The name of the field group containing the simple string field. Where: `<#>` is a value from 01 to 08. `l_field_group_name_src<#>` and `l_property_name_src<#>` are always specified in pairs. These parameters must have values specified in order. For example, `l_field_group_name_src01` must have a value before `l_field_group_name_src02` is specified. Specified field groups must be associated with an object type.
`l_property_name_src<#>`	The name of the source simple string field. Where: `<#>` is a value from 01 to 08. The source must be a simple string. At least one source must be specified. The source must already exist `l_field_group_name_src<#>` and `l_property_name_src<#>` are always specified in pairs. These parameters must have values specified in order. For example, `l_property_name_src01` must have a value before `l_property_name_src02` is specified. A property can only be specified once in the set of fields to concatenate. Only the resource description system property is supported.
`l_separator`	The separator to use between concatenated fields. The default separator is null. If you concatenate only one source into the destination, the separator character is not used. To use `&` as a separator, encode it as `chr(38)`. For example: `l_separator OP_GLOBALS.DB_Max_String_T := chr(38);` The separator string can be no longer than 100 bytes. If the destination field has a rich text display type, characters in the separator sequence must be encoded. For example, to represent a less-than sign ("<"), encode it as: `l_separator OP_GLOBALS.DB_Max_String_T := chr(38)\|\|'lt ';`
`l_object_type_name`	The name of the object type containing the destination long text field. The object type must be the same for the destination as it is for the source.
`l_field_group_name`	The name of the field group containing the destination long text field. The destination field group must exist. The destination field group must be a customer field group, not a system field group.
`l_property_name`	The name of the destination field. The name destination field must either not exist, or if it does exist, must not used anywhere. If the destination field does not already exist, the `l_large_text_length` parameter must be specified. If the destination field does exist, it must be of data type `Long String`. If the destination field is Rich Text, and source fields are a mix of Text and Rich Text, then there is the possibility that the concatenated value will not display properly in the UI. Such operations should be executed with caution.
`l_property_desc`	The description of the destination long text field.
`l_large_text_length`	The length property of the destination field. The default is `OP_OBJ_MODEL_MGR.g_dl_longtext_medium`. If the destination does not exist, this parameter must be specified, as either `OP_OBJ_MODEL_MGR.g_dl_longtext_medium` or `OP_OBJ_MODEL_MGR.g_dl_longtext_large`.
`l_is_done_by_vendor`	Set to true to add the concatenation to audit trail. The default is `OP_Globals.sc_False`. Valid values are: `OP_Globals.sc_True` `OP_Globals.sc_False` See Auditing configuration changes
`l_remote_address`	The remote address to perform the audit trail. The default is null. Any value is ignored if `l_is_done_by_vendor` is `OP_Globals.sc_False`.
`l_remote_host`	The remote host to perform the audit trail. The default is null. Any value is ignored if `l_is_done_by_vendor` is `OP_Globals.sc_False`.
`l_preview_only`	Set to true to only print the changes that will be made by script. No changes are actually made. The default is `OP_Globals.sc_False`. Valid values are: `OP_Globals.sc_True` `OP_Globals.sc_False` Tip: Run the script in preview mode (a setting in the field_concat_template.sql file) to check the results before doing the concatenation.
`l_override_objtp_logic`	Set to true to override any logic applied to the object types, such as their relationships. The default is `OP_Globals.sc_False`. Valid values are: `OP_Globals.sc_True` `OP_Globals.sc_False` If `l_object_type_name` is left blank, and the source and destination field groups are associated to different object types, the script will fail unless you set this parameter to `OP_Globals.sc_True`. Each source field group and destination field group must associate with the same object type or set of object types. For example, the following scenario will fail unless this parameter is set to `OP_Globals.sc_True`: Source field group A is associated to object types X and Y. Source field group B is associated only to object type X. Destination field group is associated only to object type X.

Sample

Note: The following sample includes only those declarative statements that are subject to your changes.

declare
  l_actor_name              ACTORINFO.NAME%type                  := 'OPAdmin';
  l_field_group_name_src01  BUNDLEDEFS.NAME%type                 := 'QA10_SS_1';
  l_property_name_src01     PROPERTYDEFS.NAME%type               := 'QA10_Simple2';
  l_field_group_name_src02  BUNDLEDEFS.NAME%type                 := 'QA10_LargeText';
  l_property_name_src02     PROPERTYDEFS.NAME%type               := 'QA10_S3';
  l_field_group_name_src03  BUNDLEDEFS.NAME%type                 := 'Core Attributes';
  l_property_name_src03     PROPERTYDEFS.NAME%type               := 'Resource Description';
  l_field_group_name_src04  BUNDLEDEFS.NAME%type                 := 'MG_7';
  l_property_name_src04     PROPERTYDEFS.NAME%type               := 'MG_S7';
  l_field_group_name_src05  BUNDLEDEFS.NAME%type                 := 'MG_4';
  l_property_name_src05     PROPERTYDEFS.NAME%type               := 'MG_S4';
  l_field_group_name_src06  BUNDLEDEFS.NAME%type                 := 'MG_5';
  l_property_name_src06     PROPERTYDEFS.NAME%type               := 'MG_S5';
  l_field_group_name_src07  BUNDLEDEFS.NAME%type                 := 'MG_6';
  l_property_name_src07     PROPERTYDEFS.NAME%type               := 'MG_S6';
  l_field_group_name_src08  BUNDLEDEFS.NAME%type                 := 'MG_3';
  l_property_name_src08     PROPERTYDEFS.NAME%type               := 'MG_S3';
  l_separator               OP_GLOBALS.DB_Max_String_T           := ',';
  l_object_type_name        ASSETTYPES.NAME%type                 := 'SOXBusEntity';
  l_field_group_name        BUNDLEDEFS.NAME%type                 := 'QA10_LargeText';
  l_property_name           PROPERTYDEFS.NAME%type               := 'TEST101';
  l_property_desc           PROPERTYDEFS.DESCRIPTION%type        := 'MGMGMGMGDescription';
  l_large_text_length       PROPERTYDEFS.DATA_LENGTH%type        := OP_OBJ_MODEL_MGR.g_dl_longtext_medium;
  l_is_done_by_vendor       OP_Globals.Flag_String_T             := OP_Globals.sc_False;
  l_remote_address          i18n_audit_trail.remote_address%type := '';
  l_remote_host             i18n_audit_trail.remote_host%type    := '';
  l_preview_only            OP_Globals.Flag_String_T             := OP_Globals.sc_False;
  l_override_objtp_logic    OP_Globals.Flag_String_T             := OP_Globals.sc_False;