Home » SQL & PL/SQL » SQL & PL/SQL » Column Altered Backing Out
icon4.gif  Column Altered Backing Out [message #293901] Tue, 15 January 2008 10:05 Go to next message
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 before
PL_LATITUDE NUMBER(8,4),PL_LONGITUDE NUMBER(8,4)

i.e. my alter script but i wont work and will throw error
ALTER 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 #293902 is a reply to message #293901] Tue, 15 January 2008 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
50+ posts and you don't already know to:
- follow OraFAQ Forum Guide, especially "How to format your post?" section.
- Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Add a new column, fill it with your previous values, drop the old one and rename the one.

Regards
Michel
Re: Column Altered Backing Out [message #294409 is a reply to message #293901] Thu, 17 January 2008 08:40 Go to previous messageGo to next message
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 #294424 is a reply to message #294409] Thu, 17 January 2008 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot decrease the length even if all the values are valid for the new length.

Regards
Michel
Re: Column Altered Backing Out [message #294470 is a reply to message #293901] Thu, 17 January 2008 13:10 Go to previous messageGo to next message
Bill B
Messages: 1484
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;
Cool 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 Go to previous messageGo to next message
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 #294630 is a reply to message #294627] Fri, 18 January 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the correction.
My brain seems to turn to white sauce these days. Sad

Regards
Michel
Re: Column Altered Backing Out [message #294657 is a reply to message #293901] Fri, 18 January 2008 06:12 Go to previous messageGo to next message
atulrsingh
Messages: 55
Registered: November 2006
Location: Mumbai
Member
I think you got confused with the NUMBER & VARCHAR datatypes.

You are true in case of NUMBER datatype

Atul
Re: Column Altered Backing Out [message #294662 is a reply to message #294657] Fri, 18 January 2008 06:35 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Need Design Idea to Store 1 - 1 - n relationship
Next Topic: Delete all constraints related to a table
Goto Forum:
  


Current Time: Fri Dec 09 09:33:20 CST 2016

Total time taken to generate the page: 0.05926 seconds