O comando abaixo mostra como excluir um Relatório cadastrado no BI Publisher. (Pela tela somente é possível desativar, mas se você criou algo por engano, vai ficando o LIXO.) Então use esse SQL:
SET SERVEROUTPUT ON
DECLARE
-- Change the following 3 parameters
var_templateCode VARCHAR2 (100) := 'XG_XML_OM_002'; -- Template Code
var_templateAppl VARCHAR2 (100) := 'ONT'; -- Application 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,
T1.TEMPLATE_CODE,
t1.end_date
FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
WHERE T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE
AND T1.TEMPLATE_CODE = var_templateCode
and T1.APPLICATION_SHORT_NAME = var_templateAppl )
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 || ' ('||RS.TEMPLATE_APP_NAME ||') 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 || ' ('||RS.DEF_APP_NAME ||') 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;
/