Link to home
Start Free TrialLog in
Avatar of YBSolutions
YBSolutionsFlag for Argentina

asked on

I need to change the datatype from char (1) to varchar2(1) of column NNNNN in table ABC

I need to change the datatype from char (1) to varchar2(1) of column NNNNN in table ABC
         This table has about 2000 records and primary key (which is being refered as FK in another table).
         May I know what is the best method to achieve it without loosing data and constraints?
        

Note: I have tried below methods.
       
      
SQL> ALTER TABLE ABC MODIFY REGN_DEFAULT_IND VARCHAR2(1);
ALTER TABLE ABC MODIFY REGN_DEFAULT_IND VARCHAR2(1)
                                *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

==========



SQL> create table temp_ABC as select * from ABC ;

Table created.

SQL> select count(*) from temp_ABC;

  COUNT(*)
----------
      1529

SQL> select count(*) from ABC ;

  COUNT(*)
----------
      1529

SQL> truncate table ABC ;
truncate table ABC
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> DELETE FROM ABC WHERE 1 =2  ;

0 rows deleted.

SQL> DELETE FROM ABC WHERE ROWNUM<1530 ;
DELETE FROM ABC WHERE ROWNUM<1530
*
ERROR at line 1:
ORA-02292: integrity constraint (USER.REGNOPERWT_REGN_FK1) violated - child record found


SQL> create table temp_ABC2 as select * from ABC WHERE 1 > 2 ;

Table created.

SQL> SELECT COUNT(*) FROM temp_ABC2 ;

  COUNT(*)
----------
         0


SQL> INSERT INTO temp_ABC2 (REGN_DEFAULT_IND) SELECT REGN_DEFAULT_IND FROM ABC ;
INSERT INTO temp_ABC2 (REGN_DEFAULT_IND) SELECT REGN_DEFAULT_IND FROM ABC
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("USER"."TEMP_ABC2"."REGN_ID")


SQL>
SQL>
SQL> ALTER TABLE ABC MODIFY REGN_DEFAULT_IND VARCHAR2(1);
ALTER TABLE ABC MODIFY REGN_DEFAULT_IND VARCHAR2(1)
                                *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype


SQL> alter table temp_ABC drop column REGN_DEFAULT_IND ;

Table altered.

SQL> alter table temp_ABC add column REGN_DEFAULT_IND VARCHAR2(1) not null ;
alter table temp_ABC add column REGN_DEFAULT_IND VARCHAR2(1) not null
                                  *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> alter table temp_ABC add  REGN_DEFAULT_IND VARCHAR2(1) not null ;
alter table temp_ABC add  REGN_DEFAULT_IND VARCHAR2(1) not null
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column


SQL> alter table temp_ABC add column REGN_DEFAULT_IND VARCHAR2(1) ;
alter table temp_ABC add column REGN_DEFAULT_IND VARCHAR2(1)
                                  *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> alter table temp_ABC add REGN_DEFAULT_IND VARCHAR2(1) ;

Table altered.

SQL> alter table temp_ABC modify REGN_DEFAULT_IND not null ;
alter table temp_ABC modify REGN_DEFAULT_IND not null
*
ERROR at line 1:
ORA-02296: cannot enable (USER.) - null values found


SQL> insert into temp_ABC (REGN_DEFAULT_IND) select REGN_DEFAULT_IND from ABC ;
insert into temp_ABC (REGN_DEFAULT_IND) select REGN_DEFAULT_IND from ABC
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("USER"."TEMP_ABC"."REGN_ID")
Avatar of YBSolutions
YBSolutions
Flag of Argentina image

ASKER

Column NNNNN in table ABC is not null type and having 'Y' and 'N' values.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
- opss sorry. i didnt see the earlier expert response. however i got few extra steps in checking that the data is there correctly.
I had to make little change and below is the final action plan, which served the purpose.
========

ALTER TABLE ABC ADD REGN_DEFAULT_IND_b varchar2(1) ;

 update ABC set REGN_DEFAULT_IND_b = REGN_DEFAULT_IND ;


-- SELECT REGN_DEFAULT_IND, REGN_DEFAULT_IND_B from ABC ;

 alter table ABC drop column REGN_DEFAULT_IND ;

 alter table ABC add REGN_DEFAULT_IND VARCHAR2(1);

 update ABC set REGN_DEFAULT_IND = REGN_DEFAULT_IND_b ;

 alter table ABC modify REGN_DEFAULT_IND not null ;

alter table ABC drop column REGN_DEFAULT_IND_b ;
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Don't forget to close the question out if it has been answered.
I'm in agreement with slightvw on the "Why?", as they are exactly the same data-type under the covers.  AskTom has a good article on the subject:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

If you must alter it to keep things "clean", you can always use DBMS_REDEFINITION which you can do online with a very momentary lock that your users will never notice.

I've written an article on how to do it:

"Zero Downtime Technique to Rebuild Oracle Tables"
It was admin decision to change it.

Thanks for your help.
Do you need further assistance with this question?  If not, please close it out.