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

5 thoughts on “Oracle SOAINFRA Purging Script

  1. Malli Arjun

    Hi Durgadas,

    Thanks in advance.
    will this script be enough to delete all the closed,completed and offend records.
    We need the purging script for BPM worklist application in Prod env.

    Thanks,
    Malli

    Like

      1. Malli Arjun

        Yes, We are using Oracle database version (11.2.0.2), Could you please let me know what is the customize fields. We are beginners in BPM. Please help us to this

        Thanks,
        Malli

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s