?
Solved

help is a procedure that is sending email

Posted on 2014-01-15
36
Medium Priority
?
395 Views
Last Modified: 2014-01-16
SCH_DATE,SCH_TIME,DONOR_ID,CONVERT_DATE,SITE_ID,FIRST_NAME,LAST_NAME,DESCRIPTION
1/15/2014,12:40:00,DN00551258,12:40:00 PM,LOCN100008,KRISTIN,NETTNIN,A Neg
1/15/2014,13:40:00,DN20242833,01:40:00 PM,LOCN100326,LESLIE,WRIGHT,B Pos
1/15/2014,15:40:00,DN00171528,03:40:00 PM,LOCN101661,SHARON,WOODMAN,A Pos
1/15/2014,16:40:00,DN00591807,04:40:00 PM,LOCN201489,MEGAN,BENHART,A Pos
1/15/2014,16:40:00,DN00496118,04:40:00 PM,LOCN201860,TAMMY,MICHAEL,O Neg
1/15/2014,17:00:00,DN20130486,05:00:00 PM,LOCN101661,DARCY,KAROUZOS,O Neg
1/15/2014,17:40:00,DN20090737,05:40:00 PM,LOCN100373,JOY,NYHANNA,B Neg
1/15/2014,18:00:00,DN20001847,06:00:00 PM,LOCN100008,FRANCES,MILLER,AB Pos


LOCN100008
LOCN101661

I am sending report in an email. I have 2 donors each in the above mentioned locations. So getting email twice with both 2 donors.
I need help to send email only once. If multiple donors I want then to go in one email.
Help appreciated.
wb-eligible-donors-send-email.sql
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 11
  • 8
36 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783298
Are you sure that is the correct procedure?

The procedure name doesn't seem to match the file name and I don't see where inside the code any email is sent.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39783308
I SAVED THAT PROCEDURE AS A SQL AND SENT IT.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783322
I still don't see where it is sending an email.  Can you point out the line to me?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 6

Author Comment

by:anumoses
ID: 39783324
UTL_HTTP.SET_PROXY (null,null);

     url :=
     utl_url.ESCAPE
     (v_server_c || '?' || v_conn_str_c ||
       '&desformat=PDF&ENVID=' || v_report_db_c ||
       '&subject="WB Donors Eligible for conversion"' ||
       '&destype=mail' ||
       '&FROM=abc@xyz.com&desname=' || 'abc@xyz.com' ||
       '&report='||v_letter||'&p_site_id='||get_rep_param_rec.site_id);
   
       req := UTL_HTTP.BEGIN_REQUEST (url);
       UTL_HTTP.SET_HEADER (req, 'User-Agent', 'Mozilla/4.0 Oracle');
         resp := UTL_HTTP.GET_RESPONSE (req);
       UTL_HTTP.READ_LINE (resp, val, TRUE);
       UTL_HTTP.END_RESPONSE (resp);

This part sends email. I took out real email ids and put fake ones
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783330
The file is named wb-eligible-donors-send-email

The procedure inside the file is wb_donors_elig_for_conv_proc.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 39783335
Sorry.  Never used utl_http.  Let alone send an email with it.

I don't know your data and what is doing what in the two different cursor loops but just a guess but can you not move that utl_http code to the outer loop?
0
 
LVL 6

Author Comment

by:anumoses
ID: 39783358
As a moderator can you delete the sensitive data?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39783361
>>When I execute this way send email

There really wasn't a need to repost the same procedure as the original.

Did you forget to scrub the data?  There appears to be potentially sensitive information in there.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785215
Let me ask if anyone can help me in sending email through a procedure other than what I am doing in the above mentioned question. I will load a sample table and data.

Just need to send email once instead of 6 times. Help is appreciated.
table-and-data.txt
procedure.txt
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39785224
Did you try what I mentioned above in http:#a39783335 ?


for get_rep_param_rec in get_rep_param_cur loop
  for get_email_rec in get_email_cur(get_rep_param_rec.site_id) loop

-- MOVE FROM HERE Email to be sent.         

  end loop;           

-- TO HERE Email to be sent.         

 end loop;


You understand your procedure and requirements.  It should be a simple matter to keep building the string/clob/??? and send the email only when you need to send it.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785228
I will try now.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785251
That worked. Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39785304
You could send it directly from the database instead of using the http process

CREATE OR REPLACE PROCEDURE create_test_procedure
IS
    c_mime_boundary CONSTANT VARCHAR2(256) := '-----AABCDEFBBCCC0123456789DE';

    CURSOR get_rep_param_cur(cp_site_id IN VARCHAR)
    IS
          SELECT sch_date,
                 sch_time,
                 donor_id,
                 gender,
                 convert_date,
                 site_id,
                 first_name,
                 last_name,
                 description
            FROM table1
           WHERE site_id = cp_site_id
        ORDER BY TO_DATE(sch_time, 'HH24:MI:SS');

    CURSOR get_email_cur(cp_site_id IN VARCHAR)
    IS
        SELECT subcenter_email
          FROM table2
         WHERE lt_location_id = cp_site_id;

    v_letter                 CLOB := EMPTY_CLOB();
    v_connection             UTL_SMTP.connection;
    v_len                    INTEGER;
    v_index                  INTEGER;
BEGIN
    FOR site_rec IN (SELECT DISTINCT site_id FROM table1)
    LOOP
        v_letter := EMPTY_CLOB();

        FOR get_rep_param_rec IN get_rep_param_cur(site_rec.site_id)
        LOOP
            v_letter :=
                   v_letter
                || TO_CHAR(get_rep_param_rec.sch_date, 'mm/dd/yyyy')
                || ','
                || TO_CHAR(get_rep_param_rec.sch_time, 'hh24:mi:ss')
                || ','
                || get_rep_param_rec.donor_id
                || ','
                || get_rep_param_rec.gender
                || ','
                || TO_CHAR(get_rep_param_rec.convert_date, 'hh24:mi:ss')
                || ','
                || get_rep_param_rec.site_id
                || ','
                || get_rep_param_rec.first_name
                || ','
                || get_rep_param_rec.last_name
                || ','
                || get_rep_param_rec.description
                || UTL_TCP.crlf;
        END LOOP;

        FOR get_email_rec IN get_email_cur(site_rec.site_id)
        LOOP
            v_connection := UTL_SMTP.open_connection('your email server goes here');
            UTL_SMTP.helo(v_connection, 'xyz.com');
            UTL_SMTP.mail(v_connection, 'abc@xyz.com');
            UTL_SMTP.rcpt(v_connection, get_email_rec.subcenter_email);
            UTL_SMTP.open_data(v_connection);

            UTL_SMTP.write_data(v_connection, 'From: abc@xyz.com' || UTL_TCP.crlf);
            UTL_SMTP.write_data(
                v_connection,
                'To: ' || get_email_rec.subcenter_email || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(
                v_connection,
                'Subject: "WB Donors Eligible for conversion"' || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);

            UTL_SMTP.write_data(
                v_connection,
                   'Content-Type: multipart/mixed; boundary="'
                || c_mime_boundary
                || '"'
                || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
            UTL_SMTP.write_data(
                v_connection,
                'This is a multi-part message in MIME format.' || UTL_TCP.crlf
            );

            UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
            UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);

            -- Set up attachment header
            UTL_SMTP.write_data(
                v_connection,
                   'Content-Disposition: attachment; filename="'
                || 'donor_information.csv' -- change the filename here
                || '"'
                || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

            -- Write attachment contents

            v_len := DBMS_LOB.getlength(v_letter);
            v_index := 1;

            WHILE v_index <= v_len
            LOOP
                UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_letter, 32000, v_index));
                v_index := v_index + 32000;
            END LOOP;

            --
            -- End attachment
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
            UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

            UTL_SMTP.close_data(v_connection);
            UTL_SMTP.quit(v_connection);
        END LOOP;
    END LOOP;
END;

Open in new window


For more information on sending email attachments...

http://www.experts-exchange.com/Database/Oracle/A_7749-How-to-send-email-attachments-with-Oracle.html
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785375
One question

UTL_SMTP.helo(v_connection, 'xyz.com'); What is this to be replaced by?
UTL_SMTP.mail(v_connection, 'abc@xyz.com'); Is this from email address?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39785386
Should changing the way you actually send the email not be a new question?

This one was how to send it only once.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785395
The other method you told me about moving email after the loop worked of one center. When I ran for all I am still getting multiple emails.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39785398
answering two posts in one...

anumoses,

UTL_SMTP.helo(v_connection, 'xyz.com'); What is this to be replaced by?
UTL_SMTP.mail(v_connection, 'abc@xyz.com'); Is this from email address?

the "helo" command needs to know what domain you are coming from, and that is generally the part after the @ in your FROM email address

the "mail" command needs to know the return address, for error messages and things like that.  This is usually, but not required to be, the same as the FROM address.

slightwv,

I thought I was answering this question.
The procedure I posted only sends the email once.
Sure, I'm using a different mechanism, and I added a new query and I altered one of the existing cursors and I un-nested the loops but added a new one that nests both of the previous two.

In short - yes, I completely rewrote the code but I was still answering this question of how to send one email per site.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39785400
>>When I ran for all I am still getting multiple emails.

You need to walk through your logic and keep building the data until it is complete for whatever level you determine is complete, then and only then send the email.

You understand your business logic, we cannot.

What you posted only has two loops.  If moving it to the outside the inner loop didn't work, try outside BOTH loops?

for get_rep_param_rec in get_rep_param_cur loop
  for get_email_rec in get_email_cur(get_rep_param_rec.site_id) loop


  end loop;          

-- MOVE FROM HERE Email to be sent.        

 end loop;

-- TO HERE Email to be sent.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39785401
>> yes, I completely rewrote the code but I was still answering this question of how to send one email per site.

Apologies.  Missed that.  From the statement "You could send it directly from the database instead of using the http process", I didn't see the logic change.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785423
When I execute the procedure

begin

create_test_procedure;

end;

error- ORA 22275 - Invalid LOB Locator specified.

Tried to google the answers. Is this version issue?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39785434
If the old method of sending emails worked, I wouldn't suggest a complete re-write now.  Focus on getting the email you want when you want it.

Once you get that, you can try a new method of sending an email if you wish.

Changing more than one thing at a time can cause more problems.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785442
The problem of sending email after the end loop as you have suggested is that cursor values are not being recognized as we end the loop.

eg; get_rep_param_rec.site_id)

Still working to figure it out.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39785453
Create a CLOB to store the data inside the loop then send the CLOB outside the loop?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39785454
please post the code you are using that generates the errors (either or both versions)

also,  I added conversions of dates to strings in my code above that weren't inappropriate change the letter construction like this...  (if you post your code using this version, make the correction first)

 v_letter :=
                   v_letter
                || TO_CHAR(get_rep_param_rec.sch_date, 'mm/dd/yyyy')
                || ','
                || get_rep_param_rec.sch_time
                || ','
                || get_rep_param_rec.donor_id
                || ','
                || get_rep_param_rec.gender
                || ','
                || get_rep_param_rec.convert_date
                || ','
                || get_rep_param_rec.site_id
                || ','
                || get_rep_param_rec.first_name
                || ','
                || get_rep_param_rec.last_name
                || ','
                || get_rep_param_rec.description
                || UTL_TCP.crlf;
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785494
I did not want all columns in the email. So I did this

FOR get_rep_param_rec IN get_rep_param_cur(site_rec.site_id)
        LOOP
            v_letter :=
                   v_letter
                || get_rep_param_rec.donor_id
                || ','
                || get_rep_param_rec.convert_date
                || ','
                || get_rep_param_rec.first_name
                || ','
                || get_rep_param_rec.last_name
                || ','
                || get_rep_param_rec.description
                || UTL_TCP.crlf;
        END LOOP;
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785538
Does not give me where the error occurs or line number. Just says what I have quoted above as error.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39785555
What does what?

post your code along with the results. please don't make us guess what you are executing.
also, there are multiple versions  my utl_smtp version and the original utl_http version.

Each produces its own set of wrong results/errors.  When you post an error/wrong result, please include the code that produces that failure.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785570
CREATE OR REPLACE PROCEDURE create_test_procedure
IS
    c_mime_boundary CONSTANT VARCHAR2(256) := '-----AABCDEFBBCCC0123456789DE';

    CURSOR get_rep_param_cur(cp_site_id IN VARCHAR)
    IS
              select sch_date,sch_time,hds.donor_id,gender,
           to_char(to_date(sch_time,'HH24:MI:SS'),'HH:MI:SS AM') as convert_date,
           site_id,hds.first_name,hds.last_name,mc.description
      from donors_don d,hbc_donor_schedule hds,master_codes mc
     where SCH_DATE = trunc(sysdate)
       AND hds.STATUS = 'CNFRM'
       AND PROC_CODE = 'WB'
       AND DRIVE_ID IS NULL
       and donation_type = 'AL'
       and mc.code_type = 'ABO'
       and mc.udf1 = to_char(hds.blood_type)
         and d.donor_id = hds.donor_id
       and hds.donor_id in (select donor_id
                              from ht_bc_don_elig_vw hbdev
                                         where hds.donor_id = hbdev.donor_id
                               and (PLATELET_DATE is null
                                              or PLATELET_DATE < trunc(sysdate)
                                or  PLASMA_DATE is null
                                                or PLASMA_DATE < trunc(sysdate)))
             and (gender = 'M'
       or
(gender = 'F' and hds.donor_id in (select di.donor_id
                              from donor_interdictions_don di
                                       where di.donor_id = hds.donor_id
                                           and di.inter_code = 'HLAN'
                                           and di.term_date is null) ))                                                                                                                                  
order by to_date(sch_time,'HH24:MI:SS');

    CURSOR get_email_cur(cp_site_id IN VARCHAR)
    IS
        select SUBCENTER_EMAIL
     from SITES
    where LT_LOCATION_ID = cp_SITE_id;

    v_letter                 CLOB := EMPTY_CLOB();

      --v_letter                 CLOB;
    v_connection             UTL_SMTP.connection;
    v_len                    INTEGER;
    v_index                  INTEGER;
BEGIN
   
      --v_letter := EMPTY_CLOB();
      
      FOR site_rec IN (SELECT DISTINCT site_id FROM table1)
    LOOP
        v_letter := EMPTY_CLOB();

        FOR get_rep_param_rec IN get_rep_param_cur(site_rec.site_id)
        LOOP
            v_letter :=
                   v_letter
                || get_rep_param_rec.donor_id
                || ','
                || get_rep_param_rec.convert_date
                || ','
                || get_rep_param_rec.first_name
                || ','
                || get_rep_param_rec.last_name
                || ','
                || get_rep_param_rec.description
                || UTL_TCP.crlf;
        END LOOP;

        FOR get_email_rec IN get_email_cur(site_rec.site_id)
        LOOP
            v_connection := UTL_SMTP.open_connection('my server name that I have taken as its sensitive date');
            UTL_SMTP.helo(v_connection, 'from email@abc.com');
            UTL_SMTP.mail(v_connection, 'from email@abc.com');
                  UTL_SMTP.rcpt(v_connection, 'to email address');
            UTL_SMTP.open_data(v_connection);

            UTL_SMTP.write_data(v_connection, 'From: my email address' || UTL_TCP.crlf);
            UTL_SMTP.write_data(
                v_connection,
                'To: ' || get_email_rec.subcenter_email || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(
                v_connection,
                'Subject: "WB Donors Eligible for conversion"' || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);

            UTL_SMTP.write_data(
                v_connection,
                   'Content-Type: multipart/mixed; boundary="'
                || c_mime_boundary
                || '"'
                || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
            UTL_SMTP.write_data(
                v_connection,
                'This is a multi-part message in MIME format.' || UTL_TCP.crlf
            );

            UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
            UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);

            -- Set up attachment header
            UTL_SMTP.write_data(
                v_connection,
                   'Content-Disposition: attachment; filename="'
                || 'donor_information.pdf' -- change the filename here
                || '"'
                || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

            -- Write attachment contents

            v_len := DBMS_LOB.getlength(v_letter);
            v_index := 1;

            WHILE v_index <= v_len
            LOOP
                UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_letter, 32000, v_index));
                v_index := v_index + 32000;
            END LOOP;

            --
            -- End attachment
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
            UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

            UTL_SMTP.close_data(v_connection);
            UTL_SMTP.quit(v_connection);
        END LOOP;
    END LOOP;
END;


Compiled it

and execute

begin

create_test_procedure;

end;

error- ORA 22275 - Invalid LOB Locator specified.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 39785589
CREATE OR REPLACE PROCEDURE create_test_procedure
IS
    c_mime_boundary CONSTANT VARCHAR2(256) := '-----AABCDEFBBCCC0123456789DE';

    CURSOR get_rep_param_cur(
        cp_site_id   IN VARCHAR
    )
    IS
          SELECT sch_date,
                 sch_time,
                 hds.donor_id,
                 gender,
                 TO_CHAR(TO_DATE(sch_time, 'HH24:MI:SS'), 'HH:MI:SS AM') AS convert_date,
                 site_id,
                 hds.first_name,
                 hds.last_name,
                 mc.description
            FROM donors_don d, hbc_donor_schedule hds, master_codes mc
           WHERE sch_date = TRUNC(SYSDATE)
             AND hds.status = 'CNFRM'
             AND proc_code = 'WB'
             AND drive_id IS NULL
             AND donation_type = 'AL'
             AND mc.code_type = 'ABO'
             AND mc.udf1 = TO_CHAR(hds.blood_type)
             AND d.donor_id = hds.donor_id
             AND hds.donor_id IN (SELECT donor_id
                                    FROM ht_bc_don_elig_vw hbdev
                                   WHERE hds.donor_id = hbdev.donor_id
                                     AND (platelet_date IS NULL
                                       OR platelet_date < TRUNC(SYSDATE)
                                       OR plasma_date IS NULL
                                       OR plasma_date < TRUNC(SYSDATE)))
             AND (gender = 'M'
               OR (gender = 'F'
               AND hds.donor_id IN (SELECT di.donor_id
                                      FROM donor_interdictions_don di
                                     WHERE di.donor_id = hds.donor_id
                                       AND di.inter_code = 'HLAN'
                                       AND di.term_date IS NULL)))
        ORDER BY TO_DATE(sch_time, 'HH24:MI:SS');

    CURSOR get_email_cur(cp_site_id IN VARCHAR)
    IS
        SELECT subcenter_email
          FROM sites
         WHERE lt_location_id = cp_site_id;

    v_letter                 CLOB := EMPTY_CLOB();

    --v_letter                 CLOB;
    v_connection             UTL_SMTP.connection;
    v_len                    INTEGER;
    v_index                  INTEGER;
BEGIN
    --v_letter := EMPTY_CLOB();

    FOR site_rec IN (SELECT DISTINCT site_id FROM table1)
    LOOP
        v_letter := '';

        FOR get_rep_param_rec IN get_rep_param_cur(site_rec.site_id)
        LOOP
            v_letter :=
                   v_letter
                || get_rep_param_rec.donor_id
                || ','
                || get_rep_param_rec.convert_date
                || ','
                || get_rep_param_rec.first_name
                || ','
                || get_rep_param_rec.last_name
                || ','
                || get_rep_param_rec.description
                || UTL_TCP.crlf;
        END LOOP;

        FOR get_email_rec IN get_email_cur(site_rec.site_id)
        LOOP
            v_connection :=
                UTL_SMTP.open_connection('my server name that I have taken as its sensitive date');
            UTL_SMTP.helo(v_connection, 'from email@abc.com');
            UTL_SMTP.mail(v_connection, 'from email@abc.com');
            UTL_SMTP.rcpt(v_connection, 'to email address');
            UTL_SMTP.open_data(v_connection);

            UTL_SMTP.write_data(v_connection, 'From: my email address' || UTL_TCP.crlf);
            UTL_SMTP.write_data(
                v_connection,
                'To: ' || get_email_rec.subcenter_email || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(
                v_connection,
                'Subject: "WB Donors Eligible for conversion"' || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);

            UTL_SMTP.write_data(
                v_connection,
                   'Content-Type: multipart/mixed; boundary="'
                || c_mime_boundary
                || '"'
                || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
            UTL_SMTP.write_data(
                v_connection,
                'This is a multi-part message in MIME format.' || UTL_TCP.crlf
            );

            UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
            UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);

            -- Set up attachment header
            UTL_SMTP.write_data(
                v_connection,
                   'Content-Disposition: attachment; filename="'
                || 'donor_information.pdf' -- change the filename here
                || '"'
                || UTL_TCP.crlf
            );
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);

            -- Write attachment contents

            v_len := DBMS_LOB.getlength(v_letter);
            v_index := 1;

            WHILE v_index <= v_len
            LOOP
                UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_letter, 32000, v_index));
                v_index := v_index + 32000;
            END LOOP;

            --
            -- End attachment
            UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
            UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);

            UTL_SMTP.close_data(v_connection);
            UTL_SMTP.quit(v_connection);
        END LOOP;
    END LOOP;
END;
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785610
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "HBC_DATA.CREATE_TEST_PROCEDURE", line 80
ORA-06512: at line 3
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39785631
check spellings, make sure you don't have a typo.

If your server is correct and you still get the error trying to open a connection then  you'll have to contact the administrator of your email server
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39785639
also...

UTL_SMTP.helo(v_connection, 'from email@abc.com');

that's not correct, you only supply the domain to helo

UTL_SMTP.helo(v_connection, 'abc.com');
0
 
LVL 6

Author Comment

by:anumoses
ID: 39785659
I contacted the network admin. He asked me to use 10.10.0.36. But I still get error.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39785668
>>>  I still get error.

I assume you mean the same error from the open_connection line, and not a new/different error or from different area of the code?

If so...

I can't diagnose the problem.

Check ports (default is 25), check address, check routing between db server and email server, etc.

You'll have to work with your admins to get that working.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39786028
thanks. All intentions were good to help me out. But I used another condition to check that the site_id exists. I am storing the site id in a temporary table and if does not exist then I send email. Right now my old procedure is working. But later I will take a look at sdstuber's approach and find why its not working. Thanks again
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question