Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    4

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

    Hi All,
    Urgerntly need your help on below:
    My C++ progrma is trying to insert the data into the Oracle 10g through stroed procedure call, on my testing box i got everything running fine but on production box it is giving me following error.
    -- ORACLE error--
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 1
    ================================================== ==
    Although both boxes have oracle 10g on them and both have same stored procedure package for inseritng the data. I am really confused if there would have been some programming error then it would have also in testing box, but error is only coming in production box.
    Can you please advise what should i check in oracle 10g setting on production.
    Regards
    Kapil

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    This can be a data dependent error; similar to a divide by 0 error.
    I suspect it is caused by trying to convert non-numeric data into a numeric variable.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2008
    Posts
    4
    Hi, Actually i ahve tried running same message/data it is running file on one box but it is giving error on another.
    Can it be because some Oracle DB settings if yes, how cani check that.
    Regards
    Kapil

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    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.
    I trust Oracle is not misreporting reality.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2008
    Posts
    4
    Hi, yes you are right but in my prelive also i have oracle 10g ther oracle errors did not turn up. ha ha
    aneways thanks a lot for your support but it is not a problem with data but its something else.......

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If its' not data, then it is you.

    Obviously, you'll have to DEBUG that stored procedure (on a production database). Happy hunting!

    P.S. Check whether you have used implicit data type conversion. Perhaps you miss TO_NUMBER or TO_CHAR or ... somewhere in code.

  7. #7
    Join Date
    Mar 2008
    Posts
    4
    Hi, See this is the PLSQL statment which is parsed successfully:
    Created PLSQL statement :BEGIN :RESULT:=cvload.te.insert_trade(TO_NUMBER(:PARAM1) , TO_NUMBER(:PARAM2), :PARAM3, TO_DATE(:PARAM4, 'HH24:MIS'), :PARAM5, :PARAM6, :PARAM7, :PARAM8, :PARAM9, :PARAM10, TO_DATE(:PARAM11, 'DDMMYYYY'), :PARAM12, :PARAM13, :PARAM14, TO_NUMBER(:PARAM15), TO_NUMBER(:PARAM16), :PARAM17, :PARAM18, :PARAM19, TO_NUMBER(:PARAM20), :PARAM21, :PARAM22, :PARAM23, :PARAM24, :PARAM25, :PARAM26, TO_DATE
    (:PARAM27, 'DDMMYYYY')); END;
    PLSQL statement parsed OK.
    There are four NUMBER type parameters for which i have supplied TO_NUMBER. The Sp function is :
    function insert_trade (
    p_te_id in number, -- 01
    p_buysell in number, -- 02
    p_contract in varchar2, -- 03
    p_hhstamp in date, -- 04
    p_market in varchar2, -- 05
    p_member in varchar2, -- 06
    p_operator in varchar2, -- 07
    p_orderid in varchar2, -- 08
    p_ordernumber in varchar2, -- 09
    p_source in varchar2, -- 10
    p_tradedate in date, -- 11
    p_tradingenv in varchar2, -- 12
    p_expiry in varchar2, -- 13
    p_callput in varchar2, -- 14
    p_strike in number, -- 15
    p_price in number, -- 16
    p_broker in varchar2, -- 17
    p_broker_ot in varchar2, -- 18 sm 20006.04.21 te extension (order taking broker)
    p_gimsg in varchar2, -- 19
    p_volume in integer, -- 20
    p_trader in varchar2, -- 21
    p_account in varchar2, -- 22
    p_openclose in varchar2, -- 23
    p_accounttype in varchar2, -- 24
    p_action in varchar2, -- 25
    p_tradeversion in varchar2, -- 26
    p_valuedate in date, -- 27
    -----------------------------------------------------------------------
    Now the same set of data passed on one box runs fine and on other it gives error, but both have Oracle 10g only. you talked about implicit converison can you please tell where more i can check about this except the above statment in which i have already given TO_NUMBER

    Regards
    Kapil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •