Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting I want query output to send on mail using table tag and output should be in table
# 1  
Old 02-11-2015
Error I want query output to send on mail using table tag and output should be in table
Code:
#! /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
Code:
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
Last edited by ankit.mca.aaidu; 02-11-2015 at 08:34 AM..
# 2  
Old 02-11-2015
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
Last edited by rbatte1; 02-11-2015 at 08:31 AM.. Reason: Neatened list
# 3  
Old 02-11-2015
Well still not getting output with table border in it
# 4  
Old 02-11-2015
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?:-
Code:
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) :-
Code:
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  
Old 02-11-2015
Yes, i wanted it to be like this

Code:
  -----------   ----------  ---------   -
| 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 |


Moderator's Comments:
Mod Comment
Please wrap code, files, input & output/errors in CODE tags
It makes it far easier to read and preserves multiple spaces where that can be critical
Last edited by rbatte1; 02-11-2015 at 09:11 AM.. Reason: Added CODE tags
# 6  
Old 02-11-2015
You will simply need to adjust your SELECT statement to include these. As an example:-
Code:
SELECT '|'||username||'|'||profile||'|' FROM DBA_USERS ;


I hope that this helps,
Robin
# 7  
Old 02-11-2015
Thanks for the help, now it giving me output as below
Code:
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
Code:
 -----------   ----------  ---------   -
| 16612969688 | 11-FEB-15 |       707 | P |
| 16309354405 | 09-FEB-15 |       152 | P |
| 14049529349 | 08-FEB-15 |       183 | P |
| 16462516471 | 03-FEB-15 |       179 | P |

Last edited by rbatte1; 02-11-2015 at 09:46 AM.. Reason: Removed QUOTE tags and corrected spelling & case
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to create a file from output of vertica table query in UTF-8 format?

Hello, In my shell script, I extract table data from HP Vertica DB into a csv file using vsql -c command. But the problem is the file getting created is in binary format and hence some of the data becomes unreadable which has chinese characters as part of data. file -i filename.csv - gives... (2 Replies)
Discussion started by: Dharmatheja
2 Replies

2. Shell Programming and Scripting

Table to send mail

I have two details to send in html email, two details present in variables, Processname & date which I need to send html email in table. Column1 is Processname: Process1 & column 2 is date:16/6/16 4:55 AM I have below basic html code, how to create table in html to send mail echo "<html>" >>... (1 Reply)
Discussion started by: rozee
1 Replies

3. Solaris

SQL QUERY to Table Output

Hi I am trying to run sql query from solaris in csh script and send the output to email. Below is my sql query select p.spid,se.program seprogram, se.machine, se.username, sq.sql_text,sq.retrows from v$process p inner join v$session se on p.addr = se.paddr inner join ( select... (2 Replies)
Discussion started by: tharmendran
2 Replies

4. Solaris

How to output the prstat into table and send through email?

Hi how to send below prstat script output into table format and send through email? prstat|awk '{ if ($9 >= 5) print $0 }' (17 Replies)
Discussion started by: tharmendran
17 Replies

5. Shell Programming and Scripting

Shell scripting unable to send the sql query data in table in body of email

I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email. spool table_update.html; SELECT * FROM PROCESS_LOG_STATS where process = 'ActivateSubscription'; spool off; exit; Please use code tags next time for your code and data.... (9 Replies)
Discussion started by: Sharanakumar
9 Replies

6. Shell Programming and Scripting

[Solved] Shell script output in HTML or with table like results and mail

Hello, Currently i have a script which will disply the results in plain text format. I want to format the result in more readable format like Making bold headings and format with colors etc. Something like html and send that content as email. Please help me how i can do that. I am using... (10 Replies)
Discussion started by: joy lobo
10 Replies

7. Shell Programming and Scripting

Take Data From a table and send it through mail

Hi can anyone help me in writing a code for taking data from a table and need to send that data through mail using mail -x command.. (3 Replies)
Discussion started by: ginrkf
3 Replies

8. Shell Programming and Scripting

Mailx command - send mail as table format

I have to send a couple of rows that have been returned from a SQL query. I have written the output of the query to a file and while i try to print this in the mail body the formatting goes wrong. Intended Output in mail body: Col1 Col2 Col3 ------ ... (2 Replies)
Discussion started by: qwertyu
2 Replies

9. UNIX for Dummies Questions & Answers

How to send html file in a mail not as an attachment but it should display in the mail in table for

Hi The below script working when we are sending the html as attachment can u please guide how to send thesmae data in table form direct in the mail and not in mail attachment . cat Employee.sql SET VERIFY OFF SET PAGESIZE 200 SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON - HEAD... (0 Replies)
Discussion started by: mani_isha
0 Replies

10. Shell Programming and Scripting

Arrange Data in table and send by mail

Everybody, can you tell me how express about this; we have text data file as : parameter1 Parameter2 AA 55 BB 77 . . . . . . We want to draw table for this data as attached then send as body of Email (4 Replies)
Discussion started by: xjklop2009
4 Replies
Login or Register to Ask a Question