troubleshooting Question

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

Avatar of YBSolutions
YBSolutionsFlag for Argentina asked on
Oracle Database
9 Comments2 Solutions4071 ViewsLast Modified:
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")
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros