How to Delete XML Publisher Definition and Template
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?
In the XML Publisher's OA Framework pages, both Template and Data Definition pages do not provide an option to delete anything. Moreover, the Template Code or Definition Code is not allowed to be updated.
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.
You cannot change the Concurrent Program Short Name in the Form, and you cannot change the XML Template Code, and you cannot change the Data definition Code. If you make a typo in any one, disable it and create another one with the correct name. That's what Oracle suggests.
Come on...I WANT TO DELETE THEM, rather than recreating everything, and leave the wrong stuff in the system.
In another blog I show the way to delete concurrent program, and in here I will show you how to delete XML publisher template and the definition associated with this template. Change the parameters to fit your needs.
PL/SQL CODE:
SET SERVEROUTPUT ON
DECLARE
-- Change the following two parameters
var_templateCode VARCHAR2 (100) := 'SYMPLIK-TEST2'; -- 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 ('Template ' || 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 (
'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE (
'Issue a COMMIT to make the changes or ROLLBACK to revert.');
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;
/
