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!

ora-06512(numeric or value error) number precision too large

Discussion in 'SQL PL/SQL' started by mdevendh, Aug 8, 2011.

  1. mdevendh

    mdevendh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    CREATE OR REPLACE
    PROCEDURE SP_THRESHOLD_ROLLUP AS
    seq NUMBER(5) :=1;
    FX_AMT NUMBER(32,18);
    ACC_NO VARCHAR2(34);
    DR_AMOUNT NUMBER(15,3);
    PRIORITY VARCHAR2(1 Byte);
    SR_CCY CHAR(3 byte);
    DEST_CCY CHAR(3 byte);
    DEBIT_AMOUNT NUMBER(15,3);
    PYMTREF CHAR(8 byte);
    CUSTID CHAR(7 Byte);
    COUNTRYCODE CHAR(2 Byte);
    CITYCODE CHAR(3 Byte);
    ROWCOUNT NUMBER;


    cursor FX_RATE_CUR IS
    select dr_amt,AMT_PRIORITY,DR_CCY,PYMT_CCY,PYMT_REF,CUST_ID,COUNTRY_CODE,CITY_CODE
    from TL_PYMT_DETAILS
    WHERE STATUS_CODE='24';


    BEGIN

    OPEN FX_RATE_CUR;
    LOOP
    FETCH FX_RATE_CUR INTO
    DR_AMOUNT,
    PRIORITY,
    SR_CCY,
    DEST_CCY,
    PYMTREF,
    CUSTID,
    COUNTRYCODE,
    CITYCODE;
    --raise_application_error(-20001,'no VALUE');
    EXIT WHEN (FX_RATE_CUR%NOTFOUND);
    -- raise_application_error(-20002,'no VALUE');
    IF PRIORITY <> 'D' THEN
    SELECT RATE_AMT INTO FX_AMT
    FROM TL_FX_RATES
    WHERE SRCE_CRNCY=SR_CCY
    AND DSTN_CRNCY=DEST_CCY;
    --raise_application_error(-20003,fx_amt);
    DEBIT_AMOUNT:=round(FX_AMT*DR_AMOUNT,3);
    --raise_application_error(-20001,DEBIT_AMOUNT);
    UPDATE TL_PYMT_DETAILS
    SET dr_amt = DEBIT_AMOUNT
    WHERE PYMT_REF=PYMTREF
    AND CUST_ID=CUSTID
    AND COUNTRY_CODE = COUNTRYCODE
    AND CITY_CODE = CITYCODE;
    --raise_application_error(-20002,dr_amt);


    END IF;
    END LOOP;
    CLOSE fx_rate_CUR;
     
  2. mdevendh

    mdevendh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    please help for above error

    please help for above error
     
  3. mdevendh

    mdevendh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi plz help me anyone for this prob
     
  4. UnixGuru

    UnixGuru Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    Haven't checked your code for logic, but looking at the syntax, this statement

    seq NUMBER(5) :=1;

    seems to be what is causing the numeric or value error
     
  5. mdevendh

    mdevendh Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    thanks..... error is resolved that s bcz of mutating triger error
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It helps tremendously if you post ALL errors and text and do so in context:

    Code (SQL):
    SQL> CREATE materialized VIEW yarpnop_mv
      2  refresh fast
      3  AS
      4  SELECT sysdate, yarpnop.*
      5  FROM yarpnop;
    FROM yarpnop
         *
    ERROR at line 5:
    ORA-12015: cannot CREATE a fast refresh materialized VIEW FROM a complex query
     
    It makes it much easier to troubleshoot errors and provide correct solutions.