Strip Comments from Oracle SQL and PL/SQL scripts

Category:

String Manipulation

Type:

Snippets

Difficulty:

Intermediate

Author: Wil Johnson

Version Compatibility:

More information:

This is a sub that accepts input and output file names, and will strip all comments from an Oracle SQL or PL/SQL script. It's especially handy if you use a script generator that includes excess useless comments.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Private Sub StripSQLComments(sInputFile As String, _
   sOutputFile As String)
    On Error GoTo ErrorHandler
    
    Dim sLine As String
    Dim bytInput As Byte
    Dim hInput As Integer
    Dim hOutput As Integer
    Dim bytBuffer(0 To 3) As Byte
    Dim bytQuoteType As Byte
    Dim lCommentCount As Long
    
    Const k_bytUnQuoted As Byte = 0
    Const k_bytSingleQuote As Byte = 39
    Const k_bytDoubleQuote As Byte = 34
    
    hInput = FreeFile
    Open sInputFile For Input As #hInput
    
    hOutput = FreeFile
    Open sOutputFile For Append As #hOutput
    
    Do Until VBA.EOF(hInput)
        
        bytInput = Asc(Input(1, #hInput))
        'push byte into FIFO queue
        bytBuffer(0) = bytBuffer(2)
        bytBuffer(2) = bytInput
        
        Select Case CStr(bytBuffer)
            
            'beginning a new block comment
            Case "/*":
                'is it outside of a quoted section?
                If bytQuoteType = k_bytUnQuoted Then
                    'clear the queue
                    bytBuffer(0) = 0
                    bytBuffer(2) = 0
                    
                    'eat chars until the end of the comment 
                      'section
                    lCommentCount = 1
                    Do Until lCommentCount = 0
                        bytInput = Asc(Input(1, #hInput))
                        'push byte into FIFO queue
                        bytBuffer(0) = bytBuffer(2)
                        bytBuffer(2) = bytInput
                        
                        Select Case CStr(bytBuffer):
                            'nested comment found
                            Case "/*":
                                lCommentCount = lCommentCount + 1
                                'clear the queue again
                                bytBuffer(0) = 0
                                bytBuffer(2) = 0
                            'end of a comment found
                            Case "*/":
                                lCommentCount = lCommentCount - 1
                                'clear the queue again
                                bytBuffer(0) = 0
                                bytBuffer(2) = 0
                            Case Else:
                                'do nothing
                        End Select
                    Loop
                    'clear the queue again
                    bytBuffer(0) = 0
                    bytBuffer(2) = 0
                Else
                    If bytBuffer(0) > 0 Then
                        sLine = sLine & Chr$(bytBuffer(0))
                    End If
                End If
            
            'a new single line comment
            Case "--":
                'is it outside of a quoted section?
                If bytQuoteType = k_bytUnQuoted Then
                    'clear the queue
                    bytBuffer(0) = 0
                    bytBuffer(2) = 0
                    'eat chars until the end of the line
                    Do Until CStr(bytBuffer) = vbCrLf
                        bytInput = Asc(Input(1, #hInput))
                        'push byte into FIFO queue
                        bytBuffer(0) = bytBuffer(2)
                        bytBuffer(2) = bytInput
                    Loop
                    'clear the queue again
                    bytBuffer(0) = 0
                    bytBuffer(2) = 0
                Else
                    If bytBuffer(0) > 0 Then
                        sLine = sLine & Chr$(bytBuffer(0))
                    End If
                End If
            
            'end of line, write line to file
            Case vbCrLf:
                Print #hOutput, sLine
                sLine = ""
                'clear the queue
                bytBuffer(0) = 0
                bytBuffer(2) = 0
            
            'wait for buffer to refill
            Case 0:
                'do nothing
            
            Case Else:
                If bytBuffer(0) > 0 Then
                    sLine = sLine & Chr$(bytBuffer(0))
                End If
                Select Case bytBuffer(2)
                    Case k_bytSingleQuote, k_bytDoubleQuote:
                        If bytQuoteType = k_bytUnQuoted Then
                            'starting new quoted section
                            bytQuoteType = bytBuffer(2)
                        Else
                            'possibly ending current one
                            If bytBuffer(2) = bytQuoteType Then
                                bytQuoteType = k_bytUnQuoted
                            End If
                        End If
                End Select
                '** temp
                DoEvents
        End Select
    Loop
    
    If CStr(bytBuffer) <> "" Then
        'didn't end on a comment
        If sLine <> "" Then
            'still output waiting to be written
            If Right$(sLine, 2) <> CStr(bytBuffer) Then
                'one or more bytes still need to be added to line
                If Right$(sLine, 1) <> Chr$(bytBuffer(0)) Then
                    'entire buffer needs to be added
                    sLine = sLine & CStr(bytBuffer)
                Else
                    'just the last byte needs to be added
                    sLine = sLine & Chr$(bytBuffer(2))
                End If
            End If
        End If
    End If
    
    If sLine <> "" Then
        Print #hOutput, sLine
    End If

ErrorHandler:
    'nothing to add here yet
    Resume Done
    
Done:
    Close #hInput
    Close #hOutput

End Sub