How often do you create a XML publisher definition with a wrong Codes (Template or Data Definition)? Or you want to change the Code so that it is more meaningful?

Due to some typo error or to give some more meaningful name as per the standards, you can’t change those fields later. Also you can’t delete them too. Oracle recommends to disable them by giving an end date. But many developers don’t like to leave the wrong stuff in the system. They better like to delete them and freshly recreate them.

Why Oracle has restricted to Update or Delete?
The reason is concurrent program with XML output matches the Short Name with the template Code to find out which XML Publisher template to use for post processing. If you delete this template, the Post Processor cannot find the template, and then give errors. So it is always better not to give an option to update or delete.

But we have this workaround to update or delete the template or data definitions using scripts. It is highly recommend to run first in development instances.

Before the workaround, let’s look at the main tables that store the information of the Data Definitions and Templates.

1. XDO_DS_DEFINITIONS_B: table for storing data source definition represented by XML Schema Definition (XSD). Each data source has one or more elements, and this information are stored in XDO_DS_ELEMENTS_B.
2. XDO_DS_DEFINITIONS_TL: translation table for XDO_DS_DEFINITIONS_B.
XDO_LOBS: This table is used for storing locale (language and territory) sensitive binary and text files. It is mainly used for storing language layout templates.
3. XDO_CONFIG_VALUES: stores the values of XML Publisher configuration properties entered from the Oracle Applications interface.
4. XDO_TEMPLATES_B: table for template information. Each template has a corresponding data source definition stored in the XDO_DS_DEFINITIONS_B. Each translation of a certain template, not each template, has a corresponding physical template file. The physical template file information is stored in the XDO_LOBS.
5. XDO_TEMPLATES_TL: translation table for XDO_TEMPLATES_B.

–Code Tested in R12.1.3 Instance
SET SERVEROUTPUT ON;
DECLARE
— Change the following two parameters
var_templateCode    VARCHAR2 (100) := ‘ORACLEERPAPPSGUIDE’;     — Template Code
boo_deleteDataDef   BOOLEAN := TRUE;     — delete the associated Data Def.
BEGIN
FOR RS
IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
T1.DATA_SOURCE_CODE,
T2.APPLICATION_SHORT_NAME DEF_APP_NAME
FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
WHERE T1.TEMPLATE_CODE = var_templateCode
AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
LOOP
XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

DELETE FROM XDO_LOBS
WHERE     LOB_CODE = var_templateCode
AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND LOB_TYPE IN (‘TEMPLATE_SOURCE’, ‘TEMPLATE’);

DELETE FROM XDO_CONFIG_VALUES
WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND TEMPLATE_CODE = var_templateCode
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 50;

DBMS_OUTPUT.PUT_LINE (‘Selected template has been ‘ || var_templateCode || ‘ deleted.’);

IF boo_deleteDataDef
THEN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
RS.DATA_SOURCE_CODE);

DELETE FROM XDO_LOBS
WHERE LOB_CODE = RS.DATA_SOURCE_CODE
AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND LOB_TYPE IN
(‘XML_SCHEMA’,
‘DATA_TEMPLATE’,
‘XML_SAMPLE’,
‘BURSTING_FILE’);

DELETE FROM XDO_CONFIG_VALUES
WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 30;

DBMS_OUTPUT.PUT_LINE (
‘Selected Data Defintion has been ‘ || RS.DATA_SOURCE_CODE || ‘ deleted.’);
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (
‘Unable to delete XML Publisher Template ‘ || var_templateCode);
DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;
/