Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: numeric or value error(How to use date as an date as input parameter) (oracle 9i)
PL/SQL: numeric or value error(How to use date as an date as input parameter) [message #584391] Mon, 13 May 2013 10:39 Go to next message
ashwanth77
Messages: 94
Registered: April 2013
Location: India
Member
How to use date as an input parameter,im supposed to use varchar2 as the data type


CREATE OR REPLACE PROCEDURE mail1 ( recievers VARCHAR2 ,p_date in varchar2 )
IS
sender VARCHAR2(30) := 'xyz@gmail.com';
mailhost VARCHAR2(100) := 'host address';
TAB VARCHAR(2) := CHR(9);
mail_conn utl_smtp.connection;
len NUMBER := 1;
v_addr VARCHAR2(50);
mesg VARCHAR2( 4000 );
Subject VARCHAR2( 4000 );
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );


cursor c1 is
--select activity_date,procedure_name,status_message,error_desc from staging_activity_log where rownum between 1 and 10 ;
select activity_date,procedure_name,status_message,error_desc from staging_activity_log where error_desc is not null and trunc(activity_date) >= to_date(p_date,'DD-MON-YYYY') ;
BEGIN


mail_conn :=utl_smtp.open_connection(mailhost,25);
mesg:=

''|| crlf ||
'activity_date' || ' ' ||' procedure_name ' || ' ' || 'status_message' || ' '||' error_desc'||crlf||
'----------------------' ||' ------------' ||' ----------' ||' -----------------'||'-------------' ||crlf;
FOR c1rec IN c1 LOOP
mesg := mesg || c1rec.activity_date||' '||c1rec.procedure_name ||' '||c1rec.status_message|| ' ' ||c1rec.error_desc || crlf;
END LOOP;

utl_smtp.helo(mail_conn,mailhost);
utl_smtp.mail(mail_conn,sender); -- sender

WHILE(INSTR(recievers,',',len) > 0) LOOP
v_addr := SUBSTR(recievers, len, INSTR(SUBSTR(recievers,len),',')-1);
len := len+INSTR(SUBSTR(recievers, len),',');
utl_smtp.rcpt(mail_conn, v_addr);
END LOOP;



utl_smtp.open_data(mail_conn);

utl_smtp.write_data(mail_conn,'From: '||sender|| crlf );
utl_smtp.write_data(mail_conn,'To: '||recievers|| crlf );
utl_smtp.write_data(mail_conn,'Subject: Test Mail '|| crlf );
utl_smtp.write_data(mail_conn,'Message:' || mesg|| crlf );

utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);

END mail1;
/

Procedure created

exec mail1 ('xxx@gmail.com,yyy@gmail.com','28-JAN-2008');

when i execute i get

BEGIN mail1 ('xxx@gmail.com,yyy@gmail.com,'28-jan-2008'); END;
Error at line 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MAIL1", line 27
ORA-06512: at line 1
Re: PL/SQL: numeric or value error(How to use date as an date as input parameter) [message #584392 is a reply to message #584391] Mon, 13 May 2013 10:53 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

Error appears to be coming from this line:
mesg := mesg || c1rec.activity_date||' '||c1rec.procedure_name ||' '||c1rec.status_message|| ' ' ||c1rec.error_desc || crlf;

If you want to concatenate a date to a string it might be an idea to to_char it.
Re: PL/SQL: numeric or value error(How to use date as an date as input parameter) [message #584395 is a reply to message #584391] Mon, 13 May 2013 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, the WHOLE session including the error.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: PL/SQL: numeric or value error(How to use date as an date as input parameter) [message #584531 is a reply to message #584395] Wed, 15 May 2013 01:47 Go to previous message
Michel Cadot
Messages: 58573
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OP chose to not feedback here and repost his question at http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=63440

Regards
Michel
Previous Topic: Query for below scenario using analytical functions
Next Topic: ERROR : ORA-06512: at "NECDEMO.GETUESLIDREGTIME", line 25
Goto Forum:
  


Current Time: Fri Jul 25 16:27:50 CDT 2014

Total time taken to generate the page: 0.21960 seconds