How to extract sql statements with awk

A common problem is to extract sql statements out of a sql file e.g. produced by full metadata impdp. Often these files are too big to be edited with vi.

Following statement extracts all sql statements with “CREATE MATERIALIZED VIEW” at the beginning with awk:


cat impdp_full_metadata.sql | awk 'BEGIN{ FLAG = 0 } { if( $0 ~ /CREATE MATERIALIZED VIEW/ ) FLAG=1; if(FLAG != 0) print $0; if ($0 ~ /;/ ) FLAG=0; }'


Explanation: At beginning of awk state FLAG is set to 0, also when semicolon is in the line. If flag is not 0, the line is printed, if pattern of statement is found, flag is set to 1 which causes line with pattern and all subsequence lines are printed up to the next line which contains a semicolon. This works also for statements spanning more than one line.