Home » SQL & PL/SQL » SQL & PL/SQL » Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments (O/S:Windows 8; Product: Oracle; version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production )
Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628733] Thu, 27 November 2014 10:41 Go to next message
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
/forum/fa/12333/0/


[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 #628735 is a reply to message #628733] Thu, 27 November 2014 11:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
PLS-00306:Wrong number or types of arguments in call to P_SENDMAIL.


So check where you call P_SENDMAIL and check its parameters against the procedure definition.

Re: Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628736 is a reply to message #628735] Thu, 27 November 2014 11:34 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I have Michel i.e. checked the p_sendmail procedure definition and also where it is being called (which is actually given above) and despite it being 5 parameters at all places, it still fails. Any idea? I have been troubleshooting this for over 2 days now before posting the message here.
Re: Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628737 is a reply to message #628736] Thu, 27 November 2014 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Any idea?


How could we as we have not the root of the error?
Any idea?
Re: Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628738 is a reply to message #628737] Thu, 27 November 2014 11:56 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
what do you mean by "we have not the root of the error?". I am not sure what you intent to say Michel.
Re: Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628739 is a reply to message #628738] Thu, 27 November 2014 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Somewhere in your code you call the procedure, you posted the procedure, you didn't post where the error occurs.

Re: Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628740 is a reply to message #628739] Thu, 27 November 2014 12:07 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
As I said the error occurs when I try to compile the whole file (the procedure is in a package body and this package body is named as a file called mzb_bwakpl.sql) at SQL * PLUS prompt, it throws the error. The question of me debugging is still out of sight as the issue exists at compilation time itself.
Re: Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628741 is a reply to message #628740] Thu, 27 November 2014 12:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Execute "set echo on" and your script then post the result.

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 Go to previous messageGo to next message
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

Re: Unable to get the procedure compiled -PLS-00306:Wrong number or types of arguments [message #628743 is a reply to message #628742] Thu, 27 November 2014 12:46 Go to previous message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Barbara: You are perfectly correct. In the procedure header I was passing it as msg_subject and while calling it was p_msg_subject. The minute I corrected the error, I was able to successfully compile the whole package body.

Thanks a ton and thanks a lot for taking the trouble.

Michel : Thanks for suggesting "set echo on" command. It did help.

Thanks once again and this issue can be closed
Previous Topic: Return individuals most regular areas
Next Topic: how often to analyze partition table
Goto Forum:
  


Current Time: Tue Apr 23 01:02:20 CDT 2024