OraFAQ Forum: SQL & PL/SQL » PL/SQL email config

Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL email config

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL/SQL email config [message #628064]

Wed, 19 November 2014 12:35

slemma
Messages: 3
Registered: November 2014

Junior Member

I was trying to config email but when i try to execute the source code i get the error below. I am using APEX.

declare v_forum_text varchar2(4000) := null;
v_email varchar2(128) := null;
v_sent_id number := NULL;

BEGIN

select count(*) into v_sent_id from users where user_id = :P3_USERS and email is not null;
select forum_text into v_forum_text from email_forum_templates where forum_text = :P3_FORUM_TEXT;
select email into v_email from users where user_id = :P3_USERS;

apex_mail.send(
p_from => 'Project@yahoo.com',
p_to => v_email,
p_subj => 'Victims Survey',
p_body => 'Hello,' || v_forum_text || ' ',
p_body_html => 'Hello,' || v_forum_text || ' ');

END;

error

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

[Updated on: Wed, 19 November 2014 12:37]

Report message to a moderator

Re: PL/SQL email config [message #628066 is a reply to message #628064]

Wed, 19 November 2014 13:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: PL/SQL email config [message #628067 is a reply to message #628064]

Wed, 19 November 2014 13:39

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Try to execute it with SQL*Plus and tell us at which line is the error.
I bet for 1st or 3rd SELECT and some user_id are not a number and P3_USERS is declare as a number.

Report message to a moderator

Re: PL/SQL email config [message #628383 is a reply to message #628067]

Mon, 24 November 2014 13:09

slemma
Messages: 3
Registered: November 2014

Junior Member

Thank you for the help it is working after i commented out the last select statement but only for one user. I am trying to pick multiple users and send it but that doesn't work. Any idea? Below is the source

declare v_forum_text varchar2(4000) := :P3_FORUM_TEXT;
v_email varchar2(128) := null;
v_sent_id number := NULL;

BEGIN

select count(*) into v_sent_id from users where user_id = :P3_USERS and email is not null;
select email into v_email from users where user_id = :P3_USERS;

v_sent_id:= apex_mail.send(
p_from => 'ProjectLOVE@juno.com',
p_to => v_email,
p_subj => 'Victims of Sex Trafficking Survey',
p_body => 'Hello,' || v_forum_text || ' ',
p_body_html => 'Hello,' || v_forum_text || ' ');

END;

Thank you,
Solomon

Report message to a moderator

Re: PL/SQL email config [message #628385 is a reply to message #628383]

Mon, 24 November 2014 13:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: PL/SQL email config [message #628388 is a reply to message #628383]

Mon, 24 November 2014 13:16

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Always post your Oracle version, with 4 decimals.
Use SQL*Plus and copy and paste your session, the WHOLE session.

Report message to a moderator

Re: PL/SQL email config [message #628405 is a reply to message #628388]

Mon, 24 November 2014 17:03

slemma
Messages: 3
Registered: November 2014

Junior Member

Ok i will thank you,

Report message to a moderator

Previous Topic:	divisor is equal to zero error
Next Topic:	INSERT with subquery in VALUES

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 09:38:26 CDT 2024