joi, 29 septembrie 2016

Export SQL query to csv and send it as a file attachment

I will use the PLSQL to fill up the file with queried data, then a linux script to send the mail.


declare
   v_file     UTL_FILE.file_type;
   v_string   VARCHAR2 (4000);

BEGIN
   v_file :=
      UTL_FILE.fopen ('DUMP2_FLORIN',
                      'tt.csv',
                      'w',
                      1000);

   v_string := 'csf_code, op_code';
   UTL_FILE.put_line (v_file, v_string);





-- header above
 

   FOR cur IN (SELECT csf_code, op_code
        FROM prg_measures)
   LOOP
      v_string :=
            cur.csf_code
         || ','
         || cur.op_code
         ;
      UTL_FILE.put_line (v_file, v_string);
   END LOOP;

   UTL_FILE.fclose (v_file);
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (v_file)
      THEN
         UTL_FILE.fclose (v_file);
      END IF;
END;



Configure the sendmail client:

vi /etc/mail/submit.cf

 find the line:

D{MTAHost}[127.0.0.1]

and change it to 

D{MTAHost}mailserver 

where "mailserver" is the mail server host

Now, the linux script:

from="florin.videa@domain.ro"
to="florin.videa@domain.ro"
subject="this is the title"
boundary="ZZ_/afg6432dfgkl.94531q"
body="This is the body"
declare -a attachments
attachments=( "tt.csv"  "tt.csv" ) #here you can put more files

# Build headers
{

printf '%s\n' "From: $from
To: $to
Subject: $subject
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary=\"$boundary\"

--${boundary}
Content-Type: text/plain; charset=\"US-ASCII\"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

$body
"

# now loop over the attachments, guess the type
# and produce the corresponding part, encoded base64
for file in "${attachments[@]}"; do

  [ ! -f "$file" ] && echo "Warning: attachment $file not found, skipping" >&2 && continue

  mimetype=$(file -b --mime "$file")

  printf '%s\n' "--${boundary}
Content-Type: $mimetype
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=\"$file\"
"

  base64 "$file"
  echo
done

# print last boundary with closing --
printf '%s\n' "--${boundary}--"

} | sendmail -t -oi   # one may also use -f here to set the envelope-from

Niciun comentariu:

Trimiteți un comentariu