Oracle SOAINFRA Purging Script

Below is the list of tables and the Proc which actually gets executed when Purg Script is called.

=================================================================================================================

GO
/****** Object: StoredProcedure [soa_workflow].[delete_workflow_instances] Script Date: 09/04/2014 16:55:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [soa_workflow].[delete_workflow_instances] @write_file SYSNAME
AS
BEGIN
SET NOCOUNT ON
–SET IMPLICIT_TRANSACTIONS ON

DECLARE @v_stmt VARCHAR(2000)
DECLARE @v_rowcount INT
BEGIN TRY
— purge of workflow data
— first, purge each child-table in turn, as there are performance concerns
— with relying on FK cascade deletes…

EXEC soa_common.log_info ‘Purging WFTask_TL’,@write_file

SET @v_stmt = ‘Purge WFTask_TL’
DELETE FROM DEV_SOAINFRA.WFTask_TL
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT

EXEC soa_common.debug_purge ‘soa_workflow.WFTask_TL’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFTaskHistory’,@write_file

SET @v_stmt = ‘Purge WFTaskHistory’
DELETE FROM DEV_SOAINFRA.WFTaskHistory
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT

EXEC soa_common.debug_purge ‘WFTaskHistory’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFTaskHistory_TL’, @write_file
SET @v_stmt = ‘Purge WFTaskHistory_TL’
DELETE FROM DEV_SOAINFRA.WFTaskHistory_TL
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT

EXEC soa_common.debug_purge ‘WFTaskHistory_TL’,”, @write_file, @v_rowcount

COMMIT

EXEC soa_common.log_info ‘Purging WFComments’,@write_file
SET @v_stmt = ‘Purge WFComments’
DELETE FROM DEV_SOAINFRA.WFComments
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT

EXEC soa_common.debug_purge ‘WFComments’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFMessageAttribute’,@write_file
SET @v_stmt = ‘Purge WFMessageAttribute’
DELETE FROM DEV_SOAINFRA.WFMessageAttribute
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFMessageAttribute’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFAttachment’,@write_file
SET @v_stmt = ‘Purge WFAttachment’
DELETE FROM DEV_SOAINFRA.WFAttachment
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT

EXEC soa_common.debug_purge ‘WFAttachment’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFAssignee’,@write_file
SET @v_stmt = ‘Purge WFAssignee’
DELETE FROM DEV_SOAINFRA.WFAssignee
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFAssignee’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFReviewer’,@write_file
SET @v_stmt = ‘Purge WFReviewer’
DELETE FROM DEV_SOAINFRA.WFReviewer
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFReviewer’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFCollectionTarget’,@write_file
SET @v_stmt = ‘Purge WFCollectionTarget’
DELETE FROM DEV_SOAINFRA.WFCollectionTarget
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFCollectionTarget’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFRoutingSlip’,@write_file
SET @v_stmt = ‘Purge WFRoutingSlip’
DELETE FROM DEV_SOAINFRA.WFRoutingSlip
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFRoutingSlip’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFNotification’,@write_file
SET @v_stmt = ‘Purge WFNotification’
DELETE FROM DEV_SOAINFRA.WFNotification
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFNotification’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFTaskTimer’,@write_file
SET @v_stmt = ‘Purge WFTaskTimer’
DELETE FROM DEV_SOAINFRA.WFTaskTimer
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFTaskTimer’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFTaskError’,@write_file
SET @v_stmt = ‘Purge WFTaskError’
DELETE FROM DEV_SOAINFRA.WFTaskError
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFTaskError’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFHeaderProps’,@write_file
SET @v_stmt = ‘Purge WFHeaderProps’
DELETE FROM DEV_SOAINFRA.WFHeaderProps
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFHeaderProps’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFEvidence’,@write_file
SET @v_stmt = ‘Purge WFEvidence’
DELETE FROM DEV_SOAINFRA.WFEvidence
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFEvidence’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFTaskAssignmentStatistic’,@write_file
SET @v_stmt = ‘Purge WFTaskAssignmentStatistic’
DELETE FROM DEV_SOAINFRA.WFTaskAssignmentStatistic
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFTaskAssignmentStatistic’, ”, @write_file, @v_rowcount
COMMIT

EXEC soa_common.log_info ‘Purging WFTaskAggregation’,@write_file
SET @v_stmt = ‘Purge WFTaskAggregation’
DELETE FROM DEV_SOAINFRA.WFTaskAggregation
WHERE rootTaskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFTaskAggregation’, ”, @write_file, @v_rowcount
COMMIT

— Now purge main task table
EXEC soa_common.log_info ‘Purging WFTask’,@write_file
SET @v_stmt = ‘Purge WFTask’
DELETE FROM DEV_SOAINFRA.WFTask
WHERE taskId in (SELECT twfp.TASKID FROM soa_workflow.TEMP_WFTASK_PURGE twfp)
SET @v_rowcount = @@ROWCOUNT
EXEC soa_common.debug_purge ‘WFTask’, ”, @write_file, @v_rowcount
COMMIT
END TRY

BEGIN CATCH
EXEC soa_common.log_error @v_stmt,@write_file
— Raise an error with the details of the exception
DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity INT
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
–SET IMPLICIT_TRANSACTIONS OFF
SET NOCOUNT OFF

END