Delete XML Publisher Data Definition and Template
The Problem:
In XML Publisher Administrator responsibility, both Data Definition and Template page don’t provide an option to delete data definition or template. Once created, you can’t even change the following:- Data Definition Code
- Data Definition Application
- Template Code
- Template Application
- Template Type
Why Oracle has done that way?
The reason is that: 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.The Workaround:
It is always recommended to go for this workaround in development instances and NOT in any production or UAT instance. After making the Data Definition and Template perfect in dev instance, you can always migrate them to production via FNDLOAD and XDOLOADER utilities.Before the workaround, let’s look at the main tables that store the information of the Data Definitions and Templates.
- 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.
- 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.
- XDO_CONFIG_VALUES: stores the values of XML Publisher configuration properties entered from the Oracle Applications interface.
- 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.
- XDO_TEMPLATES_TL: translation table for XDO_TEMPLATES_B.
SELECT *
FROM XDO_DS_DEFINITIONS_B
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';
SELECT *
FROM XDO_DS_DEFINITIONS_TL
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';
SELECT *
FROM XDO_LOBS
WHERE LOB_CODE = 'XX_DATA_DEF_CODE';
SELECT *
FROM XDO_CONFIG_VALUES
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';
Delete the Data Definitions:
-- API to delete Data Definition from XDO_DS_DEFINITIONS_B and XDO_DS_DEFINITIONS_TL table
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (<DATA_DEF_APP_NAME>,<DATA_DEF_CODE>);
COMMIT;
END;
-- Delete Data Templates, xml schema etc. from XDO_LOBS table (There is no API)
DELETE FROM XDO_LOBS
WHERE LOB_CODE = <DATA_DEF_CODE>
AND APPLICATION_SHORT_NAME = <DATA_DEF_APP_NAME>
AND LOB_TYPE IN
('XML_SCHEMA',
'DATA_TEMPLATE',
'XML_SAMPLE',
'BURSTING_FILE');
-- Delete from XDO_CONFIG_VALUES (if required)
DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME = <DATA_DEF_APP_NAME>
AND DATA_SOURCE_CODE = <DATA_DEF_CODE>;
Queries for the Templates:
SELECT *
FROM XDO_TEMPLATES_B
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';
SELECT *
FROM XDO_TEMPLATES_TL
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';
SELECT *
FROM XDO_LOBS
WHERE LOB_CODE = 'XX_TEMPLATE_CODE';
SELECT *
FROM XDO_CONFIG_VALUES
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';
Delete the templates:
-- API to delete Data Definition from XDO_TEMPLATES_B and XDO_TEMPLATES_TL table
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW (<TEMPLATE_APP_NAME>, <TEMPLATE_CODE>);
COMMIT;
END;
-- Delete the Templates from XDO_LOBS table (There is no API)
DELETE FROM XDO_LOBS
WHERE LOB_CODE = <TEMPLATE_CODE>
AND APPLICATION_SHORT_NAME = <TEMPLATE_APP_NAME>
AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');
-- Delete from XDO_CONFIG_VALUES (if required)
DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME = <TEMPLATE_APP_NAME>
AND TEMPLATE_CODE = <TEMPLATE_CODE>
AND DATA_SOURCE_CODE = <DATA_DEF_CODE>;
