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

Di Oracle menurunkan panjang sebuah kolom tidak semudah menaikkannya. Jika bertemu dengan ORA-01440, kita bisa menggunakan solusi yang ditawarkan di [1].

Berikut contohnya : 

alter table depot.tbl_botoljaminan add njaminan_temp NUMBER(10,2);
update depot.tbl_botoljaminan set njaminan_temp = njaminan ;
update depot.tbl_botoljaminan set njaminan = null;
alter table depot.tbl_botoljaminan modify njaminan NUMBER(10,2);
update depot.tbl_botoljaminan set = njaminan_temp;
alter table depot.tbl_botoljaminan drop column njaminan_temp ;
ALTER TABLE depot.tbl_botoljaminan DROP COLUMN njaminan_temp ; (di Oracle 8i tidak bisa) ;
CREATE TABLE depot.tbl_botoljaminan_backup2 AS SELECT cnobukti,  ckode_customer, cjenis_trans, njaminan, nkwantitas, cketerangan, dtanggal, ctransfer , cnotransaksi FROM depot.tbl_botoljaminan; 
DROP TABLE depot.tbl_botoljaminan ; 
CREATE TABLE depot.tbl_botoljaminan AS SELECT * FROM depot.tbl_botoljaminan_backup2 ; 
DROP TABLE depot.tbl_botoljaminan_backup2 ;


Referensi 

  1. Changing precision of numeric column in Oracle, http://stackoverflow.com/questions/9233909/changing-precision-of-numeric-column-in-oracle


Comments