Oracle Apps : Truncate VS Delete

Truncate will effectively just move the high water mark of a table back to "zero" and either release allocated space back to dba_free_space or keep it for the segment. It just moves a pointer and says "magically there is no data in this table". It doesn't actually touch the data, it just says "we don't have it anymore"


Delete physically deletes, row by row. It deletes from the table, it deletes the index entry. The space is not given back and the high water marks stay exactly where they are. if there were a million rows in the table, the high water mark will be at the million row mark (or above).

Deleting Layout RTF Template from Data Template

Use below script TO DELETE the existing Template in BI Publisher.

  SET SERVEROUTPUT ON DECLARE

  -- Change the following two parameters

  var_templateCode VARCHAR2 (100) := 'Template Code'; -- 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.');

  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;

/