Documentation Center

Creating custom Contact extended details (Oracle)

You can specify custom Contact extended detail fields by adding or removing columns in the CONTACTS_EXTENDED_DETAILS table in the tridion_cm_email and tridion_submgmt databases.

Before you begin

The procedure described in this task must only be executed by a Database Administrator (DBA).

About this task

Outbound E-mail comes with a default set of extended detail fields. In most cases you will want to remove some of these fields and add custom extended detail fields of your own. This task describes how to create custom Contact Extended Detail fields or remove existing ones.

When you want to create or remove database columns for Contact Extended Details, you must do so in the following database tables as these tables need to be kept in sync:

  • On your Content Manager Server, change the following tables in the Outbound E-mail (tridion_cm_email) database:
    • CONTACTS_EXTENDED_DETAILS
    • IMPORT_CONTACTS
  • On your Presentation Server, change the following table in the Outbound E-mail subscription management (tridion_submgmt) database:
    • CONTACTS_EXTENDED_DETAILS

This task also describes how to create database indexes on extended detail fields. Depending on the size and set up of the CONTACTS_EXTENDED_DETAIL table, it may be useful to create indexes on extended details that are heavily used for searching. Note however that indexes decrease performance when saving and importing. Since search is implemented case in-sensitive, on Oracle you need to create function-based indexes using the Upper(fieldname) function.

Procedure

  1. Using an appropriate database manager for your Outbound E-mail database, access the Outbound E-mail database server.
  2. Open the tridion_cm_email database and navigate to the CONTACTS_EXTENDED_DETAILS table.
  3. Add or remove columns to this table by changing the table design, but DO NOT change the CONTACT_ID field. The name of this Contact Detail (that is, of this database table column) must meet the following restrictions:
    • It cannot be the same as any other standard or extended Contact Detail.
    • It cannot begin with two underscore characters (’__’).
    • It must begin with an underscore or a lowercase or uppercase letter from the English alphabet (a-z and A-Z).
    • All characters that follow the first character must be an underscore, a lowercase or uppercase letter from the English alphabet (a-z and A-Z), or a digit (0-9).

    For Oracle, the maximum permitted length of the database field is 255 characters and the data type of the new column must be one of the following:

    • Text types—VARCHAR2
    • Numerical types—NUMBER
    • Date/time types—DATE
  4. Specify whether the fields are optional (NULL) or mandatory (NOT NULL) .
  5. Save your changes.
  6. Create a database index for extended detail fields that are used heavily by users for search:
    1. In the CONTACTS_EXTENDED_DETAILS table, create function based indexes (that use the UPPER function to compare values) using the following syntax:
      CREATE INDEX index_name ON CONTACTS_EXTENDED_DETAILS(UPPER(field_name));

      Where field_name is the name of an extended detail field.

    2. Make sure the following security requirements are set to be able to use the function-based index:
      • The database user must have the query rewrite privilege.
      • The following system parameters must be set correctly:
        • QUERY_REWRITE_ENABLED=TRUE
        • QUERY_REWRITE_INTEGRITY=TRUSTED
    3. Enable or disable search on each extended detail field in the Content Manager Explorer interface in the OutboundEmail.xml configuration file.
  7. Navigate to the IMPORT_CONTACTS table. Add or remove exactly the same columns, using the exact same name (including uppercase and lowercase) and properties as the columns in the tridion_cm_email database.
  8. Save your changes and close the database manager.
  9. Access the Subscription Management (tridion_submgmt) database and navigate to the CONTACTS_EXTENDED_DETAILS table. Add or remove exactly the same columns, using the exact same name (including uppercase and lowercase) and properties as the columns in the tridion_cm_email database.
  10. Save your changes and close the database manager.

Results

You have added or removed columns defining the extended details for Outbound E-mail Contacts and enabled search fields.

What to do next

After you have changed extended details in the database, you need to configure the Content Manager server: Configuring Contact details