 Column Altered Backing Out [message #293901] |
Tue, 15 January 2008 10:05  |
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
|
Member |
|
|
Hi,
Small issue
I have altered on column ALTER TABLE POLICY_LOCATIONS
MODIFY(PL_LATITUDE NUMBER(12,8),
PL_LONGITUDE NUMBER(12,8));
I need to back it out to as it was beforePL_LATITUDE NUMBER(8,4),PL_LONGITUDE NUMBER(8,4)
i.e. my alter script but i wont work and will throw errorALTER TABLE POLICY_LOCATIONS
MODIFY(PL_LATITUDE NUMBER(8,4),
PL_LONGITUDE NUMBER(8,4));
But i can't empty the columns as its in production
one option is to take backup column, empty the column, alter and again update that column, but i'm trying to find out if there is another possibility..
Please help with a suggestion, if there is another option
Thanks in advance
Atul
[EDITED by LF: added [code] tags]
[Updated on: Tue, 15 January 2008 15:26] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Column Altered Backing Out [message #294409 is a reply to message #293901] |
Thu, 17 January 2008 08:40  |
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
|
Senior Member |
|
|
well Atul,
I doubt there is any other way.
But, you can try you luck by
Step:1finding the maximum length of the pl_latitude,PL_LONGITUDE columns.
If it's length (here we are finding the maximum length )less than or equal to the (8,4) then you can modify policy_location,PL_LONGITUDE columns of policy_location table directly,
i mean without adding a new column and fill it with your previous values, drop the old one and rename the one.
Below is the sample query to find the maximum size of a ename column in emp:
select max(length(ename)) from emp ;
Step 2: If first step is failed then there is no option other than that of what Michel said.
well try your luck.
But, is the Script released accidently to the Production or intensionaly ?
[Updated on: Thu, 17 January 2008 09:53] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Column Altered Backing Out [message #294470 is a reply to message #293901] |
Thu, 17 January 2008 13:10 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
1) alter table POLICY_LOCATIONS add junk1 number(8,4);
2) alter table POLICY_LOCATIONS add junk2 number(8,4);
3) update POLICY_LOCATIONS set junk1 = pl_latitude,junk2 = PL_LONGITUDE;
4) update POLICY_LOCATIONS set pl_latitude = null,PL_LONGITUDE=null;
5) alter table POLICY_LOCATIONS modify(pl_latitude number(8,4),
PL_LONGITUDE number(8,4));
6) update POLICY_LOCATIONS pl_latitude = junk1, PL_LONGITUDE= junk2;
7) alter table POLICY_LOCATIONS drop column junk2;
 alter table POLICY_LOCATIONS drop column junk1;
This will change the columns and maintain the structure so that any inserts without column lists will still work.
[Updated on: Thu, 17 January 2008 13:11] Report message to a moderator |
|
|
|
| Re: Column Altered Backing Out [message #294627 is a reply to message #293901] |
Fri, 18 January 2008 04:13 |
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
|
Senior Member |
|
|
Michel,
we can decrease the length of a column if the column contains the values less than or equal to the New updatable value.
Here is the demonstration
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER(4)
EMPNAME VARCHAR2(10)
SUPID NUMBER(4)
DEPTNO VARCHAR2(4)
MGR NUMBER(3)
SQL> select * from emp;
EMPID EMPNAME SUPID DEPT MGR
---------- ---------- ---------- ---- ----------
100 anu 1 10 301
1 sid 50 20 301
50 sagar 100 50 305
2 Rahul 1000 10 302
3 Rai 200 10 302
4 Raj 300 20 304
5 Ram 300 10 305
7 rao 200 10 301
6 CRA 400 10 306
7 200 10 301
10 rows selected.
SQL> select max(length(empname))max_length from emp ;
MAX_LENGTH
----------
5
SQL> alter table emp modify empname varchar2(5);
Table altered.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPID NUMBER(4)
EMPNAME VARCHAR2(5)
SUPID NUMBER(4)
DEPTNO VARCHAR2(4)
MGR NUMBER(3)
Conclusion: we can modify the column if the length of the values
is less than or equal to the New column size.
[Updated on: Fri, 18 January 2008 04:14] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Column Altered Backing Out [message #294662 is a reply to message #294657] |
Fri, 18 January 2008 06:35 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
This is that!
SQL> desc emp
Name Null? Type
---------- -------- -----------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10 CHAR)
JOB VARCHAR2(9 CHAR)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> alter table emp modify (sal number(10,2));
Table altered.
SQL> alter table emp modify (sal number(7,2));
alter table emp modify (sal number(7,2))
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
