Execute command from query

I intend to send an email through the result of a query. the problem is that it is being printed instead of running the command. can someone explain to me what i am doing wrong?

sqlTxt=$(sqlplus -s "$CONNECT" << EOF 
   WHENEVER SQLERROR EXIT SQL.SQLCODE;

   SET FEEDBACK OFF
   SET HEADING OFF
   SET SERVEROUTPUT ON
   SET FEED OFF
   SET TERMOUT OFF
   SET VERIFY OFF
   SET ECHO OFF
   SET HEAD OFF
select 'echo -en '||cme.BODY ||' |  mailx -s '||cme.SUBJECT ||' -a "$HTMLFILE"'||decode(MAIL_FROM,null,'',' -r "<' ||MAIL_FROM|| '>"')||' -c "'||MAIL_CC||'" "'||MAIL_TO||'"' send  from CUST_SEND_EMAIL cme WHERE  cme.program_name='SEND_EMAIL' and  cme.status='NOK';
/
EOF
)

$sqlTxt

Some additional notes:

• cme.BODY contains n . for this reason I use echo -en
• i am using mailx because i found the simplest to work
• it is not mandatory to use the command as I did. I can assign to variables the content of each column resulting from the query (which I tried to do without success).

Someone can help me?

Thanks in advance

Answer

Change the final line

$sqlTxt

to

eval $sqlTxt

More info about eval, and a warning!

it adds a space to the file extension

That will be 80 characters in; nothing to do with the attachment per se, just SQL*Plus’ default line width wrapping the result. If you keep the plain $sqlTxt as well you’ll see the command it’s trying to generate, which may have several spaces, at 80-char intervals – though if your terminal is set to 80 chars wide then that might not be obvious still.

You can override the line width with a high number, longer that you expect the command to ever be, e.g.

SET LINES 5000

You might want to enclose the subject in quotes too, in case that contains spaces; I was testing successfully with this slight variant:

...
   SET HEAD OFF
   SET LINES 5000

   select 'echo -en '
      || cme.BODY
      || ' |  mailx '
      || ' -s "' || cme.SUBJECT || '"'
      || ' -a "$HTMLFILE"'
      || case when MAIL_FROM is not null then ' -r "<' || MAIL_FROM || '>"' end
      || ' -c "' || MAIL_CC || '"'
      || ' "' || MAIL_TO || '"'
   from CUST_SEND_EMAIL cme
   where cme.program_name = 'SEND_EMAIL'
   and cme.status = 'NOK';
EOF
)

# to debug
$sqlTxt

eval $sqlTxt

Aside from splitting the query into multiple lines for readability and adding a few quotes, I also removed the /. As the query already ended with a semicolon that was just re-executing the buffer – which holds that query – so the output was duplicated, causing some odd results (extra spurious and illegal ‘to’ addresses).