Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628733] |
Thu, 27 November 2014 10:41 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
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 me
with this and tell me where exactly the error could be. Code 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);
The procedure itself:
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;
Now calling the procedure to send an email:
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. *** ') ;
I have also attached a screenshot of the error message below
[mod-edit: image inserted into message body by bb]
[Updated on: Thu, 27 November 2014 11:54] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628742 is a reply to message #628741] |
Thu, 27 November 2014 12:16 |
|
Barbara Boehmer
Messages: 9090 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You have defined your fourth parameter in your procedure as msg_subject, but are calling it as p_msg_subject. That causes the error.
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_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' || ...
[Updated on: Thu, 27 November 2014 12:18] Report message to a moderator
|
|
|
|