Home » SQL & PL/SQL » SQL & PL/SQL » Number datatype size cant decreased..Why??????
Number datatype size cant decreased..Why?????? [message #448238] Mon, 22 March 2010 04:42 Go to next message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Hi all,

In my table there is column with number datatype of size col1 number(15,3) and my data in column is like
001
002
003

and i am changing its size to number(10,3) by alter table command but it not allowing. why?????? as my data in that column still satisfy the the changes.

and even when i modify the char column to varchar2 column by alter table command and changing the size of that column,it is not allowing me to change it why?????

kindly reply pls.


Thanks,
Amit Sonar.
Re: Number datatype size cant decreased..Why?????? [message #448239 is a reply to message #448238] Mon, 22 March 2010 04:48 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What error message are you getting?
Re: Number datatype size cant decreased..Why?????? [message #448250 is a reply to message #448238] Mon, 22 March 2010 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because Oracle will not scan your table to check if your data are correct (and it can't do it as data may concurrently be modified), so if some/any data might not satisfied the new definition the statement is rejected (and this is clearly specified in the documentation).

Regards
Michel

[Updated on: Mon, 22 March 2010 05:23]

Report message to a moderator

Re: Number datatype size cant decreased..Why?????? [message #448258 is a reply to message #448238] Mon, 22 March 2010 06:31 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
Hi Amith,

Alteris a DDL command, you have to exceute the Alter command when the database object(Table) is defined and it contains no data.

For example temp table contains a column like
n number(10,5).
you can change its size by alter command successfully.
But if the column contains any data i.e less or more than the modified column size it causes error.

ex: temp2(s char(20));

s
---
oracle
forms
reports

So you can modify the s column datatype by alter command with size must be greater than 7.

you should not allow to modify the column less than that size.
Re: Number datatype size cant decreased..Why?????? [message #448262 is a reply to message #448258] Mon, 22 March 2010 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
you have to exceute the Alter command when the database object(Table) is defined and it contains no data.

Quote:
But if the column contains any data i.e less or more than the modified column size it causes error.

Quote:
So you can modify the s column datatype by alter command with size must be greater than 7

No, this are not the rules.

SQL> select * from t;
COL
----------
oracle
forms
reports

3 rows selected.

SQL> alter table t modify col char(8);
alter table t modify col char(8)
                     *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big


The rules are:
- if you change the datatype (to a different kind) or change with the same datatype but decrease the size, then the table must be empty
- you can increase the size even if the table is not empty

And even these rules depend on the version, for instance 10g (don't remember for 9i) accepts to decrease varchar2 column size when data are present but not precision or scale of a number. Of course CHAR columns cannot be decreased as all data have the maximum length.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Mon, 22 March 2010 07:05]

Report message to a moderator

Re: Number datatype size cant decreased..Why?????? [message #448269 is a reply to message #448262] Mon, 22 March 2010 07:41 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
If you are creating a table with character data type it always takes the maximum length for all columns data.
i.e. if c char(10).
it always takes length as 10 for each row if the data is less than 10 characters.
So you cannot able to alter the column size.

you can alter the tables column size if it is a varchar data type.

Please find the attatchment for detailed solution.
it will help you to understand easily....

Regards,
siva
  • Attachment: sol.txt
    (Size: 2.62KB, Downloaded 152 times)
Re: Number datatype size cant decreased..Why?????? [message #448274 is a reply to message #448269] Mon, 22 March 2010 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what I said but NOT what you previously said:
Quote:
ex: temp2(s char(20));
...
So you can modify the s column datatype by alter command with size must be greater than 7.

And I showed you it is wrong and explain why, I see you now understood it.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand
Re: Number datatype size cant decreased..Why?????? [message #448276 is a reply to message #448238] Mon, 22 March 2010 08:13 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) You've just repeated what Michel said, but badly. varchar and char are both character datatypes, but only char behaves as you've described.
2) The attachment appears to be empty.
3) Code should be posted directly in the thread (using code tags - see the orafaq forum guide if you're not sure how) and not in an attachment unless it's really long, and this solution shouldn't be.
Re: Number datatype size cant decreased..Why?????? [message #448278 is a reply to message #448276] Mon, 22 March 2010 08:20 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
I am telling that it is not possible to alter a char datatype column to less size,

we can able to alter the variable length char data type only.

and there may be a problem with the attached file and the code is ......
SQL> create table t(c char(10));

Table created.

SQL> insert into t values('oracle');

1 row created.

SQL> insert into t values('reports');

1 row created.

SQL> select * from t;

C                                                                                     
----------                                                                                    
oracle                                                                                              
reports                                                                                             

SQL> alter table t modify c char(8);
alter table t modify c char(8)
                     *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big 


SQL>  alter table t modify c char(9);
 alter table t modify c char(9)
                      *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big 


SQL> alter table t modify c char(10);

Table altered.

SQL> select length(c) from t;

 LENGTH(C)                                                                                          
----------                                                                                          
        10                                                                                          
        10                                                                                          

SQL> drop table t;

Table dropped.

SQL> create table t(c varchar2(10));

Table created.

SQL> insert into t values('oracle');

1 row created.

SQL> insert into t values('reports');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

C                                                                                                   
----------                                                                                          
oracle                                                                                              
reports                                                                                             

SQL> select length(c) from t;

 LENGTH(C)                                                                                          
----------                                                                                          
         6                                                                                          
         7                                                                                          

SQL> alter table t modify c char(8);

Table altered.






CM: Added [code] tags.

[Updated on: Mon, 22 March 2010 08:23] by Moderator

Report message to a moderator

Re: Number datatype size cant decreased..Why?????? [message #448279 is a reply to message #448238] Mon, 22 March 2010 08:21 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
amit.sonar wrote on Mon, 22 March 2010 04:42
Hi all,

In my table there is column with number datatype of size col1 number(15,3) and my data in column is like
001
002
003


Impossible.
Re: Number datatype size cant decreased..Why?????? [message #448281 is a reply to message #448238] Mon, 22 March 2010 08:25 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
@sivaora
I asked you to use code tags, you didn't, with the result that some of your text was converted into smilies. I've fixed it but next time use code tags.

Also I don't see any actual solution to the problem, just an example of changing varchar to char.
Re: Number datatype size cant decreased..Why?????? [message #448286 is a reply to message #448281] Mon, 22 March 2010 08:49 Go to previous messageGo to next message
sivaora
Messages: 119
Registered: October 2009
Location: Hyderabad
Senior Member
It is not possible to alter the column size of a table if it is a number data type with precision and scale specification and if the table contains any data.
it is possible to alter the number(p,s) datatype only the table is empty.And the error message is also very clear..
"column to be modified must be empty to decrease precision or scale"

But in case of number(p) {with out scale specification)we can alter according to the rules.

Regards
siva
Re: Number datatype size cant decreased..Why?????? [message #448287 is a reply to message #448286] Mon, 22 March 2010 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It is not possible to alter the column size of a table if it is a number data type with precision and scale specification and if the table contains any data.
it is possible to alter the number(p,s) datatype only the table is empty.

Wrong:
SQL> create table t (col number(5,2));

Table created.

SQL> insert into t values (100.21);

1 row created.

SQL> commit;

Commit complete.

SQL>  alter table t  modify col number(7,3);

Table altered.


Quote:
And the error message is also very clear..
"column to be modified must be empty to decrease precision or scale"

Yes, it is clear, see the keyword in bold.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Mon, 22 March 2010 08:54]

Report message to a moderator

Re: Number datatype size cant decreased..Why?????? [message #448328 is a reply to message #448287] Mon, 22 March 2010 14:00 Go to previous messageGo to next message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Guys thanks for your contribution......pls see below

first case- alter table with no data


SQL>
SQL>
SQL> create table test_num(col1 number(5,3),col2 char(30), col3 varchar2(30));

Table created.

SQL> alter table test_num modify col1 number(4,1);

Table altered.

SQL> alter table test_num modify col2 char(25);

Table altered.

SQL> alter table test_num modify col3 varchar2(25);

Table altered.

SQL>


no doubt about it........

Now table has some data

select * from test_num;

COL1 COL2 COL3
----- ------------------------- -------------------------
1 sam neil
2 monica bellucci
3 Will smith


Second case:- altering table having data

SQL> alter table test_num modify(col1 number(3,1));
alter table test_num modify(col1 number(3,1))
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale


SQL> ed
Wrote file afiedt.buf

1* alter table test_num modify(col1 number(5,1))
SQL> /

Table altered.

SQL>
SQL>
SQL> alter table test_num modify(col2 char(20));
alter table test_num modify(col2 char(20))
*
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big


SQL> alter table test_num modify(col3 varchar2(20));

Table altered.


no doubt about second case also

please care fully see 3rd one

SQL> alter table test_num modify(col2 varchar2(25));

Table altered.

SQL> alter table test_num modify(col2 varchar2(20));
alter table test_num modify(col2 varchar2(20))
*
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big


SQL> desc test_num;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER(5,1)
COL2 VARCHAR2(25)
COL3 VARCHAR2(20)

SQL>
SQL>
SQL> select length(col2) from test_num;

LENGTH(COL2)
------------
25
25
25


In third case i modifed the col2 char(30) to col2 varchar2(25), table altered successfully. When i modified col3 varchar2(20) then it succeed.

SQL> alter table test_num modify(col3 varchar2(15));

Table altered.

Now later on when i modifed the col2 varchar2(25) to col2 varchar2(20) then its giving error.

it is still showing fixed length but data type of col2 is varchar2. It should show variable length as per data in that column as below.....

1* select length(col3) from test_num
SQL> /

LENGTH(COL3)
------------
4
8
5


so from this scenario i came to conclusion that

1) When i decrease size of column with data type varchar2 defined at the time table creation, it allowed me to do that.

1) Even i modify char column to varchar2 column, this will succeed but later on when i go to decrease it's size, oracle wont allowo do it.

It means oracle internally treated as that column as char column only.

Please tell me your opinions.

Thanks,
Amit.
Re: Number datatype size cant decreased..Why?????? [message #448329 is a reply to message #448328] Mon, 22 March 2010 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In third case i modifed the col2 char(30) to col2 varchar2(25), table altered successfully

You forgot that you have changed it to char(25) before when table was empty.

Quote:
Now later on when i modifed the col2 varchar2(25) to col2 varchar2(20) then its giving error.

Because data have 25 characters.

Regards
Michel

Re: Number datatype size cant decreased..Why?????? [message #448330 is a reply to message #448286] Mon, 22 March 2010 14:12 Go to previous messageGo to next message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Hi Siva,

Even if you have column with dataype number havin precision only, you cant decrease the size of that column. Here is explaination.

SQL> create table test_1(col1 number(10));
SQL> insert into test_1 values(10001);

1 row created.

SQL> ed
Wrote file afiedt.buf

1* insert into test_1 values(10002)
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_1;

COL1
----------
10001
10002

SQL>
SQL> desc test_1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER(10)

SQL> alter table test_1 modify(col1 number(7));
alter table test_1 modify(col1 number(7))
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
Re: Number datatype size cant decreased..Why?????? [message #448331 is a reply to message #448329] Mon, 22 March 2010 14:24 Go to previous messageGo to next message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Hi Michel,

How??? i converted to varchar2 and its variable length datatype. then its should give me length as per data in that column.

Thnks
Amit.
Re: Number datatype size cant decreased..Why?????? [message #448332 is a reply to message #448331] Mon, 22 March 2010 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Number datatype size cant decreased..Why?????? [message #448333 is a reply to message #448331] Mon, 22 March 2010 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
amit.sonar wrote on Mon, 22 March 2010 14:24
Hi Michel,

How??? i converted to varchar2 and its variable length datatype. then its should give me length as per data in that column.

Thnks
Amit.

I don't understand what you mean and what the "how" refers to.

Regards
Michel

Re: Number datatype size cant decreased..Why?????? [message #448412 is a reply to message #448331] Tue, 23 March 2010 04:34 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
amit.sonar wrote on Mon, 22 March 2010 14:24
Hi Michel,

How??? i converted to varchar2 and its variable length datatype. then its should give me length as per data in that column.

Thnks
Amit.


And that's exactly what it did.
You had a char column - char columns have trailing spaces.
You converted it to a varchar - the trailing spaces will remain
Varchar will store trailing spaces, it just doesn't add them automatically like char does.
Observe:
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> create table test (a char(10));

Table created.

SQL> insert into test (a) values ('four');

1 row created.

SQL> commit;

Commit complete.

SQL> select length(a) from test;

 LENGTH(A)
----------
        10

SQL> alter table test modify a varchar2(10);

Table altered.

SQL> select length(a) from test;

 LENGTH(A)
----------
        10

SQL> alter table test modify a varchar2(5);
alter table test modify a varchar2(5)
                        *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big


SQL> update test set a = trim(a);

1 row updated.

SQL> commit;

Commit complete.

SQL> select length(a) from test;

 LENGTH(A)
----------
         4

SQL> alter table test modify a varchar2(5);

Table altered.

SQL> 


Re: Number datatype size cant decreased..Why?????? [message #448495 is a reply to message #448333] Tue, 23 March 2010 14:08 Go to previous messageGo to next message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Hi Michel,

I meant to say i converted char column to varchar2 and car is fixed length and varchar2 is variable length. If the column is of varchar2 then data in that column should have variable length size according to string size but there still its showing size as 25.

Thanks
Amit.
Re: Number datatype size cant decreased..Why?????? [message #448496 is a reply to message #448412] Tue, 23 March 2010 14:11 Go to previous messageGo to next message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Hi Cookimaster,

Thanks for the good explanation with example. I will execute as per your explainnation.


Thanks,
Amit.
Re: Number datatype size cant decreased..Why?????? [message #448498 is a reply to message #448495] Tue, 23 March 2010 14:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the column is of varchar2 then data in that column should have variable length size according to string size but there still its showing size as 25.

Variable length does not mean trimming spaces.
Spaces are kept in the data when you convert from char to varchar2.

Regards
Michel
Re: Number datatype size cant decreased..Why?????? [message #448905 is a reply to message #448498] Thu, 25 March 2010 11:25 Go to previous messageGo to next message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Hi Michel,

You mean to say variable length doesnt have any trailing spaces but char have trailing spaces thats why it showed extra 6 bytes.

Cheers,
Amit

[Updated on: Thu, 25 March 2010 11:26]

Report message to a moderator

Re: Number datatype size cant decreased..Why?????? [message #448909 is a reply to message #448238] Thu, 25 March 2010 11:37 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
No.

variable length means that it doesn't add trailing spaces automatically.

SQL> create table trailing_spaces (a varchar2(10), b char(10));

Table created.

SQL> insert into trailing_spaces (a,b) values ('A', 'B');

1 row created.

SQL> insert into trailing_spaces (a,b) values ('A ', 'B ');

1 row created.

SQL> insert into trailing_spaces (a,b) values ('A    ', 'B    ');

1 row created.

SQL> commit;

Commit complete.

SQL> select '$'||a||'$' a, '$'||b||'$' b, length(a), length(b) from trailing_spaces;

A            B             LENGTH(A)  LENGTH(B)
------------ ------------ ---------- ----------
$A$          $B         $          1         10
$A $         $B         $          2         10
$A    $      $B         $          5         10

SQL> 


If you give a varchar data with trailing spaces it will store them.
Re: Number datatype size cant decreased..Why?????? [message #448910 is a reply to message #448905] Thu, 25 March 2010 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
amit.sonar wrote on Thu, 25 March 2010 17:25
Hi Michel,

You mean to say variable length doesnt have any trailing spaces but char have trailing spaces thats why it showed extra 6 bytes.

Cheers,
Amit

It's quite simple: VARCHAR2 keeps the data as you give them, CHAR pads them up to their defined length.

Regards
Michel

Re: Number datatype size cant decreased..Why?????? [message #448915 is a reply to message #448238] Thu, 25 March 2010 13:03 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
You don't need the table empty to decrease precision, simply the column. do the following
alter table my_table add column col1_temp number(10,3);

update my_table
set col1_temp = col1,
col1 = null;

alter table my_table modify column col1 col1 number(10,3);

update my_table
set col1 = col1_temp;


alter table my_table drop column col1_temp;





CM: added code tags.

[Updated on: Thu, 25 March 2010 13:07] by Moderator

Report message to a moderator

Re: Number datatype size cant decreased..Why?????? [message #448918 is a reply to message #448915] Thu, 25 March 2010 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It should be better to post a complete and working test case.
SQL> create table my_table (col1 number(20,3));

Table created.

SQL> insert into my_table values(1000.999);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table my_table add column col1_temp number(10,3);
alter table my_table add column col1_temp number(10,3)
                         *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> alter table my_table add col1_temp number(10,3);

Table altered.

SQL> update my_table
  2  set col1_temp = col1,
  3  col1 = null;

1 row updated.

SQL> alter table my_table modify column col1 col1 number(10,3);
alter table my_table modify column col1 col1 number(10,3)
                                        *
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter table my_table modify col1 number(10,3);

Table altered.

SQL> update my_table
  2  set col1 = col1_temp;

1 row updated.

SQL> alter table my_table drop column col1_temp;

Table altered.

It should be better in this case to drop col1 and rename col1_temp to col1 to avoid the second update.
And I'm not sure it is not better to recreate the table or even better to use rdbms_redefinition to keep it online.

Regards
Michel
Re: Number datatype size cant decreased..Why?????? [message #448920 is a reply to message #448238] Thu, 25 March 2010 14:03 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
sorry, I typed it free hand. However I showed the method that I did because a lot of people have fits if you change the column order of the table. They don't always specify the column names in an insert. Bad programming, yes, but it happens.
Re: Number datatype size cant decreased..Why?????? [message #448921 is a reply to message #448920] Thu, 25 March 2010 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Right! Often forgot how many work.

Regards
Michel
Re: Number datatype size cant decreased..Why?????? [message #449149 is a reply to message #448921] Sat, 27 March 2010 07:28 Go to previous message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Hi all,

Thanks a lot guys i think it pretty much cleared my doubt. Thanks a lot.

Cheers,
Amit
Previous Topic: check availability of datafile for ETL process
Next Topic: subquery
Goto Forum:
  


Current Time: Thu Sep 29 05:36:53 CDT 2016

Total time taken to generate the page: 0.07626 seconds