#!/bin/bash #fmt_sql.sh: Format a long SQL of multiple lines into a readable and runnable SQL #Please pass of the file storing the unformatted SQL as argument and the SQL in it will be formatted. The file named .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