Oracle^® Business Intelligence Presentation Services Administration Guide > Administering Oracle BI Dashboards > Configuring Report Write Back >

Creating a Write Back Template

The write back template is an XML-formatted file that contains SQL commands needed to insert and update records in the write back table and columns you have created. You can create multiple write back templates, customizing each one for the fields used in each specific report. In the report properties you specify the name of the write back template to use.

The write back template must meet the following requirements:

• To meet security requirements, you must specify the connection pool along with the SQL commands to insert and update records. This SQL references the values passed in the write back schema to generate the SQL statements to modify the database table. Values can be referenced either by position (such as @1, @3) or by column ID (@{c0}, @{c2}). Column positions start numbering with 1, whereas column IDs start with c0. The use of Column ID is preferred.

  NOTE:  The notation @n is not the same as @{cn-1}, because the column with ID cn-1 may not be the nth column in the table.

• You must include both an <insert> and an <update> element in the template. If you do not want to include SQL commands within the elements, then you must insert a blank space between the opening and closing tags. For example, you must enter the element as

  <insert> </insert>

  rather than

  <insert></insert>

  If you omit the blank space, then you will see a write back error message such as "The system is unable to read the Write Back Template 'my_template'".

• If a parameter's data type is not an integer or real number, then add single quotes around it. If the database does not do Commits automatically, then add the optional postUpdate node after the insert and update nodes to force the commit. The postUpdate node typically follows this example:

  <postUpdate>COMMIT</postUpdate>

• Store the write back template files in the CustomMessages folder. The system can have multiple CustomMessages folders, such as SAROOTDIR\web\msgdb\customMessages, where SAROOTDIR is the installation directory. For information, see Customizing the Oracle BI Presentation Services User Interface Using XML Message Files.
• The write back template files can have any name of your choosing, because the system reads all XML files in the CustomMessages folder. To ensure that write back works correctly, include in the WebMessage element of the file the name of the SQL template that you specified when you created the write back table. You can have multiple WebMessage elements in one file, with each element specifying one SQL template.

  The following example shows the specification of the SQL template that is called "SetQuotaUseID."

  <WebMessage name="SetQuotaUseID">