| Decrease precision or scale error [message #578103] |
Sun, 24 February 2013 08:11  |
 |
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
|
Member |
|
|
I have been trying to decrease the number datatype length of a column empno.
But it shows decrease precision or scale error.
The column has not occupied more than 4 digits & current column size is 10.
How do i decrease it to 5.
See below the screen output of SQL PLUS
SQL> alter table emp modify empno number(6);
alter table emp modify empno number(6)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
SQL> desc emp;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(10)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
Let me know by what query this can be solved.
Regards,
Ashish
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Decrease precision or scale error [message #578128 is a reply to message #578127] |
Sun, 24 February 2013 08:34  |
 |
Michel Cadot
Messages: 54175
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You can copy the data elsewhere and copy back after modification.
You can create a new table with correct datatype, copy the data, drop the old table, and rename the new one.
You can export the data, empty the table, make your modifications and import the data.
You can use DBMS_REDEFINITION.
And so on.
But anyway at one moment the table must be empty.
Regards
Michel
[Updated on: Sun, 24 February 2013 08:34] Report message to a moderator |
|
|
|
|
|
| Re: Decrease precision or scale error [message #578135 is a reply to message #578132] |
Sun, 24 February 2013 09:10 |
Solomon Yakobson
Messages: 1398
Registered: January 2010
|
Senior Member |
|
|
SQL> create table tbl(
2 n number(10)
3 )
4 /
Table created.
SQL> insert
2 into tbl
3 select empno
4 from emp
5 /
14 rows created.
SQL> alter table tbl
2 modify n number(5)
3 /
modify n number(5)
*
ERROR at line 2:
ORA-01440: column to be modified must be empty to decrease precision or scale
SQL> alter table tbl
2 add n1 number(5)
3 /
Table altered.
SQL> update tbl
2 set n1 = n,
3 n = null
4 /
14 rows updated.
SQL> alter table tbl
2 modify n number(5)
3 /
Table altered.
SQL> update tbl
2 set n = n1
3 /
14 rows updated.
SQL> alter table tbl
2 drop column n1
3 /
Table altered.
SQL> desc tbl
Name Null? Type
----------------------------------------- -------- ----------------------------
N NUMBER(5)
SQL> select *
2 from tbl
3 /
N
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
N
----------
7900
7902
7934
14 rows selected.
SQL>
SY.
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
