Home » SQL & PL/SQL » SQL & PL/SQL » Changing Fieldtypes
icon11.gif  Changing Fieldtypes [message #199754] Thu, 26 October 2006 02:19 Go to next message
Buzzer
Messages: 28
Registered: May 2006
Junior Member
Hello NG!

I use an Oracle 9i and a 10g database. Every textfield I declared as a NVARCHAR. Now I had some problems using that (for example at comparisions using the WHERE-clause). So I decided to navigate from NVARCHAR to VARCHAR. Now I didn't find any SQL-syntax since yet.

Does anyone have an idea how I could solute this problem?

Best Regards
Re: Changing Fieldtypes [message #199761 is a reply to message #199754] Thu, 26 October 2006 02:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Bummer! This comes from memory, but I *think* the column must be empty before the datatype can be changed.

The change itself can be done with:
ALTER TABLE yourtable MODIFY nvarchar_column VARCHAR2(nn);


MHE
Re: Changing Fieldtypes [message #199762 is a reply to message #199754] Thu, 26 October 2006 02:43 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> create table ttt ( name nvarchar2(20));

Table created.

SQL> alter table ttt modify ( name varchar2(20));

Table altered.
Re: Changing Fieldtypes [message #199765 is a reply to message #199761] Thu, 26 October 2006 02:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Maaher is right, it has to be empty.

But interestingly, I was able to change a non-empty column from VARCHAR2 to NVARCHAR2, but not back again.

Ross Leishman
Re: Changing Fieldtypes [message #199767 is a reply to message #199765] Thu, 26 October 2006 02:55 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

But one possible way is below.
SQL> create table one_nv ( name nvarchar2(20));

Table created.

SQL> insert into one_nv values ('dummy');

1 row created.

SQL> insert into one_nv values ('dummy_one');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table one_nv modify (name varchar2(20));
alter table one_nv modify (name varchar2(20))
                           *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype


SQL> create table two_vv ( name varchar2(20));

Table created.

SQL> insert into two_vv (name) select name from one_nv;

2 rows created.

SQL>
Re: Changing Fieldtypes [message #199778 is a reply to message #199754] Thu, 26 October 2006 03:27 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

or..

CREATE TABLE table_var2 (c1)AS SELECT CAST(c1 AS VARCHAR2(100)) FROM table_nvar2;
Re: Changing Fieldtypes [message #199782 is a reply to message #199754] Thu, 26 October 2006 03:37 Go to previous message
Buzzer
Messages: 28
Registered: May 2006
Junior Member
Ok everyone thank you very much!

Very Best Regards
Previous Topic: Query challenge - complex logic
Next Topic: Query output Exist / not exist
Goto Forum:
  


Current Time: Wed Dec 07 18:13:17 CST 2016

Total time taken to generate the page: 0.10153 seconds