Please help in populating output in table using shell scripting

Shell Programming and Scripting
1

Below is my code where i tried using table tag to print out put in table but its not working

#!/bin/ksh

#[[ -f ./.profile ]] && . ./.profile 2>/dev/null

if test -f '.profile'; then . ./.profile; fi;

#. .profile

LOG_DIR=/app/rpx/jobs/scripts/just/logs
  sendEmail()
    {
        pzCType="$1"; pzTitle="$2"; pzMsg="$3"; pzFrom="$4"; pzTo="$5"; pzFiles="$6"; pzReplyTo="$7"
        (
          echo "From: $pzFrom\nTo: $pzTo\nSubject: $pzTitle${pzReplyTo:+\nReply-To: $pzReplyTo}"

              [[ -n "$pzCType" ]] && echo "Content-Type: $pzCType"

              echo
               echo "$pzMsg"
                  [[ -n "$pzFiles" ]] &&
                  echo &&
                  for pzFile in $pzFiles
                  do
                    [[ -f "$pzFile" ]] && uuencode "$pzFile" "$pzFile"
                  done

                ) | mail "$pzTo"
          }

## all pending subscriber .

pending=`sqlplus -s pag_app/abcdef@prpx1-vip.unix.gsm1900.org:7779/prpx.unix.gsm1900.org <<EOF
set feedback off;
set heading off;
set trimspool on;
set pagesize 0;
SET LINESIZE 180;

select '<tr><td>'||msisdn||'</td><td>'||status_changed_time||'</td><td>'||rate_plan_id||'</td><td>'||status||'</td></tr>' from sub
scriber where status = 'P' and status_changed_time < sysdate order by status_changed_time  desc;
EOF`

activating=`sqlplus -s pag_app/abcdef@prpx1-vip.unix.gsm1900.org:7779/prpx.unix.gsm1900.org <<EOF
set feedback off;
set heading off;
set trimspool on;
set pagesize 0;
SET LINESIZE 180;

select '<tr><td>'||msisdn||'</td><td>'||status_changed_time||'</td><td>'||rate_plan_id||'</td><td>'||status||'</td></tr>' from sub
scriber where status = 'I' and status_changed_time < sysdate order by status_changed_time  desc;
EOF`

final="Pending
    <table><tr><td>Msisdn    </td><td>      Date    </td><td>    RP Status </td></tr>
    $pending
    </table>
    Activating
        <table> <tr><td>Msisdn     </td><td>     Date   </td><td>     RP Status  </td></tr>
        $activating
        </table>"
sendEmail '' "Pending_Activating" "$final" 'ankit.gupta92@gmail.com' 'balaji.k@gmail.com' " " ' '
exit 0;

---------- Post updated at 05:23 AM ---------- Previous update was at 05:22 AM ----------

Below is the output of above

Pending
    <table><tr><td>Msisdn    </td><td>      Date    </td><td>    RP Status </td></tr>
    <tr><td>15204503281</td><td>19-FEB-15</td><td>183</td><td>P</td></tr>
<tr><td>19155404218</td><td>19-FEB-15</td><td>183</td><td>P</td></tr>
<tr><td>18329976221</td><td>19-FEB-15</td><td>179</td><td>P</td></tr>
<tr><td>16127078234</td><td>19-FEB-15</td><td>183</td><td>P</td></tr>
<tr><td>15208738121</td><td>19-FEB-15</td><td>173</td><td>P</td></tr>
<tr><td>14016880957</td><td>19-FEB-15</td><td>707</td><td>P</td></tr>
<tr><td>12525487753</td><td>19-FEB-15</td><td>173</td><td>P</td></tr>
<tr><td>12566400680</td><td>19-FEB-15</td><td>717</td><td>P</td></tr>
    </table>
    Activating
	<table> <tr><td>Msisdn     </td><td>     Date   </td><td>     RP Status  </td></tr>
	</table>
	<tr><td>17027857667</td><td>19-FEB-15</td><td>156</td><td>I</td></tr>
<tr><td>12068591961</td><td>15-FEB-15</td><td>176</td><td>I</td></tr>
<tr><td>12064460516</td><td>15-FEB-15</td><td>176</td><td>I</td></tr>
<tr><td>19544713197</td><td>31-JAN-15</td><td>156</td><td>I</td></tr>
2

Did you know that SQLPlus supports html markup ?

set markup html on
3

I tried set markup html on, still its not working

#!/bin/ksh

#[[ -f ./.profile ]] && . ./.profile 2>/dev/null

if test -f '.profile'; then . ./.profile; fi;

#. .profile

LOG_DIR=/app/rpx/jobs/scripts/just/logs
  sendEmail()
    {
        pzCType="$1"; pzTitle="$2"; pzMsg="$3"; pzFrom="$4"; pzTo="$5"; pzFiles="$6"; pzReplyTo="$7"
        (
          echo "From: $pzFrom\nTo: $pzTo\nSubject: $pzTitle${pzReplyTo:+\nReply-To: $pzReplyTo}"

              [[ -n "$pzCType" ]] && echo "Content-Type: $pzCType"

              echo
               echo "$pzMsg"
                  [[ -n "$pzFiles" ]] &&
                  echo &&
                  for pzFile in $pzFiles
                  do
                    [[ -f "$pzFile" ]] && uuencode "$pzFile" "$pzFile"
                  done

                ) | mail "$pzTo"
          }

## all pending subscriber .

pending=`sqlplus -s pag_app/abcdef@prpx1-vip.unix.gsm1900.org:7779/prpx.unix.gsm1900.org <<EOF
set feedback off;
set markup html on;
set heading off;
set trimspool on;
set pagesize 0;
SET LINESIZE 180;

select '<tr><td>'||msisdn||'</td><td>'||status_changed_time||'</td><td>'||rate_plan_id||'</td><td>'||status||'</td></tr>' from sub
scriber where status = 'P' and status_changed_time < sysdate order by status_changed_time  desc;
EOF`

activating=`sqlplus -s pag_app/abcdef@prpx1-vip.unix.gsm1900.org:7779/prpx.unix.gsm1900.org <<EOF
set feedback off;
set markup html on;
set heading off;
set trimspool on;
set pagesize 0;
SET LINESIZE 180;

select '<tr><td>'||msisdn||'</td><td>'||status_changed_time||'</td><td>'||rate_plan_id||'</td><td>'||status||'</td></tr>' from sub
scriber where status = 'I' and status_changed_time < sysdate order by status_changed_time  desc;
EOF`

final="Pending
    <table><tr><td>Msisdn    </td><td>      Date    </td><td>    RP Status </td></tr>
    $pending
    </table>
    Activating
        <table> <tr><td>Msisdn     </td><td>     Date   </td><td>     RP Status  </td></tr>
        $activating
        </table>"
sendEmail '' "Pending_Activating" "$final" 'ankit.gupta92@gmail.com' 'balaji.k@gmail.com' " " ' '
exit 0;

Output is as below

Pending
    <table><tr><td>Msisdn    </td><td>      Date    </td><td>    RP Status </td></tr>
    <p>
<table border='1' width='90%' align='center' summary='Script output'> <tr> <td> <tr><td>12198057608</td><td>21-FEB-15</td><td>173</td><td>P</td></tr>
</td>
</tr>
</table>
<p>
    </table>
    Activating
	<table> <tr><td>Msisdn     </td><td>     Date   </td><td>     RP Status  </td></tr>
	<p>
<table border='1' width='90%' align='center' summary='Script output'> <tr> <td> <tr><td>19175889670</td><td>21-FEB-15</td><td>183</td><td>I</td></tr>
</td>
</tr>
<tr>
<td>
<tr><td>17027857667</td><td>19-FEB-15</td><td>156</td><td>I</td></tr>
</td>
</tr>
<tr>
<td>
<tr><td>12068591961</td><td>15-FEB-15</td><td>176</td><td>I</td></tr>
</td>
</tr>
<tr>
<td>
<tr><td>12064460516</td><td>15-FEB-15</td><td>176</td><td>I</td></tr>
</td>
</tr>
<tr>
<td>
<tr><td>19544713197</td><td>31-JAN-15</td><td>156</td><td>I</td></tr>
</td>
</tr>
</table>
<p>
	</table>
4

I think you misunderstood what I was suggesting...

try this:

activating=`sqlplus -s user/passwd@host <<EOF 
set feedback off; 
set markup html on; 
set trimspool on; 
set pagesize 0; 
SET LINESIZE 180;  
select  
    msisdn, status_changed_time as date,rate_plan_id as RP ,status 
  from subscriber
  where status = 'I' 
    and status_changed_time < sysdate 
  order by status_changed_time  desc; 
EOF`

and use the resulting output as the activating table

5 
#!/bin/ksh

#[[ -f ./.profile ]] && . ./.profile 2>/dev/null

if test -f '.profile'; then . ./.profile; fi;

#. .profile

LOG_DIR=/app/rpx/jobs/scripts/just/logs
  sendEmail()
    {
        pzCType="$1"; pzTitle="$2"; pzMsg="$3"; pzFrom="$4"; pzTo="$5"; pzFiles="$6"; pzReplyTo="$7"
        (
          echo "From: $pzFrom\nTo: $pzTo\nSubject: $pzTitle${pzReplyTo:+\nReply-To: $pzReplyTo}"

              [[ -n "$pzCType" ]] && echo "Content-Type: $pzCType"

              echo
               echo "$pzMsg"
                  [[ -n "$pzFiles" ]] &&
                  echo &&
                  for pzFile in $pzFiles
                  do
                    [[ -f "$pzFile" ]] && uuencode "$pzFile" "$pzFile"
                  done

                ) | mail "$pzTo"
          }

## all pending subscriber .

pending=`sqlplus -s pag_app/pass@prpx1-vip.unix.gsm1900.org:7779/prpx.unix.gsm1900.org <<EOF
set feedback off;
set markup html on;
set trimspool on;
set pagesize 0;
SET LINESIZE 180;

select msisdn, status_changed_time, rate_plan_id, status from subscriber where status = 'P' and status_changed_time < sysdate orde
r by status_changed_time  desc;
EOF`

activating=`sqlplus -s pag_app/pass@prpx1-vip.unix.gsm1900.org:7779/prpx.unix.gsm1900.org <<EOF
set feedback off;
set markup html on;
set trimspool on;
set pagesize 0;
SET LINESIZE 180;

select msisdn, status_changed_time, rate_plan_id, status from subscriber where status = 'I' and status_changed_time < sysdate orde
r by status_changed_time  desc;
EOF`

final="Pending
Msisdn      Date   RP Status
$pending
Activating
Msisdn      Date   RP Status
$activating"

sendEmail '' "Pending_Activating" "$final" 'ankit.gupta92@gmail.com' 'ankit.gupta92@gmail.com' " " ' '

When i tried as above, it just giving me tr, td tags

as below

6

Looks an HTML page with a perfect table to me, once it's enclosed in <html> tags...

---------- Post updated at 09:49 ---------- Previous update was at 09:46 ----------

Why don't you leave the entire text creation to your DB system with markup html on, to be on the safe side?