Joining broken lines with awk or perl

Shell Programming and Scripting
1

Hi,
I have a huge file with sql broken statements like:

 
PP3697HB @@@@0
<<<<<<Record has been deleted as per PP3697HB>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
ND IND = 75);

I need all these broken lines to be recombined to a single line.

The line should look like:

 
PP3697HB @@@@0<<<<<<Record has been deleted as per PP3697HB>>>>>>FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 75);

How can I achieve this in perl/awk.

We can say that the start of the line must be '^PP(.)' and the end of sql statement must be '(.);$'

Let me know if you have difficulty understand the problem and I will try to explain again.

2 
perl -ne 'chomp; (/^PP/../;$/) && print' file
3 
$ cat sql.txt
PP3697HB @@@@0
<<<<<<Record has been deleted as per PP3697HB>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
ND IND = 75);
PP3697HB @@@@1
<<<<<<Record has been deleted as per PP3697HC>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
ND IND = 76);

$ sed -n '/^PP/ { p; n; p; n; :k N; /;$/ { s/\n//g; p; d; }; bk }' sql.txt
PP3697HB @@@@0
<<<<<<Record has been deleted as per PP3697HB>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 75);
PP3697HB @@@@1
<<<<<<Record has been deleted as per PP3697HC>>>>>>
FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 76);
4 
$
$
$ cat -n broken.sql
     1  PP3697HB @@@@0
     2  <<<<<<Record has been deleted as per PP3697HB>>>>>>
     3  FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
     4  RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
     5  .department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
     6  tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.r
     7  unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
     8  ND IND = 75);
     9  PP3697HB @@@@1
    10  <<<<<<Record has been deleted as per PP3697XY>>>>>>
    11  FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHE
    12  RE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur
    13  .department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.s
    14  tatus = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261367'AND rc.r
    15  unet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' A
    16  ND IND = 85);
$
$
$ perl -lne 'BEGIN {undef $/} while (/^(PP.*?;)$/msg) { ($x=$1) =~ s/\n//g; print $x}' broken.sql
PP3697HB @@@@0<<<<<<Record has been deleted as per PP3697HB>>>>>>FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261366'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 75);
PP3697HB @@@@1<<<<<<Record has been deleted as per PP3697XY>>>>>>FROM sys.xtab_ref rc,sys.xtab_sys f,sys.domp ur WHERE rc.milf = ur.milf  AND rc.molf = f.molf AND ur.dept = 'SWIT'AND ur.department = 'IND' AND share = '2' AND ur.status = 'DONE' AND f.status = 'TRUE' AND rc.OPERATOR = '=' AND rc.VALUE = '261367'AND rc.runet IN (SELECT milf FROM sys.domp WHERE change = 'OVO' AND IND = 85);
$
$
$
$
5

Slurping in the whole file may not work in this case as the OP mentions that the input file is huge.
The following should do the trick (depending on the input) without being memory-intensive:

perl -ln0x3B -e 'if(/^\n?PP/) { s/\n//g; print $_, $/}' file