change datatype of column

Question

Post reply

change datatype of column

kavitha.shivaramaiah

SSC Veteran

Points: 220
More actions
December 31, 2008 at 4:27 am

#199553

hi,
I am trying to change data type of a column from number to varchar2.(Data base has data).
CREATE OR REPLACE PROCEDURE K_P
IS
CURSOR cur IS select user_tab_columns.table_name, user_tab_columns.column_name from user_tab_columns,user_tables where column_name in ('REC_ID','RECORD_ID') and data_type = 'NUMBER' and data_precision = 32
and user_tab_columns.table_name = user_tables.table_name;
tab VARCHAR2 (32);
BEGIN
for i IN (select table_name, constraint_name --disable first the foreign key
from user_constraints
where status = 'ENABLED')
loop
DBMS_OUTPUT.put_line ( 'R disable');
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' disable constraint ' ||i.constraint_name;
end loop i;
FOR i IN cur
LOOP
tab := '1';
tab := i.table_name || tab;
DBMS_OUTPUT.put_line ('****table name:****' || i.table_name||' ' || i.column_name);
DBMS_OUTPUT.put_line ('****table name:****' || i.table_name||' ' || tab);
DBMS_OUTPUT.put_line ( 'CREATE TABLE '
|| tab
|| ' AS (SELECT * FROM '
|| i.table_name
|| ' )'
);
EXECUTE IMMEDIATE 'CREATE TABLE '
|| tab
|| ' AS (SELECT * FROM '
|| i.table_name
|| ' )';
DBMS_OUTPUT.put_line ('DELETE FROM ' || i.table_name);
EXECUTE IMMEDIATE 'DELETE FROM ' || i.table_name;
DBMS_OUTPUT.put_line ( 'alter column');
DBMS_OUTPUT.put_line ( 'ALTER TABLE '
|| i.table_name
|| ' MODIFY ( '|| i.COLUMN_NAME ||' VARCHAR2(32))');
EXECUTE IMMEDIATE ('ALTER TABLE '
|| i.table_name
|| ' MODIFY ( ' || i.COLUMN_NAME ||' VARCHAR2(32))');
DBMS_OUTPUT.put_line ( 'INSERT INTO '
|| i.table_name
|| ' SELECT * FROM '
|| tab
);
EXECUTE IMMEDIATE 'INSERT INTO ' || i.table_name || ' SELECT * FROM '
|| tab;
DBMS_OUTPUT.put_line ('DROP TABLE ' || tab);
EXECUTE IMMEDIATE 'DROP TABLE ' || tab;
END LOOP;
for i IN (select table_name, constraint_name --disable first the foreign key
from user_constraints
where constraint_type ='P'
and status = 'DISABLED')
loop
DBMS_OUTPUT.put_line ( 'Primary');
DBMS_OUTPUT.put_line ( 'ALTER TABLE '
|| i.table_name
|| ' enable constraint '|| i.constraint_name);
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' enable constraint ' ||i.constraint_name;
end loop i;
for i IN (select table_name, constraint_name --disable first the foreign key
from user_constraints
where constraint_type ='U'
and status = 'DISABLED')
loop
DBMS_OUTPUT.put_line ( 'Unique');
DBMS_OUTPUT.put_line ( 'ALTER TABLE '
|| i.table_name
|| ' enable constraint '|| i.constraint_name);
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' enable constraint ' ||i.constraint_name;
end loop i;
for i IN (select table_name, constraint_name --disable first the foreign key
from user_constraints
where constraint_type ='R'
and status = 'DISABLED')
loop
DBMS_OUTPUT.put_line ( 'Ref');
DBMS_OUTPUT.put_line ( 'ALTER TABLE '
|| i.table_name
|| ' enable constraint '|| i.constraint_name);
EXECUTE IMMEDIATE 'alter table ' ||i.table_name|| ' enable constraint ' ||i.constraint_name;
end loop i;
FOR i IN (SELECT constraint_name, table_name
FROM user_constraints)
LOOP
DBMS_OUTPUT.put_line ( 'others');
DBMS_OUTPUT.put_line ('**********************'||i.table_name);
DBMS_OUTPUT.put_line ( 'ALTER TABLE '
|| i.table_name
|| ' enable constraint '|| i.constraint_name);
EXECUTE IMMEDIATE 'alter table '|| i.table_name || ' enable constraint '|| i.constraint_name ;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
rollback;
DBMS_OUTPUT.put_line (TO_CHAR (SQLCODE) || ' : ' || SQLERRM (SQLCODE));
rollback;
END K_P;
this procedure works fine for column which is not primary key,but for primary key column its giving error like,
-1463 : ORA-01463: cannot modify column datatype with current constraint(s)
Kindly suggest me how can i achieve it.
thanks & regards
Kavitha

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

You might want to try posting this question here - http://asktom.oracle.com/pls/asktom/f?p=100:1:2761078594096133

This site is SQL Server people, not Oracle guys.

Who knows though, I get surprised all the time....

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Here is what I'd do.

1- Add the new varchar2 column column_name_new as needed.

2- Populate varchar2 column with a simple update statement.

3- Rename old columns as column_name_old

4- Rename new column_name_new as column_name

5- Hide old column_name_old column

On time you may want to drop the hidden column.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Vijaya Kadiyala SSCrazy Eights Points: 9149 More actions · Answer 3

Hi

If you have some data in the column and want to change the data type, then it will NOT work. To change the data type of the column you shouldn't have any values.

You need to follow Paul's steps, i.e. Add on temp column with varcahr and update this column with Old Number column etc.

If the table is very large then it may take lot of time.

try doing it when there is less or no little activity on this table.

Thanks -- Vj

http://dotnetvj.blogspot.com

http://oravj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

kavitha.shivaramaiah SSC Veteran Points: 220 More actions · Answer 4

hi,

i would like to thanks for replying,

but the column has refferential constraints.how can i go ahead?

change datatype of column

Cookies on SQLServerCentral