Tuesday, June 29, 2010

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.


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;
/

19 comments :

Anonymous said...

Don't use this code. This is not working.
the references in the XDO_LOBS table is not removed.

SYMPLiK said...

Apart from XDO_LOBS, some config data is also stored in XDO_CONFIG_VALUES. Script has changed to remove those data.

Anonymous said...

It worked for me fine.
(the updated with XDO_LOBS, XDO_CONFIG_VALUES version).
thanks
stn

Anonymous said...

IT WORKED ...THANK YOU VERY MUCH....;)

Anonymous said...

This script working fine.
Thanks
Chandrasekaran

Anonymous said...

It works thanks so much Christopher

Anonymous said...

Awesome...I used several times, it worked for me all the time. Thanks for sharing the valuable info.

Anonymous said...

Thank u so much...... Code is doing perfect.

Rgds,
Sanjeev Kumar
Delhi

prichard said...

Hi Christopher,

Thanks a ton for your code. I was just checking the lob_type in xdo_lobs and note that I have the below lob_types - TEMPLATE_SOURCE, TEMPLATE instead of DATA_TEMPLATE and XML_SCHEMA.

Regards,
Richard




Anonymous said...

100% working for me...thanks for the script..

Anonymous said...

thanks for the scripts =)

Anonymous said...

Thanks a ton...!
I works like magic.

Anonymous said...

*It works like magic!!

Anonymous said...

It is working for me, Thank u.

Anonymous said...

Thanks for this code, realy it work for me.

Jorge Abreu said...

Thanks for sharing!

Anonymous said...

http://www.erporacleapps.com/2013/03/delete-xml-publisher-definition-and.html

Anonymous said...

Works great for me - thanks !

Pawan Saraswat said...

No Doubt, it is a very useful and working script. Thanks for helping
XML-P world with this script -
..
just a little bit amendment I want you made in this script so that it become generic.

Add these 3 commands to delete the translations as well just after deleting the template -

DELETE FROM XDO_TRANS_UNIT_VALUES WHERE TEMPLATE_CODE = var_templateCode;

DELETE FROM XDO_TRANS_UNIT_PROPS WHERE TEMPLATE_CODE = var_templateCode;

DELETE FROM XDO_TRANS_UNITS WHERE TEMPLATE_CODE = var_templateCode;

Now As and when you delete the template, translations associated to that template_code will also get deleted.

Regards,
Pawan Sarswat