ORA-00932: inconsistent datatypes: expected NUMBER got LONG

[gxangel]

Hi,

I'm trying to change one of the column in my table from LONG datatype to VARCHAR2 datatype.

I intend to create an new column using the SQL below:

alter table id_table add content_new varchar2(4000)

Followed by copying the content column using:

update id_table set content_new=content

However, i got the following error.

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Is there any way i can work around this? Thanks in advance!

[malay_biswal]

For character data type you need to use ''.
Do it like this.
update id_table set content_new='content'

[gxangel]

Sorry, i think i didn't make myself clear enough.

I mean to use :

update id_table set content_new=content

to copy the data in column CONTENT to column CONTENT_NEW, not to update the column CONTENT_NEW with the word 'content'.

I can't find the work around to copy a column of LONG type data...

[malay_biswal]

Then you need to write one function which will convert Long to varchar2 and using that function you would be able to achieve.

[DSupp2008]

You may need to use CLOB instead of VARCHAR2 to convert LONG data type values. Some of your existing values may be too large to be contained in a VARCHAR2 in Oracle database.

DSupp2008