#!/bin/bash
#fmt_sql.sh: Format a long SQL of multiple lines into a readable and runnable SQL
#Please pass <name> of the file storing the unformatted SQL as argument and the SQL in it will be formatted. The file named <name>.bk will store its original SQL.
#Make sure trailing space(s) are preserved in the input file. For example, if the line as seen in the source (e.g. v$sql*) ends with "FROM TAB1 ", the ending space must be present in the input file.
#If the SQL is a DML, after running this program, you may need to manually shift the word "insert", "delete", or "update" to the beginning of line.
#If you want the line length to be other than 100, change the number 100 on the line of `fold' below.

if [[ -z "$*" ]]; then
 echo 'Pass the name of file that stores the long SQL as argument!'
 exit 0
fi
cp -p $1 $1.bk #comment out this line if you don't need a backup
perl -pi -e 's/\n//g;s/(\bSELECT\b)/\n$1/ig;s/(\bFROM\b)/\n$1/ig;s/(\bWHERE\b)/\n$1/ig' $1
fold -w 100 -s $1 > $$ #max length of line 100; change as you wish
perl -pi -e 's/^/  / unless /(^SELECT|^FROM|^WHERE)/i' $$
mv $$ $1