I have a procedure in oracle database to send an email using a function we defined.
Called for example:
my_send_email stored procedure:
create or replace PROCEDURE my_SEND_EMAIL(p_from in varchar2,
p_to in varchar2,
p_cc in varchar2,
p_subject in varchar2,
p_message in varchar2)
is
BEGIN
utl_mail.send(sender => p_from,
recipients => p_to,
cc => p_cc,
subject => p_subject,
message => p_message
);
END my_SEND_EMAIL;
it works like a wrap to call the utl_mail.send.
but when I execute a simple email like below:
DECLARE
p_from VARCHAR2(500);
p_to VARCHAR2(2500);
p_cc VARCHAR2(500);
p_subject VARCHAR2(500);
p_message VARCHAR2(500);
BEGIN
p_from := 'noreply@mycompany.com';
p_to := 'techline@mycompany.com';
p_cc := 'myname@mycompany.com';
p_subject := 'This is a test to delete';
p_message := 'this is a test for email';
my_send_email(p_from => p_from, p_to => p_to, p_cc => p_cc, p_subject => p_subject, p_message => p_message);
--rollback;
END;
I got below error:
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PS.UTL_MAIL", line 662
ORA-06512: at "PS.UTL_MAIL", line 423
ORA-06512: at "PS.UTL_MAIL", line 679
ORA-06512: at "PS.my_SEND_EMAIL", line 11
ORA-06512: at line 13
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
And the email is not sent. any clue what is wrong?
Thanks much!