Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: Trouble inserting value to a column defined as Number (7,3)

    Having trouble inserting value to a column defined as Number (7,3) the value is, 0013460.

    Data is being read from a flat text file.

    I have tried the following with the indicated results:

    -- SUBSTR(V_INPUT_BUFFERS, 168,7) -- ORA-01438: value larger than specified precision allows for this column
    -- TO_NUMBER(LTRIM( SUBSTR(V_INPUT_BUFFERS, 168,7), 0) ) -- ORA-01438: value larger than specified precision allows for this column
    -- NVL(TO_NUMBER(LTRIM( SUBSTR(V_INPUT_BUFFERS, 168,7), 0),'9999D999'),0) -- ORA-01722: invalid number
    -- LTRIM( SUBSTR(V_INPUT_BUFFERS, 168,7), 0) -- ORA-01438: value larger than specified precision allows for this column
    -- TO_NUMBER(LTRIM( SUBSTR(V_INPUT_BUFFERS, 168,7), 0),'9999D999') -- ORA-01722: invalid number
    -- TO_NUMBER(LTRIM( SUBSTR(V_INPUT_BUFFERS, 168,7), 0),'9999D999') -- BASE_RT -- ORA-01722: invalid number

    Any Help would be greatly appreciated.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    When you specify number(7,3) you are saying that you have a number 7 in length where three positions are used for the decimal portion. In otherwords, you can insert a value from -9999.999 -> 9999.999

    If you want to store a number that is 7 digits in length, with 3 positions for the decimal postion, then use number(10,3).
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    Bill,

    The column in the table is defined as Number (7,3) and
    I want to store data that has a value from -9999.999 -> 9999.999.
    The input data from the flat file looks like 0013460
    I have used TO_NUMBER with a mask of 9999V99 as well as 9999D99 with no positive results.

    I hope this helps, Please help.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try


    1* select to_number('0013460')/1000 from dual
    SQL> /

    TO_NUMBER('0013460')/1000
    -------------------------
    13.46
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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