I have a procedure(private and not declared in package specs) called p_sendmail and this should raise an error message and pass that message to the calling procedure so that it can be shown on a web page. I am just unable to do that when I pass an invalid email id. The page just errors out with a runtime error saying:
Failed to execute target procedure ORA-20002: ORA-29279: SMTP permanent error: 550 5.1.1 <balbhablh@abcd.ca>... User unknown
ORA-06512: at "STANJORE.BWAKPLNS", line 401
ORA-06512: at "STANJORE.BWAKPLNS", line 1106
ORA-06512: at "STANJORE.BWAKPLNS", line 1248
ORA-06512: at line 33
The code for p_sendmail is as follows:
-- Local variable
CRLF CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
p_err_message1 VARCHAR2(100);
-- --------------------------------------------------------------------------------
-- FORWARD DECLARATIONS
-- -----------------------------------------------------------------------------
PROCEDURE p_sendmail(p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN VARCHAR2,
msg_subject IN VARCHAR2 DEFAULT NULL,
p_msg_body IN LONG DEFAULT NULL
);
PROCEDURE p_sendmail(p_sender_email IN VARCHAR2,
p_from IN VARCHAR2,
p_to IN VARCHAR2,
msg_subject IN VARCHAR2 DEFAULT NULL,
p_msg_body IN LONG DEFAULT NULL
) is
v_to_list LONG;
v_cc_list LONG;
v_bcc_list LONG;
v_date VARCHAR2(255) DEFAULT TO_CHAR(SYSDATE, 'DD MON YYYY HH24:MI:SS PM');
g_mail_conn UTL_SMTP.CONNECTION;
SMTP_HOST CONSTANT VARCHAR2(256) := 'smtp-abc.defg.ca';
SMTP_PORT CONSTANT PLS_INTEGER := 25;
BEGIN
g_mail_conn := UTL_SMTP.OPEN_CONNECTION(SMTP_HOST, SMTP_PORT);
UTL_SMTP.helo(g_mail_conn, SMTP_HOST);
UTL_SMTP.mail(g_mail_conn, p_sender_email);
UTL_SMTP.rcpt(g_mail_conn, p_to);
UTL_SMTP.open_data(g_mail_conn );
UTL_SMTP.write_data( g_mail_conn, '' || CRLF);
UTL_SMTP.write_data(g_mail_conn, p_msg_body);
UTL_SMTP.close_data(g_mail_conn );
UTL_SMTP.quit(g_mail_conn);
EXCEPTION
WHEN utl_smtp.transient_error THEN
RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM);
--DBMS_OUTPUT.put_line('TransientError: Invalid Operation as service may not be available.');
WHEN utl_smtp.permanent_error THEN
RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM);
--DBMS_OUTPUT.put_line('Permanent Error: The email id entered is either invalid or recepients mail box is full.');
--p_errmessage:=SQLERRM;
WHEN others THEN
RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM);
IF SQLERRM IS NOT NULL THEN
p_err_message1:=SQLERRM;
ELSE
p_err_message1:=NULL;
END IF;
END p_sendmail;
--Now calling the procedure after declaring them (this calling procedure is part of a package body)
p_sendmail(p_sender_email => 'stanjore@someemailid.ca', --- Send Email to the Donor
p_from => 'PLEDGING <stanjore@someemailid.ca>',
p_to => v_store_email_address,
p_msg_subject => 'Anonymous User',
p_msg_body => 'Thank you' ||
CRLF ||
'The email confirms that we have received your pledge' ||
CRLF ||
CRLF ||
' Name: ' || v_full_name ||
CRLF ||
'Temporary ID: ' || v_azbwccp_id ||
CRLF ||
'Reference Number: ' || MTID ||
CRLF ||
' Amount: ' || to_number(campaign_desg_amt1) ||
CRLF ||
' Campaign: ' || campaign ||
CRLF ||
' Designation: ' || adbdesg_rec.adbdesg_name ||
CRLF ||
' Type: ' || atvpldg_rec.atvpldg_desc ||
CRLF ||
' Duration: ' || atvpdur_rec.atvpdur_desc ||
CRLF ||
' Frequency: ' || atvfreq_rec.atvfreq_desc ||
CRLF ||
' Start Date: ' || bill_date2 ||
CRLF ||
CRLF ||
'Your pledge is being processed.' ||
CRLF ||
'In the meantime, should you wish to amend this transaction, please contact us ' ||
CRLF ||
CRLF ||
'Thank you for your support.' ||
CRLF ||
CRLF ||
CRLF ||
CRLF ||
'*** This is an automated message system. Please do not respond to this e-mail. *** '
);
--This is where I am trying to bring it back and show it to the user on the page.
IF p_err_message1 is NOT NULL THEN
msg_text := '<b> The email entered is an invalid one.Please enter a correct one;' ;
END IF;
I opened the file in VI editor and looked at the line numbers but could not decipher it. The only thing I feel that is wrong is I am unable to propogate the error message.
Based on what Oracle and others have said RAISE_APPLICATION_ERROR passes the message from the backend to non oracle front end. So where is that I am going wrong or what is that I am doing wrong. Can any one please help?
Once again many thanks in advance.