I want query output to send on mail using table tag and output should be in table

Shell Programming and Scripting
1 
#! /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 msisdn , status_changed_time, rate_plan_id,  status from subscriber where status = 'P' and status_changed_time < sysdate or
der by 2 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 msisdn , status_changed_time, rate_plan_id,  status from subscriber where status = 'I' and status_changed_time < sysdate or
der by 2 desc;
EOF`

final="Pending
$pending

Activating
$activating"

sendEmail '' "Pending_Activating" "$final" 'xyz@gmail.com' 'abc@gmail.com' " " ' '

echo $final > $LOG_DIR/pending_`date +%d%m%y`.log

Above output is as below

Pending
16612969688 11-FEB-15	       707 P
16309354405 09-FEB-15	       152 P
14049529349 08-FEB-15	       183 P
16462516471 03-FEB-15	       179 P

Activating
12485580879 10-FEB-15	       156 I
16097271993 09-FEB-15	       156 I
15202489178 07-FEB-15	       183 I
15057305084 07-FEB-15	       183 I
13615489396 07-FEB-15	       183 I
17022926929 05-FEB-15	       183 I
13602235799 05-FEB-15	       183 I
15713314401 05-FEB-15	       183 I
16236285136 04-FEB-15	       156 I
19103369407 02-FEB-15	       156 I
19544713197 31-JAN-15	       156 I
18326291677 09-JAN-15	       174 I
15302079579 29-DEC-14	       173 I

but i want it should in table where tables border should be visible

Please help in this regard

2

A few points:-

  • To force ksh, the first line should be #!/bin/ksh , not #! /bin/ksh
  • By hard coding your credentials in sqlplus -s pag_app/wi... anyone can see them with ps whilst your query is running.
    Better to put them in the block underneath with the set feedback off; etc.
  • To get the column labels, I would suggest not turning off the headings, i.e. remove set heading off;

I hope that this helps.

Robin

3

Well still not getting output with table border in it

4

I have completely misunderstood your requirement. It's not entirely clear what you need, so do you want the output to look more like this?:-

Pending 
  -----------   ----------  ---------   -
| 16612969688 | 11-FEB-15 |       707 | P |
| 16309354405 | 09-FEB-15 |       152 | P |
| 14049529349 | 08-FEB-15 |       183 | P |
| 16462516471 | 03-FEB-15 |       179 | P |

Activating
  -----------   ----------  ---------   -
| 12485580879 | 10-FEB-15 |       156 | I |
| 16097271993 | 09-FEB-15 |       156 | I |
| 15202489178 | 07-FEB-15 |       183 | I |
| 15057305084 | 07-FEB-15 |       183 | I |
| 13615489396 | 07-FEB-15 |       183 | I |
| 17022926929 | 05-FEB-15 |       183 | I |
| 13602235799 | 05-FEB-15 |       183 | I |
| 15713314401 | 05-FEB-15 |       183 | I |
| 16236285136 | 04-FEB-15 |       156 | I |
| 19103369407 | 02-FEB-15 |       156 | I |
| 19544713197 | 31-JAN-15 |       156 | I |
| 18326291677 | 09-JAN-15 |       174 | I |
| 15302079579 | 29-DEC-14 |       173 | I |

Do you want the output like this (to feed into a spreadsheet, for example) :-

Pending
16612969688,11-FEB-15,       707,P
16309354405,09-FEB-15,       152,P
14049529349,08-FEB-15,       183,P
16462516471,03-FEB-15,       179,P

Activating
12485580879,10-FEB-15,       156,I
16097271993,09-FEB-15,       156,I
15202489178,07-FEB-15,       183,I
15057305084,07-FEB-15,       183,I
13615489396,07-FEB-15,       183,I
17022926929,05-FEB-15,       183,I
13602235799,05-FEB-15,       183,I
15713314401,05-FEB-15,       183,I
16236285136,04-FEB-15,       156,I
19103369407,02-FEB-15,       156,I
19544713197,31-JAN-15,       156,I
18326291677,09-JAN-15,       174,I
15302079579,29-DEC-14,       173,I

if you could post some expected results (in CODE tags) then that gives us a target to work on.

Robin

5

Yes, i wanted it to be like this

  -----------   ----------  ---------   -
| 16612969688 | 11-FEB-15 |       707 | P |
| 16309354405 | 09-FEB-15 |       152 | P |
| 14049529349 | 08-FEB-15 |       183 | P |
| 16462516471 | 03-FEB-15 |       179 | P |

Activating
  -----------   ----------  ---------   -
| 12485580879 | 10-FEB-15 |       156 | I |
| 16097271993 | 09-FEB-15 |       156 | I |
| 15202489178 | 07-FEB-15 |       183 | I |
| 15057305084 | 07-FEB-15 |       183 | I |
| 13615489396 | 07-FEB-15 |       183 | I |
| 17022926929 | 05-FEB-15 |       183 | I |
| 13602235799 | 05-FEB-15 |       183 | I |
| 15713314401 | 05-FEB-15 |       183 | I |
| 16236285136 | 04-FEB-15 |       156 | I |
| 19103369407 | 02-FEB-15 |       156 | I |
| 19544713197 | 31-JAN-15 |       156 | I |
| 18326291677 | 09-JAN-15 |       174 | I |
| 15302079579 | 29-DEC-14 |       173 | I |
6

You will simply need to adjust your SELECT statement to include these. As an example:-

SELECT '|'||username||'|'||profile||'|' FROM DBA_USERS ;

I hope that this helps,
Robin

7

Thanks for the help, now it giving me output as below

Pending
|16612969688|11-FEB-15|707|P|
|16309354405|09-FEB-15|152|P|
|14049529349|08-FEB-15|183|P|
|16462516471|03-FEB-15|179|P|

Activating
|12485580879|10-FEB-15|156|I|
|16097271993|09-FEB-15|156|I|
|15202489178|07-FEB-15|183|I|
|15057305084|07-FEB-15|183|I|
|13615489396|07-FEB-15|183|I|
|17022926929|05-FEB-15|183|I|
|13602235799|05-FEB-15|183|I|
|15713314401|05-FEB-15|183|I|
|16236285136|04-FEB-15|156|I|
|19103369407|02-FEB-15|156|I|
|19544713197|31-JAN-15|156|I|
|18326291677|09-JAN-15|174|I|
|15302079579|29-DEC-14|173|I|

Can I get output like this as well, it will be more good looking

 -----------   ----------  ---------   -
| 16612969688 | 11-FEB-15 |       707 | P |
| 16309354405 | 09-FEB-15 |       152 | P |
| 14049529349 | 08-FEB-15 |       183 | P |
| 16462516471 | 03-FEB-15 |       179 | P |
8

If you show us the SELECT statement you are now using and we can probably adjust it.

Is there a reason for having it in boxes? It will make it more difficult to use the data later on. For a report to be published, it's usual to have a comma separated file that you can read into a spreadsheet. You can graph it then, which can be much better for understanding the information you wish to convey rather that just the raw data.

Robin

9

Is your goal to create a mail with html body that has the result of your query as content? You'll need to generate a html report first (find the documentation here) and use an appropriate content type.
If you just want the columns seperated have a look at the SQL*Plus setting colsep .

1 Like
10

Can you pelase help how can i bring that dotted lines on top of my row value

-----------   ----------  ---------   -
| 16612969688 | 11-FEB-15 |       707 | P |

---------- Post updated 02-12-15 at 12:18 AM ---------- Previous update was 02-11-15 at 11:40 PM ----------

Well as you said using HTML tags i tried but i thinking i am missing something

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;
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
HEAD "<TITLE>Pending</TITLE>
<STYLE types='text/css'>
<!-- BODY {background: #FFFFC6}
</STYLE>"
BODY "TEXT='#FF00Ff'"
TABLE "WIDTH='90%' BORDER='5'"

SPOOL report.html

select msisdn,status_changed_time,rate_plan_id,status from subscriber where status = 'P' and status_changed_time < sysdate order b
y status_changed_time  desc;

SPOOL OFF
EOF`

Output is as below

SP2-0734: unknown command beginning "HEAD "<TIT..." - rest of line ignored.
<br>
SP2-0734: unknown command beginning "<STYLE typ..." - rest of line ignored.
<br>
SP2-0734: unknown command beginning "<!-- BODY ..." - rest of line ignored.
<br>
SP2-0042: unknown command "</STYLE>"" - rest of line ignored.
<br>
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
<br>
SP2-0734: unknown command beginning "BODY "TEXT..." - rest of line ignored.
<br>
SP2-0734: unknown command beginning "TABLE "WID..." - rest of line ignored.
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'> <tr> <td>
16268612580
</td>
<td>
11-FEB-15
</td>
<td align="right">
       179
</td>
<td>
P
</td>
</tr>
<tr>
<td>
17327623109
</td>
<td>
11-FEB-15
</td>
<td align="right">
       707
</td>
<td>
P
</td>
</tr>

---------- Post updated at 12:21 AM ---------- Previous update was at 12:18 AM ----------

I tried using HTML Report, i think i am missing something here

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;
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
HEAD "<TITLE>Pending</TITLE>
<STYLE types='text/css'>
<!-- BODY {background: #FFFFC6}
</STYLE>"
BODY "TEXT='#FF00Ff'"
TABLE "WIDTH='90%' BORDER='5'"

SPOOL report.html

select msisdn,status_changed_time,rate_plan_id,status from subscriber where status = 'P' and status_changed_time < sysdate order b
y status_changed_time  desc;

SPOOL OFF
EOF`

Output is below

SP2-0734: unknown command beginning "HEAD "<TIT..." - rest of line ignored.
<br>
SP2-0734: unknown command beginning "<STYLE typ..." - rest of line ignored.
<br>
SP2-0734: unknown command beginning "<!-- BODY ..." - rest of line ignored.
<br>
SP2-0042: unknown command "</STYLE>"" - rest of line ignored.
<br>
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
<br>
SP2-0734: unknown command beginning "BODY "TEXT..." - rest of line ignored.
<br>
SP2-0734: unknown command beginning "TABLE "WID..." - rest of line ignored.
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'> <tr> <td>
16268612580
</td>
<td>
11-FEB-15
</td>
<td align="right">
       179
</td>
<td>
P
</td>
</tr>
<tr>
<td>
17327623109
</td>
<td>
11-FEB-15
</td>
<td align="right">
       707
</td>
<td>
P
</td>
</tr>

Please help if i am doing something wrong here

11

You are missing the minus signs:

...
SET LINESIZE 180;
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Pending</TITLE> -
<STYLE types='text/css'> -
<!-- BODY {background: #FFFFC6} -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"

SPOOL report.html
...
12

Now i tried including '-' sign

#!/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.gsm=1900.org:7779/prpx.unix.gsm1900.org <<EOF
set pagesize 0;
SET LINESIZE 180;
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Pending</TITLE> -
<STYLE types='text/css'> -
<!-- BODY {background: #FFFFC6}--> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"

SPOOL report.html

select msisdn,status_changed_time,rate_plan_id,status from subscriber where status = 'P' and status_changed_time < sysdate order b
y status_changed_time  desc;

SPOOL OFF
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 msisdn,status_changed_time,rate_plan_id,status from subscriber where status = 'I' and status_changed_time < sysdate order b
y status_changed_time  desc;
EOF`

final="Pending
$pending

Activating
$activating"

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

echo $final > $LOG_DIR/pending_`date +%d%m%y`.log

getting no output

i Only tried it on pending first later i can do the same on activating parrt

13

The backquote is at the wrong place:

...
## all pending subscriber .

pending=`sqlplus -s pag_app/abcdef@prpx1-vip.unix.gsm=1900.org:7779/prpx.unix.gsm1900.org <<EOF
set pagesize 0;
...
14

You could always have a:-

SELECT ' -------------------   ----------   ---------------   -' FROM dual ;

Would that help?

Robin

15

Can I print my output in below mentioned format? This is exactly what I am looking for.

Similar table format and background colour, this is exactly what I am looking for, please tell what I need to do for getting output in this format, any demo will be really helpful.

16

I would suggest that this pretty view is generated by the client on your desktop. If you need it like this, then you may need to run it on your desktop. I presume you are wanting to then send the image/data out as a report.

If it has to look pretty, then the desktop is probably the best place for it.

Robin

17

I referred below link 8 Generating HTML Reports from SQL*Plus

from here i got this image

---------- Post updated 02-13-15 at 06:55 AM ---------- Previous update was 02-12-15 at 07:25 AM ----------

18

The documentation gives explanations and examples. Where is the problem? Do you get any error messages when you implement the steps described in the docu?

19 
#!/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.gsm=1900.org:7779/prpx.unix.gsm1900.org <<EOF
set pagesize 0;
SET LINESIZE 180;
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Pending</TITLE> -
<STYLE types='text/css'> -
<!-- BODY {background: #FFFFC6}--> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"

SPOOL report.html

select msisdn,status_changed_time,rate_plan_id,status from subscriber where status = 'P' and status_changed_time < sysdate order b
y status_changed_time  desc;

SPOOL OFF
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 msisdn,status_changed_time,rate_plan_id,status from subscriber where status = 'I' and status_changed_time < sysdate order b
y status_changed_time  desc;
EOF`

final="Pending
$pending

Activating
$activating"

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

echo $final > $LOG_DIR/pending_`date +%d%m%y`.log

I tried this but its giving no output, can you please brief the steps in sequence that i need to follow, to get the desired output in table and attractive background

20

As noted by cero in post #13, the backquote is in the wrong place:
Change:

`pending=sqlplus -s pag_app/abcdef@prpx1-vip.unix.gsm=1900.org:7779/prpx.unix.gsm1900.org <<EOF

to:

pending=`sqlplus -s pag_app/abcdef@prpx1-vip.unix.gsm=1900.org:7779/prpx.unix.gsm1900.org <<EOF

That may not be enough to get what you want, but it is a contributing factor.