how to insert into number(19,19) from number(38)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: how to insert into number(19,19) from number(38)

  1. #1
    Join Date
    Dec 2001
    Posts
    120
    hi all,

    how can i insert into a column of datatype NUMBER(19,19) from a column which has datatype of NUMBER(38). Right now I am getting an error called :
    ORA-01438: value larger than specified precision allows for this column

    pls let me know the workaround if there.

    thanks in advance!
    Parijat

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Are you using the MODIFY option with the ALTER TABLE command?


  3. #3
    Join Date
    Dec 2001
    Posts
    120

    ORA-01440: column to be modified must be empty to decrease precision or scale.

    hi,

    I even tried doing that . It gives the following error.

    ORA-01440: column to be modified must be empty to decrease precision or scale.

    -----------------

    even i tried to cut & paste the values from Number(38) to number(19,19) but that also fails!

    what could be the solution pls let me know.

    thanks

    Paul

  4. #4
    Join Date
    Apr 2001
    Posts
    107
    If you're not on prod database, you could do an export, truncate your table, alter your table, and import with ignore=Y

    Hope it helps

    Cheers

    Fabien

  5. #5
    Join Date
    Feb 2002
    Posts
    27
    u can alter the column of to_tab to number(38,19) and then can insert from number(38) to number(38,19)

  6. #6
    Join Date
    Dec 2001
    Posts
    120

    It worked!

    Thanks to all u guys!

    The problem occured due to inserting Number(38) into number(19,19). It works fine when inserted into number(38,19) from number(38).

    thanks once again.

    Parijat

  7. #7
    Join Date
    Feb 2002
    Posts
    27
    U r lucky .
    I faced a similer situation where I have to change the data type from NUBER to NUMBER(15,2) and I had only one option to exp/imp.


  8. #8
    Join Date
    Dec 2001
    Posts
    120
    but why u had to exp/imp ? the update on number(15,2) from number(38) should have worked. Would u pls mention why u did it through exp/imp and not through update or insert?

    thanks for ur help.

    Parijat

  9. #9
    Join Date
    Feb 2002
    Posts
    27
    the column was NUMBER only.
    and I had to change it to NUMBER(15,2)


  10. #10
    Join Date
    Feb 2009
    Posts
    1

    Thumbs up To documentate

    Hello,

    I suggest you the following procedure:

    1 - Add a temp column in the end of your table:
    alter table your_table add (col_temp varchar(300))
    2 – Copy the values of your official column to the new one:
    update your_table set col_temp = TO_CHAR(your_official_column_name)
    3 – Set your official column to null:
    update your_table set your_official_column_name = NULL
    4 – Adjust your official column with the usual alter table:
    Alter table your_table modify your_official_column_name number(X,Y);
    OBS: X,Y = Precision scale
    5 – Copy the values back to the original column:
    update your_table set your_official_column_name = TO_NUMBER(col_temp)
    6 – Drop the temporary column:
    alter table your_table drop column col_temp

    Good lock
    Josué Monteiro Viana

Posting Permissions

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


Click Here to Expand Forum to Full Width