Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting bogged down with PLS-00201: identifier 'P_ERR_MESSAGE1' must be declared

buggleboy007Dec 2 2014 — edited Dec 2 2014

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.

ScreenHunter_22 Dec. 02 10.49.jpg

This post has been answered by Sven W. on Dec 2 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 30 2014
Added on Dec 2 2014
3 comments
295 views