PL-SQL code to delete XML/BI Publisher template and Data Definition

1SET SERVEROUTPUT ON
2
3DECLARE
4   -- Change the following two parameters
5   var_templatecode    VARCHAR2 (100) := 'XXTEST';            -- Template Code
6   boo_deletedatadef   BOOLEAN := FALSE;
7   -- delete the associated Data Def.
8BEGIN
9   FOR rs
10      IN (SELECT t1.application_short_name template_app_name,
11                 t1.data_source_code,
12                 t2.application_short_name def_app_name
13            FROM xdo_templates_b t1, xdo_ds_definitions_b t2
14           WHERE     t1.template_code = var_templatecode
15                 AND t1.data_source_code = t2.data_source_code)
16   LOOP
17      xdo_templates_pkg.delete_row (rs.template_app_name, var_templatecode);
18
19      DELETE FROM xdo_lobs
20            WHERE     lob_code = var_templatecode
21                  AND application_short_name = rs.template_app_name
22                  AND lob_type IN ('TEMPLATE_SOURCE', 'TEMPLATE');
23
24      DELETE FROM xdo_config_values
25            WHERE     application_short_name = rs.template_app_name
26                  AND template_code = var_templatecode
27                  AND data_source_code = rs.data_source_code
28                  AND config_level = 50;
29
30      DBMS_OUTPUT.put_line ('Template ' || var_templatecode || ' deleted.');
31
32      IF boo_deletedatadef
33      THEN
34         xdo_ds_definitions_pkg.delete_row (rs.def_app_name,
35                                            rs.data_source_code);
36
37         DELETE FROM xdo_lobs
38               WHERE     lob_code = rs.data_source_code
39                     AND application_short_name = rs.def_app_name
40                     AND lob_type IN ('XML_SCHEMA',
41                                      'DATA_TEMPLATE',
42                                      'XML_SAMPLE',
43                                      'BURSTING_FILE');
44
45         DELETE FROM xdo_config_values
46               WHERE     application_short_name = rs.def_app_name
47                     AND data_source_code = rs.data_source_code
48                     AND config_level = 30;
49
50         DBMS_OUTPUT.put_line (
51            'Data Defintion ' || rs.data_source_code || ' deleted.');
52      END IF;
53   END LOOP;
54
55   DBMS_OUTPUT.put_line (
56      'Issue a COMMIT to make the changes or ROLLBACK to revert.');
57EXCEPTION
58   WHEN OTHERS
59   THEN
60      ROLLBACK;
61      DBMS_OUTPUT.put_line (
62         'Unable to delete XML Publisher Template ' || var_templatecode);
63
64      DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 200));
65END;
66/