I am trying to capture an error message from the exception block and then pass it out to the calling procedure. I am getting bogged down with an error "PLS-00201: identifier 'P_ERR_MESSAGE1' must be declared". How can I overcome this or how can I pass the error message to the main procedure.
The situation is as follows:
-- Local variable
CRLF CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
-- --------------------------------------------------------------------------------
-- 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,
p\_err\_message1 OUT VARCHAR2);
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,
p\_err\_message1 OUT VARCHAR2 ) 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);
-- DBMS_OUTPUT.put_line('Unable to send an email.');
--p_errmessage:=SQLERRM;
IF SQLERRM IS NOT NULL THEN
p_err_message1:=SQLERRM;
ELSE
p\_err\_message1:=NULL;
END IF;
END p_sendmail;
--The calling procedure below:
p_sendmail(p_sender_email => 'stanjore@someemailid.ca', --- Send Email to the Donor
p\_from => 'PLEDGING \<[stanjore@someemailid.ca](mailto: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. *** ',
p\_err\_message1);
Now when I compile it, I am getting bogged down with an error message called: PLS-00201: identifier 'P_ERR_MESSAGE1' must be declared
Where is it I am going wrong? When I googled up, it talked about either the variable is not defined(which is not the case) or about privileges (which is not the case as I compiled the same procedure with fewer parameters last week in my schema). Any idea?
I am attaching a screenshot as well. Many thanks in advance.
Yes, I have not been able to copy and paste the package specs and the whole body because its too too big. Hope you understand.
