#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    PL/SQL: numeric or value error


    This procedure works fine,btut when i include P_date i start getting PL/SQL: numeric or value error.I must use only varchar2 for P_date

    CREATE OR REPLACE PROCEDURE mail1 ( recievers VARCHAR2 ,p_date in varchar2 )
    IS
    sender VARCHAR2(30) :=(E-Mail address)';
    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 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 ||to_char( 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 ('E-Mail address1,E-Mail address2','28-JAN-2008');
    when i execute i get the following error

    BEGIN mail1 ('E-Mail address1,E-Mail address2','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
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    845
    Rep Power
    387
    ORA-06502: PL/SQL: numeric or value error string
    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 which is line 27?
    You need to format your code using the "code" tags (# symbol above).

IMN logo majestic logo threadwatch logo seochat tools logo