2 Replies Latest reply: May 15, 2014 10:00 AM by rvelez RSS

    Check for Empty Export

    rvelez

      Currently I have a scheduled process that creates and export file and emails accordingly.  Sometimes, the export file is empty (which is ok) but will still email the blank file.  Is there a way to check if the file is empty so I can determine whether to send it or not?  Any help would be greatly appreciated.

        • Check for Empty Export
          Gareth Horton

          Hi,

           

          This is a job for the scripting functionality of MDP 7 or higher.

           

          I am going from the assumption that you are using either 7 or 8.

           

          You have two choices here:

           

          1. Consider this to be a failure and force the job to fail, meaning the distributions (in this case, e-mail) will not take place.  This will make the job fail with the error:

           

          "User script function PostExport returned false"

           

          2. Consider this to be normal operation, everything will work normally, the file will get created, but just not e-mailed when it is has zero rows.  The job will complete successfully.

           

          1 is slightly simpler, in that you can create a distribution via the user interface, and just add some code in the PostExport script to make it fail under that condition. 

           

          However, if you have multiple exports, then if only one creates zero records, all distributions will fail.

           

          Note that you will have to edit the code to suit your exact needs:

           

          [font="courier"]Dim itemList As JobLogItemList

          Dim item As JobLogItem

          Dim n As Integer

           

          itemList = Log.GetAllOutputItems()

          If itemList.Count = 0 Then  'No exports exist, don't do anything.

           

          Else 'go through the list of exports

               n = 0

               For Each item In itemList

                    n = n + 1

                          'if there are no records in the export, force a failure

                          'by returning ExportCompleted as False, telling Data Pump that the exports failed.

                        

                    if (item.recordcount=0)   then

           

          ExportCompleted = False

          Return ExportCompleted

           

                          

                    End if

               Next

          End If /font[/quote]2. The more elegant approach is to create an email distribution dynamically, based on the result of a test if the record count is zero.

           

          Look at the Data Pump scripting help at the AddDistribution_Email and AddDistribution_EmailZip methods for more information on the arguments.

           

          This code would also go in the PostExport Script

           

          [font="courier"]Dim itemList As JobLogItemList

          Dim item As JobLogItem

          Dim n As Integer

          Dim names as String

           

          names = "recipient@test.com;anotherrecipient@test.com"

           

          itemList = Log.GetAllOutputItems()

           

          If itemList.Count = 0 Then  'No exports exist, do nothing

           

          Else

               n = 0

               For Each item In itemList

                    n = n + 1

           

                    'If the record count is greater than zero ...

                    if (item.recordcount>0) then

           

                    'Note you can also add another check if you have multiple exports and you only want to email a specific one...

                    'if (item.recordcount>0) and (item.location = "C:Program FilesDatapumpExportNoTable Overwrite.txt") then

           

                         'This uses the AddDistribution_Email method to create an email distribution on the fly and attach the file.

                         log.AddDistribution_Email(item.location,names,"datapump@test.com","","Test Email","This is a test for file " + item.location,True)

                     

               End if

               Next

          End If /font[/quote]Hope this helps.

           

          Gareth

           

          Originally posted by RVelez:

          Currently I have a scheduled process that creates and export file and emails accordingly.  Sometimes, the export file is empty (which is ok) but will still email the blank file.  Is there a way to check if the file is empty so I can determine whether to send it or not?  Any help would be greatly appreciated. /b[/quote]

          • Check for Empty Export
            rvelez

            Thanks.  I will give this a try.