1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL/SQL: numeric or value error

Discussion in 'SQL PL/SQL' started by ashwanth, May 15, 2013.

  1. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    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. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    May be the following will be problem.

    " mesg " Variable can store maximum of 4000 characters.

    But in the for loop you are getting more than 4000 characters.

    copy the follwing code in FOR Loop and check

    begin
    mesg := mesg ||to_char( c1rec.activity_date)||' '||c1rec.procedure_name ||' '||c1rec.status_message|| ' ' ||c1rec.error_desc || crlf;
    exception
    WHEN OTHERS THEN
    dbms_output.put_line('Exception : '||SQLERRM);
    end;




    Regards
    Sambasiva Reddy.K
     
  3. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Taking the following quote as true:
    The problem may lie here:
    Plz, post the format of the value passed into p_date parameter and also the value of trunc(activity_date). Just any one actual value.
    I'm hinting at a genuine date format mismatch.
     
  4. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Ari,

    He already posted that one.

    exec mail1 ('E-Mail address1,E-Mail address2','28-JAN-2008');


    I think that's not an issue.


    Regards
    Sambasiva Reddy.K
     
  5. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Oops !! :eek: i failed to copy that.

    Ok !! Just try the same by commenting the line:
    If it does work, then i can suggest u a workaround. ;)

    Coz many times we face this regarding date values

    I'm still relying on the statement
    So i believe its not the size of the mesg variable size which is the problematic element.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please stop guessing at where the error is located -- the error text clearly states it occurs at line 27, so you need to query USER_SOURCE for this procedure and return the text where line = 27. You will then have a place to actually begin your troubleshooting.
     
  7. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    Hi all,

    the problem was due to the size of mesg variable.i used filters in my query then it worked .thanks:)
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You could also have increased the variable size as the character limit for varchar2 variables in PL/SQL is 32767, not 4000.