Deleteing XML Publisher Data Definition and Template from Backend

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?

Oracle application is not providing any option to delete the data definition and template but here I'm providing a script by which you can delete both from back end. But before running the script you have to take care of two things:

a)Give the proper template code in the variable var_template code of the script because you can't rollback.
b)If you to delete the data definition file with template then make the variable boo_deleteDataDef true unless make it as false.

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.

/* Formatted on 2014/07/04 19:11 (Formatter Plus v4.8.8) */

SET SERVEROUTPUT ON

DECLARE

-- Change the following two parameters

   var_templatecode    VARCHAR2 (100) := 'Order_Details';    -- 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;

/

Challa.