Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Having a column FIRSTNAME NVARCHAR2(30) converted from VARCHAR2(30) with

alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- worked!

I tried to restore the original. However

alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- fails!

gives an Ora-01439 error, that the values must be empty.

First introducing a new column FIRSTNAME2 VARCHAR2(30), copying and renaming did not succeed, maybe due to failing Oracle experience?

share|improve this question
    
Can you show us what you tried when you introduced a new column, copied then renamed? –  Colin 't Hart Nov 28 '13 at 12:31
    
@Colin'tHart sorry for not answering - I was in a meeting, and the question is answered now. Basically some internet examples were erroneous. –  Joop Eggen Nov 28 '13 at 14:05
add comment

1 Answer

up vote 2 down vote accepted

Please try this, its working for me. Hope it helps you.

CREATE TABLE TEST_1
(
FIRSTNAME VARCHAR2(30)
);

INSERT INTO TEST_1(FIRSTNAME)
VALUES('NARENDRA');

COMMIT;

ALTER TABLE TEST_1
MODIFY FIRSTNAME  NVARCHAR2(30);

--Gives Error while modifying
/*ALTER TABLE TEST_1  MODIFY FIRSTNAME  VARCHAR2(30);8*/

ALTER TABLE TEST_1
ADD (FIRSTNAME2 VARCHAR2(30)
);


UPDATE TEST_1
SET FIRSTNAME2= substr(FIRSTNAME,1,30)

COMMIT; 

ALTER TABLE TEST_1
DROP COLUMN FIRSTNAME;  

ALTER TABLE TEST_1
RENAME COLUMN FIRSTNAME2 TO FIRSTNAME;
share|improve this answer
    
while coping NVARCHAR2 TO VARCHAR2 oracle requireS a explicit conversion... so we can use to_char too. UPDATE TEST_1 SET FIRSTNAME2= TO_CHAR(FIRSTNAME) –  Narendra Nov 28 '13 at 13:22
    
Thanks it worked and I learnt where I went wrong –  Joop Eggen Nov 28 '13 at 13:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.