A Small Utility to Generate Cascading Delete SQL Script in Oracle

• Download solution - 24.18 KB

Introduction

The article is to share a small utility to generate cascading delete SQL Script in Oracle and to introduce how it works.

Background

Recently I was doing data fixes for my client, which required to delete data in tens of tables. There tables have reference tables, which have reference tables, which have reference tables ... To delete a table, I had to delete its reference tables first, to delete its reference tables, I had to delete the reference tables' reference tables first and so on. In one case, there are six levels deep and 220 tables to delete first in strict order. I created the utility to generate the cascading delete script to save me from the tedious and error-prone job. I post it in CodeProject so that others may use it since I got a lot of help from the site. The utility supports Oracle database now. But it can be extended to support SQLServer. The source code is provided so that others may extend it.

Using the Code

The code structure of the utility consists of three parts. The bottom part is to get the database dictionary information like primary key and reference table. The middle part is to implement the core logic to generate script. The upper part is the UI, a windows application interface which lets the user generate script. The utility was implemented with VB.NET and Visual Studio 2010.

Let me introduce from the bottom part. In the bottom part, an interface was introduced so that it can be extended to support different databases.

Public Interface IDictionaryInfo

    ' Get Primary Key of a table
    Function GetPKColName(ByVal strTableName As String) As String

    ' Get foreign key reference table and the foreign key column in that table
    '   A list of KeyValuePair rather than a dictionary is used as
    '   return type since one reference table may have 1+ reference column.
    Function GetFKReferences(ByVal strTableName As String) _
    	As List(Of KeyValuePair(Of String, String))

End Interface

Here is the implementation of the interface to support Oracle database.

' the implementation of the IDictionaryInfo for oracle
Public Class OracleDictionaryInfo
    Implements IDictionaryInfo

    ' get the primary key for a table
    Public Function GetPKName(ByVal strTableName As String) _
    	As String Implements IDictionaryInfo.GetPKColName
        strTableName = strTableName.ToUpper		'Oracle tables are saved
        				' in uppercase in Oracle dictionary.

        'SQL to retrieve oracle dictionary to get primary key
        Dim strSQl As String = String.Format_
        	("SELECT cols.column_name FROM user_constraints cons, _
        	user_cons_columns cols WHERE cols.table_name = '{0}' _
        	AND cons.constraint_type = 'P' AND cons.constraint_name = _
        	cols.constraint_name", strTableName)

        Dim dt = GetDataTable(strSQl)

        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)(0)
        Else
            Return String.Empty
        End If
    End Function


    ' get the foreign key tables and its reference columns
    Public Function GetFKReferences(ByVal strTableName As String) _
    As System.Collections.Generic.List(Of _
    System.Collections.Generic.KeyValuePair(Of String, String)) _
    Implements IDictionaryInfo.GetFKReferences
        strTableName = strTableName.ToUpper

        Dim strSQl As String = String.Format_
        ("select C.TABLE_NAME, C.COLUMN_NAME from user_cons_columns _
        c where c.CONSTRAINT_NAME IN ( Select constraint_name from_
        (user_constraints) where constraint_type='R'  and _
        r_constraint_name in (select constraint_name from user_constraints _
        where constraint_type in ('P','U') and table_name='{0}'))", _
        strTableName)

        Dim dt = GetDataTable(strSQl)

        Dim tnq = From dr In dt.Rows Select New KeyValuePair_
        (Of String, String)(CType(dr(0), String), CType(dr(1), String))
        Return tnq.ToList()
    End Function

End Class

The middle part collects database dictionary information of primary keys and references. It calls an internal recursive function to generate the delete scripts in a cascading way.

' Core class to generate script for the table
Public Class GenScriptCore

    Private iDic As IDictionaryInfo

    Private Sub New()   ' no instance is allowed
    End Sub

    ' Main function to generate script.  Prepare database type, _
    connection string and table name to call the function
    Public Shared Function GenScript(ByVal enumDB As DB, _
    ByVal strConnection As String, ByVal strTableName As String) As String
        Dim oc As New GenScriptCore

        Select Case [enumDB]
            Case DB.Oracle
                oc.iDic = New OracleDictionaryInfo(strConnection)
            Case DB.SQlServer
                Throw New Exception("SQL server is not supported yet. _
                But you can implement it by implementing the interface of _
                IDictionaryInfo for SQL Server")
        End Select

        Dim strPKCol = oc.iDic.GetPKColName(strTableName)

        If String.IsNullOrEmpty(strPKCol) Then
            Throw New Exception(String.Format("table '{0}' _
            does not exist or has no primary key / unique key", strTableName))
        End If

        Return oc.gs(strTableName, strPKCol) ' call the main method
    End Function

	' Core function to generate script though recursive calling
    Private Function gs(ByVal strTableName As String, _
    ByVal strFilterColumn As String, _
    Optional ByVal strInFilter As String = Nothing) As String

        Dim sb As New System.Text.StringBuilder
        Dim strPKCol = iDic.GetPKColName(strTableName)

        If String.IsNullOrEmpty(strPKCol) Then
            Throw New Exception(String.Format("table '{0}' _
            has no primary key / unique key", strTableName))
        End If

        ' define where clause for the reference tables
        Dim strNextLevelWhereClause As String
        If Not String.IsNullOrEmpty(strInFilter) Then
            strNextLevelWhereClause = String.Format("{0} _
            in ({1})", strFilterColumn, strInFilter)
        Else
            strNextLevelWhereClause = String.Format("{0} = _
            p_{0}", strFilterColumn)
        End If

        Dim strNextLevelFilter = String.Format("Select {0} from _
        {1} where {2}", strPKCol, strTableName, strNextLevelWhereClause)

        ' find the foreign key reference tables and delete them first
        Dim lstFKTableFKCol = iDic.GetFKReferences(strTableName)
        For Each kv In lstFKTableFKCol
            sb.AppendLine(gs(kv.Key, kv.Value, strNextLevelFilter))
        Next

        '  delete itself
        Dim strDeleteWhereClause As String
        If Not String.IsNullOrEmpty(strInFilter) Then
            strDeleteWhereClause = String.Format_
            ("{0} in ({1})", strFilterColumn, strInFilter)
        Else
            strDeleteWhereClause = String.Format_
            ("{0} = p_{0}", strFilterColumn)
        End If

        sb.AppendFormat("delete {0} where {1};", _
        strTableName, strDeleteWhereClause)
        sb.AppendLine()

        Return sb.ToString
    End Function

End Class

The UI collects user input of database type, connection string and table name. It verifies the input and calls the middle layer to generate the script, which is displayed inside the window and at the same time is copied to the clipboard. If the app throws an exception, UI will show the error message in red.

Points of Interest

The small utility helped me a lot. I wish the small tool can help other people.

History

• Initial version: Feb 17, 2011

The author, Ronald Cao, is a senior .NET Developer in TP Systems Co.