Home » SQL & PL/SQL » SQL & PL/SQL » Decrease precision or scale error (11g)
Decrease precision or scale error [message #578103] Sun, 24 February 2013 08:11 Go to next message
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 #578105 is a reply to message #578103] Sun, 24 February 2013 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have been trying to decrease the number datatype length of a column empno.
But it shows decrease precision or scale error.

Quote:
Let me know by what query this can be solved.

TRUNCATE TABLE emp;


Regards
Michel

[Updated on: Sun, 24 February 2013 08:15]

Report message to a moderator

Re: Decrease precision or scale error [message #578107 is a reply to message #578103] Sun, 24 February 2013 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The column has not occupied more than 4 digits & current column size is 10.


This is the default value for number column size. See NUMWIDTH property of SQL*Plus.
Please read SQL*PlusĀ® User's Guide and Reference.

Regards
Michel

Re: Decrease precision or scale error [message #578112 is a reply to message #578105] Sun, 24 February 2013 08:23 Go to previous messageGo to next message
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
Member
Michel Cadot wrote on Sun, 24 February 2013 19:42
Quote:
I have been trying to decrease the number datatype length of a column empno.
But it shows decrease precision or scale error.

Quote:
Let me know by what query this can be solved.

TRUNCATE TABLE emp;


Regards
Michel

Thanks for your reply,
But I don't want to truncate I need solution without truncating.
Regards,
Ashish
Re: Decrease precision or scale error [message #578113 is a reply to message #578107] Sun, 24 February 2013 08:24 Go to previous messageGo to next message
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
Member
Michel Cadot wrote on Sun, 24 February 2013 19:44
Quote:
The column has not occupied more than 4 digits & current column size is 10.


This is the default value for number column size. See NUMWIDTH property of SQL*Plus.
Please read SQL*PlusĀ® User's Guide and Reference.

Regards
Michel



I am not talking about SQL*Plus width property.
I want to decrease the datatype length from 10 to 5.
So that number values greater than 5 cannot be stored in empno.
Regards,
Ashish
Re: Decrease precision or scale error [message #578117 is a reply to message #578113] Sun, 24 February 2013 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I gave you the answer for both questions.
Read them.
And be clearer in your next questions.

Regards
Michel
Re: Decrease precision or scale error [message #578118 is a reply to message #578112] Sun, 24 February 2013 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But I don't want to truncate I need solution without truncating.


DELETE emp;


Regards
Michel
Re: Decrease precision or scale error [message #578124 is a reply to message #578118] Sun, 24 February 2013 08:30 Go to previous messageGo to next message
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
Member
See I don't want to remove any data from the table, otherwise I will loose the data?
Re: Decrease precision or scale error [message #578127 is a reply to message #578124] Sun, 24 February 2013 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why do you think Oracle tells you:
column to be modified must be empty to decrease precision or scale


Regards
Michel
Re: Decrease precision or scale error [message #578128 is a reply to message #578127] Sun, 24 February 2013 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
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 #578132 is a reply to message #578128] Sun, 24 February 2013 08:41 Go to previous messageGo to next message
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
Member
Thanks for the information.
Well I thought might be if alternative solution without dropping the table would be there.
Now I will follow the same by renaming the table & creating new structure.
Thanks for your valuable time.
Regards,
Ashish
Re: Decrease precision or scale error [message #578135 is a reply to message #578132] Sun, 24 February 2013 09:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
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.
Re: Decrease precision or scale error [message #578137 is a reply to message #578128] Sun, 24 February 2013 09:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
Michel Cadot wrote on Sun, 24 February 2013 09:34

But anyway at one moment the table must be empty.


Column, not table.

SY.
Re: Decrease precision or scale error [message #578138 is a reply to message #578137] Sun, 24 February 2013 09:23 Go to previous messageGo to next message
ashishpatel1992
Messages: 38
Registered: February 2013
Location: India
Member
Solomon Yakobson wrote on Sun, 24 February 2013 20:50
Michel Cadot wrote on Sun, 24 February 2013 09:34

But anyway at one moment the table must be empty.


Column, not table.

SY.

Thanks.
You cleared all my doubts.
Regards,
Ashish
Re: Decrease precision or scale error [message #578139 is a reply to message #578137] Sun, 24 February 2013 09:34 Go to previous message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, "column", "table" was a misuse of language.
Thanks for the correction.

Regards
Michel
Previous Topic: How to Undo a drop table operation?
Next Topic: sql (merged)
Goto Forum:
  


Current Time: Tue Sep 23 17:37:35 CDT 2014

Total time taken to generate the page: 0.10593 seconds