Automating Oracle SQL Review with SOAR: A Step‑by‑Step Guide

Background

Oracle DBA team needed to automatically review developer‑submitted SQL scripts. Manual review was slow and error‑prone, so an automated solution based on SRE principles was required.

Automation Approach

More than a hundred review rules were extracted, e.g.:

All new objects must be prefixed with the user name.

SEQUENCE creation must specify a CACHE value ≥ 200.

DML statements such as DELETE or UPDATE must include a WHERE clause.

…

To avoid implementing a checker from scratch, the open‑source SOAR (SQL Optimizer And Rewriter) was chosen and extended with Oracle‑specific rules.

SOAR Overview

SOAR was originally built for MySQL but its analysis framework can be reused for other databases. By disabling the built‑in MySQL rules and adding custom Oracle rules, the tool can evaluate Oracle scripts without modifying the core parser.

Toolchain Installation

1. Install Go

$ wget https://dl.google.com/go/go1.10.2.linux-amd64.tar.gz
$ tar zxvf go1.10.2.linux-amd64.tar.gz
$ export GOROOT=/path/to/go
$ export GOPATH=/path/to/go
$ export PATH=$PATH:$GOROOT/bin
$ go version

2. Install Git

$ yum -y install git
$ git --version

3. Build SOAR from source

$ mkdir workspace && cd workspace
$ go get -d github.com/XiaoMi/soar
$ cd $GOPATH/src/github.com/XiaoMi/soar && make
$ ./soar -version

Custom Rule Development

Using an IDE (e.g., Goland) the team added heuristic checks for Oracle in the SOAR source tree. After editing rule files, the default MySQL rules were disabled in soar.yaml. The compiled binary and the configuration file were placed in the execution directory.

Web UI Integration

A lightweight Flask‑based web UI (soar‑web) was customized to let developers upload SQL files for analysis.

1. Install Python 3.6 and dependencies

$ yum install python36 python36-pip
$ pip install Flask pymysql pycryptodome

2. Download and unpack soar‑web

$ wget https://codeload.github.com/xiyangxixian/soar-web/zip/master -O soar-web-master.zip
$ unzip soar-web-master.zip
$ cd soar-web-master

Copy the custom SOAR binary and soar.yaml into the web directory and adjust core/common.py so the UI can invoke ./soar on the uploaded file.

Result

The Oracle SQL review tool achieved roughly 90 % automated coverage of routine checks, dramatically reducing DBA workload. The framework can be extended to support additional databases.