Skip to Main Content
  • Questions

  • Change VARCHAR to VARCHAR2 in oracle 12c without deleting data

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prasun.

Asked: April 07, 2017 - 2:56 am UTC

Last updated: April 08, 2017 - 5:23 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi ,

How can I change the data type from VARCHAR to VARCHAR2 in 12c DB without deleting records? Please advice.

Regards,
Prasun

and Connor said...

Its's very easy...

SQL> create table t ( x varchar(50) );

Table created.

SQL> insert into t
  2  select 'String '||rownum
  3  from dual connect by level <= 1000;

1000 rows created.

SQL>
SQL> alter table t modify x varchar2(50);

Table altered.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks so much

A reader, April 07, 2017 - 6:15 pm UTC

Thanks so much Connor!!.

Is oracle internally convert VARCHAR to VARCHAR2 ?

I have created table with VARCHAR but oracle internally converted to VARCHAR2.

create table zztemp_changedattype (id number(10), col1 varchar(50),col2 varchar2(150));

desc zztemp_changedattype
-----------------------
Name Null? Type
---- ----- -------------
ID NUMBER(10)
COL1 VARCHAR2(50) ----CONVERTED VARCHAR2
COL2 VARCHAR2(150)

Is it due any parameter or it is default behavior ?

Regards,
Prasun
Connor McDonald
April 08, 2017 - 5:23 am UTC

Internally they are currently the same thing.

VARCHAR2 is always the recommended option.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.