Hi,

I have a script in which a lot of sql queries are embedded ie,"Select .....;".My purpose is to document all the dml statements in the script along with the line number.

I am thinking of writing a perlscript or by using awk/sed scripts for extracting all the 'select' statements/queries along with the line number.
I need the entire query,ie starting from Select to semicolon(.

Can anyone please help me to achieve this.Your help is appreciated

Hey, a sample file would help me out in getting what u want.
The reason for this is if a its a normal select statment then it completes in one line.
If the SQL statement has sub-queries which spans more that one line then we would have to concatenate it so that it becomes one line and that can be presented ....


Let us know regarding this.

Thanks for the reply.
The file contains select statements which spans over multiple lines.I can say the max number of lines as 10.

Regards
Dileep

First of all i dont think u need any scripts..
U can do with a single line command along with pipes
Here is the command

sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n" >> targetfile

INPUT FILE : test
OUTPUT FILE : targetfile

Assumptions :
*************
1. The input file doesnt have any blank lines.
2. If it has then use the following sed command
sed '/^$/d' <sourcefile> <targetfile>

Here is my input file
cat test
1 SELECT * FROM DUAL;
2 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I ;
3 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
4 ASDFASDFASDFASDFSAD
5 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );

THINGS THAT ARE TAKEN CARE OFF :
*********************************
1. There might be a single SELECT STATEMENT (no sub-queries).
2. There might be sub-queries which are written continiously (without breaking)
3. There might be sub-queries which are written in different statements.


Now the command :
sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n" >> targetfile

Explanation of the commands :
*****************
***sed '/;/G' test ***
*****************

This inserts a new line after the end of a complete SQL statment ie. when it encounters a ";" it inserts a line after it.

*****************
***grep "SELECT" ***
*****************
The output of the command sed '/;/G' test is piped and only the SELECT queries are grepped from that.

********************************
****sed -e :a -e '$!N;s/\n/~/g;ta'******
********************************

The output of the previous command sed '/;/G' test | grep "SELECT" is sent as input to the above command. This is manily done becoz if there are queries which span more than one line which is not continuous. (they might have pressed enter key and continued)

**************
***tr "~" "\n"***
**************
This will replace all "~" characters to "\n"
sed '/;/G' test | grep "SELECT" | sed -e :a -e '$!N;s/\n/~/g;ta' | tr "~" "\n"
1 SELECT * FROM DUAL;
2 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I ;
3 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H I A B C D E F G H
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );
5 SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I A B C D E F G H I A B
I A B C D IN ( SELECT A B C D E F G H I A B C D E F G H IA B C D E F G H I A B C D E F G H I );

Here is the file which u needed....