Forum Stats

  • 3,734,283 Users
  • 2,246,937 Discussions


Send Email to Individual

User_W58DO Member Posts: 151 Red Ribbon

Hi Experts,

I am having a table with 100 of records in employee table and with unique emplid .Need to send email to each emplid the result of that particular row for that emplid. Could you all suggest me the best approach. I am sure of send a bulk report of a table.

Create table emp1

(EMPLID Varchar2(11),

Comments Varchar2(4000),

Name varchar2(30),

Request Varchar2(40))

Insert into emp1 ( emplid,comments,name,request)

values ('1 ', 'abcdef', 'john', 'Cleaning',

'2 ', 'abcdef', 'micha', 'washing',

'4 ', 'abcdef', 'ron', 'mopping',

'3 ', 'abcdef', 'storn', 'cooking',)

Expected results: it has to send to emplid individually

For EMPlid : 1

Sub : Dear (Name Column)

Mssg Body: Today its your duty for 'Request Column here'

Add comments column also here


well any lead would be helpful



  • 3348850
    3348850 Member Posts: 5 Red Ribbon

    If you are Only working with Oracle Database UTL_SMTP is the best option. if you have other technologies to work with like Oracle Apex you have many in built features.

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    Not sure why you've rejected Gaz's response. Whilst it's not a complete answer to what you want to do, this community isn't about us doing all your work for you. The basis of what you want is done using the UTL_SMTP package as Gaz has indicated.

    You can easily find ready made packages that people have written and put online that use the UTL_SMTP package and give a wrapper for sending emails, some more basic than others. There's also the UTL_MAIL package that Oracle provide, though it requires installing and configuring on the database and only offers basic options (like you can only send a single attachment). You'd be better with a more comprehensive package and those can be found online, or the link Gaz supplied gives plenty of example code that you can build in to your own package.

    At the end of the day, email is based around SMTP protocol, so the UTL_SMTP package is the one that underlies what you need.

    As for constructing your messages, that depends what you really want. You could have plain text emails, you could have more fancy HTML based emails, you could put he data directly in the email (plain text or HTML tables etc.) or you could build up the data in to an attachment (whatever format you choose) and send it that way, or multiple attachments if needed. It's completely flexible.

    You've been given the information you need to get started, so have a go and if you get stuck on something, then ask specifically about that issue.

    It shouldn't be hard to loop through your records of data and send separate emails if that's what you want to do.

  • User_W58DO
    User_W58DO Member Posts: 151 Red Ribbon
    edited Jan 4, 2021 7:31PM

    @BluShadow Sorry @Gaz in Oz response was good accidently pressed No but i didn`t find other option to revert or mark it as right

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    @User_W58DO Ok, I've unmarked the "No" from Gaz's response so you can mark things accordingly. ;)

Sign In or Register to comment.