Solved

help is a procedure that is sending email

Posted on 2014-01-15
36
374 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
  • 16
  • 11
  • 8
36 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
I SAVED THAT PROCEDURE AS A SQL AND SENT IT.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I still don't see where it is sending an email.  Can you point out the line to me?
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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 76

Expert Comment

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

The procedure inside the file is wb_donors_elig_for_conv_proc.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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
Comment Utility
As a moderator can you delete the sensitive data?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
I will try now.
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
That worked. Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> 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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Create a CLOB to store the data inside the loop then send the CLOB outside the loop?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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
Comment Utility
Does not give me where the error occurs or line number. Just says what I have quoted above as error.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
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 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
I contacted the network admin. He asked me to use 10.10.0.36. But I still get error.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>  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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now