This forum is currently locked. You can't register or post questions at this time. (read more)

Sending email attachments

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

viyer
Member
Posts: 1
Joined: Fri Jul 25, 2014 1:07 pm

Sending email attachments

Postby viyer » Fri Jul 25, 2014 1:34 pm

Hi Tim,

I saw your post on sending email attachments.
http://www.oracle-base.com/articles/mis ... -plsql.php

I have a csv file in my ORACLE server that I want to send as attachment.
It is in the Oracle server in C:\TEMP.
I have created a directory called MYCSV that points to this location.
The csv file name in TST.CSV.

Now, how do I call this

TST_EMAIL(pTO_ADDR => 'me@mycompany.com',
pFROM_ADDR => 'me@mycompany.com',
pMSG_SUBJECT => 'TST SUBJ',
pMSG_BODY => 'TEST MESSAGE 1',
pCC_ADDR => 'me@mycompany.com',
pATTACH_NAME => 'TST.CSV', <<<??? is this right? what comes in here?>>
pATTACH_MIME => << What comes in here?>> 'application/excel'
pATTACH_BLOB => << AGain is this reqd?? it is a small file , 90 kb>> )
END;

Thanks,
Vidya.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Sending email attachments

Postby Tim... » Fri Jul 25, 2014 4:08 pm

Hi.

pATTACH_NAME => 'TST.CSV',

This is just the name of the attachment that you want to see when the email is read. Put whatever you like here. What you have is fine.

pATTACH_MIME => 'text/csv'

It's a CSV file, so it should have a CSV mime type. :)

pATTACH_BLOB => ...

Well, CSV is text, so really you should be using a CLOB attachment, not a BLOB attachment, so maybe:

pATTACH_CLOB => l_clob

Now you have to populate v_clob with the contents of the file before you call this. Maybe using this type of thing...

Code: Select all

DECLARE
  l_bfile  BFILE;
  l_clob   CLOB;
BEGIN
  l_bfile := BFILENAME('MYCSV', 'TST.CSV');
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);
END;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 6 guests

cron