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

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

Are you using the MODIFY option with the ALTER TABLE command?

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

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

u can alter the column of to_tab to number(38,19) and then can insert from number(38) to number(38,19)

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

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.

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

the column was NUMBER only.
and I had to change it to NUMBER(15,2)

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