Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

help is a procedure that is sending email

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
anumoses
Asked:
anumoses
  • 16
  • 11
  • 8
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
I SAVED THAT PROCEDURE AS A SQL AND SENT IT.
0
 
slightwv (䄆 Netminder) Commented:
I still don't see where it is sending an email.  Can you point out the line to me?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
The file is named wb-eligible-donors-send-email

The procedure inside the file is wb_donors_elig_for_conv_proc.
0
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
As a moderator can you delete the sensitive data?
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
I will try now.
0
 
anumosesAuthor Commented:
That worked. Thanks
0
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Should changing the way you actually send the email not be a new question?

This one was how to send it only once.
0
 
anumosesAuthor Commented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
>> 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
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
anumosesAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Create a CLOB to store the data inside the loop then send the CLOB outside the loop?
0
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
Does not give me where the error occurs or line number. Just says what I have quoted above as error.
0
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
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
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
I contacted the network admin. He asked me to use 10.10.0.36. But I still get error.
0
 
sdstuberCommented:
>>>  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
 
anumosesAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 16
  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now