I am in a situation where I believe despite passing the correct number of parameters in my local procedure (which has been "forward declarations" besides writing logic for it;I am using all of this in a package) is failing to compile at SQL * PLUS prompt because of error PLS-00306:Wrong number or types of arguments in call to P_SENDMAIL.
I am passing 5 parameters in forward declarations area and also in the logic/header of the procedure besides calling the procedure in the package. Can one of you help mewith this and tell me where exactly the error could be. Code is as follows:
[code]
-- 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);
[/code]
The procedure itself:
[code]
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
DBMS_OUTPUT.put_line('TransientError: Invalid Operation as service may not be available.');
WHEN utl_smtp.permanent_error THEN
DBMS_OUTPUT.put_line('Permanent Error: The email id entered is either invalid or recepients mail box is full.');
WHEN others THEN
DBMS_OUTPUT.put_line('Unable to send an email'||' '||SQLERRM);
END p_sendmail;
[/code]
Now calling the procedure to send an email:
[code]
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. *** ') ;
[/code]
I have also attached a screenshot of the error message below:
Please help me if you can. I am just unable to figure the issue out.
Thanks in advance
**Sandeep
**