PL/SQL Code Analysis with Toad DevOps Toolkit and Python

pip install pywin32

If you receive an error that goes something like… ‘pip’ is not recognized as an internal or external command, operable program or batch file. You’ll need to add it to your system path. More on that here.

Step 1. Toad DevOps Toolkit and Python

You can grab the script that’s available here or you can open a text editor or Python IDE and create a new file named tdt_codeanalysis_example.py and add the code below.

Setting up Code Analysis

Our script only needs to do a few things. Specify an Oracle connection string, create a Toad DevOps Toolkit instance, supply a few code analysis options, and execute.

# Import win32com.client so that we have easy access to COM interfaces.
import win32com.client

# Connection information used to connect to your Oracle database.
source_connection = "SCOTT/SCOTT_PASSWORD@ORA_DATABASE"

# Start Toad DevOps Toolkit.
tdt = win32com.client.Dispatch("Toad.ToadAutoObject")

# Create and assign a Toad DevOps Toolkit connection.
tdt.CodeAnalysis.Connection = tdt.Connections.NewConnection(source_connection)
# Set code analysis report information.
tdt.CodeAnalysis.ReportName = "CA Report"
tdt.CodeAnalysis.OutputFolder = "C:\Temp"
tdt.CodeAnalysis.ReportFormats.IncludeHTML = True

# Set database object information to analyze.
database_object = tdt.CodeAnalysis.DBObjects.Add()
database_object.ObjectName = "ADD_EXPENSES"
database_object.ObjectOwner = "SCOTT"
database_object.ObjectType = "FUNCTION"

# Execute Code Analysis.
tdt.CodeAnalysis.Execute()

tdt.Quit()

A brief explanation of the script above:

• Line 2: Import pywin32 to provide access to Toad DevOps Toolkit’s COM interface.
• Line 5: Create an Oracle connection string. This will be unique to your database setup.
• Line 8: Create an instance of Toad DevOps Toolkit.
• Line 11–15: Create a new connection from your connection string and specify some default reporting options.
• Line 18–21: This is the database object that we’ll be running analysis on. This will be unique to your database setup.
• Line 24: Execute code analysis using the options provided.
• Line 26: Exit Toad DevOps Toolkit.

Step 2. Run our Code Analysis Script

Go ahead and run the script from your IDE or from the command line:

python tdt_codeanalysis_example.py

When running your script if you receive the error… ImportError: DLL load failed, check out this post (it’s an easy fix).

If you followed the same report settings as the example above, you should have a newly created report named C:\Temp\CA Report.htmlthat looks similar to this:

That’s all there is to it. Hopefully this is pretty straightforward, especially if you’ve used Toad for Oracle’s Code Analysis in the past.

I hope this has inspired you to take a look at adding automated database code analysis to your DevOps pipeline. If you have any suggestions or questions let me know in the comments or on our forum. The full script is available here and more Toad DevOps Toolkit information can be found here.