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

[parijat67]

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

[julian]

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

[parijat67]

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

[shadow21]

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

[newora]

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

[parijat67]

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

[newora]

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.

[parijat67]

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

[newora]

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

[JosueMonteiro]

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