Multiple lines into one line when ; is found

Shell Programming and Scripting
1

I have sql's in a file separated by ";", need to put the sql in one single line till i find a ";" The input file is like this

SELECT         s.sid, 
                s.serial#, 
                p.spid as "OS PID", 
                s.username, 
                s.module, 
                st.value/100 as "CPU sec" 
FROM         v$sesstat st, 
                v$statname sn, 
                v$session s, 
                v$process p 
WHERE         sn.name                 = 'CPU used by this session' 
AND                 st.statistic#                 = sn.statistic# 
AND                 st.sid                         = s.sid 
AND                 s.paddr                 = p.addr 
AND                 s.last_call_et         < 3600 
AND                 s.logon_time                 > (SYSDATE - 240/1440) 
AND                 st.value/100                 > 10 
ORDER BY         st.value;SELECT         s.sid, 
                s.serial#, 
                p.spid as "OS PID", 
                s.username, 
                s.module, 
                st.value/100 as "CPU sec" 
FROM         v$sesstat st, 
                v$statname sn, 
                v$session s, 
                v$process p 
WHERE         sn.name                 = 'CPU used by this session' 
AND                 st.statistic#                 = sn.statistic# 
AND                 st.sid                         = s.sid 
AND                 s.paddr                 = p.addr 
AND                 s.last_call_et         < 3600 
AND                 s.logon_time                 > (SYSDATE - 240/1440) 
AND                 st.value/100                 > 10 
ORDER BY         st.value;SELECT         s.sid, 
                s.serial#, 
                p.spid as "OS PID", 
                s.username, 
                s.module, 
                se.time_waited, 
                se.event 
FROM         v$session_event se, 
                v$session s, 
                v$process p 
WHERE         s.last_call_et         < 3600 
AND                 s.logon_time                 > (SYSDATE - 240/1440) 
AND                 se.sid                         = s.sid 
AND                 s.paddr                 = p.addr 
AND                 se.time_waited         > 10 
ORDER BY         se.time_waited desc;

I need the output as follows,

SELECT s.sid, s.serial#,p.spid as "OS PID", s.username,s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic#= sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module,se.time_waited, se.event FROM v$session_event se, v$session s, v$process p WHERE s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND se.sid = s.sid AND s.paddr = p.addr AND se.time_waited > 10 ORDER BY se.time_waited desc;
SELECT s.sid, s.serial#,p.spid as "OS PID", s.username,s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic#= sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;

till i see ";" i need to consider that as one single line and in next line i ll get next sql statement till one ;

thanks
gopal

2

How about:

awk '$1=$1' RS=';' ORS=';\n' infile

output:

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited, se.event FROM v$session_event se, v$session s, v$process p WHERE s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND se.sid = s.sid AND s.paddr = p.addr AND se.time_waited > 10 ORDER BY se.time_waited desc;
3

Hey thanks... Its a good one. when i used ur command it just executes a "return" when it finds a ";". But i need it in a single line till ";" When I redirected it shows multiple lines there.

Kindly help me.

Ur o/p is contradicting to my o/p.

Thanks
gopal

4

What output did you get? CAnyou show an example? If you are using Solaris, use /usr/xpg4/bin/awk or nawk.

5

how about

'paste -s':  echo "a
b" | paste -s

gives "a\tb" output

see 'man paste' for even cooler operations (like specifiyig delimiters).

Finally you can split up your line again at ';' using:

sed -e 's/;/;\n/g'

In your example (named your.code)

cat your.code | paste -d" " -s | sed -e 's/;/;\n/g' -e 's/[ \t]\+/ /g' > perfect.txt

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "CPU sec" FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND st.value/100 > 10 ORDER BY st.value;
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited, se.event FROM v$session_event se, v$session s, v$process p WHERE s.last_call_et < 3600 AND s.logon_time > (SYSDATE - 240/1440) AND se.sid = s.sid AND s.paddr = p.addr AND se.time_waited > 10 ORDER BY se.time_waited desc;
6

It worked completely but a small change in the paste command

cat your.code | paste -d" " -s - | sed -e 's/;/;\n/g' -e 's/[ \t]\+/ /g' > perfect.txt

I just included a "-" after -s, it works and also used that awk command given above.

Thanks for all...:slight_smile:

7

I dont understand what was wrong with Scrutinizer's code it worked very well with me