A Small Utility to Generate Cascading Delete SQL Script in Oracle






4.33/5 (7 votes)
The article is to introduce a utility to generate cascading delete script for an Oracle table.
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.