0

I have confusion between CLOB and VARCHAR2? Can CLOB be store as VARCHAR2? If I modify the column from CLOB to VARCHAR2 will it work properly? If no why?

  • A varchar2 max size is 32767. Enough? – jarlh Sep 07 '20 at 09:22
  • Yes, it is enought –  Sep 07 '20 at 09:23
  • Then I should consider altering the column data type. Be sure to check dependencies and do a proper backup before you start. – jarlh Sep 07 '20 at 09:26
  • 1
    Does this answer your question? [How to change a dataype CLOB TO VARCHAR2(sql)](https://stackoverflow.com/questions/19841947/how-to-change-a-dataype-clob-to-varchar2sql) – VBoka Sep 07 '20 at 09:42
  • be careful, varchar2 only can get up to 32k if you have 12.2 and you have executed the corresponding changes in the database dictionary. See this https://oracle-base.com/articles/12c/extended-data-types-12cR1#12cr2-update – Roberto Hernandez Sep 07 '20 at 09:46
  • 32k varchar2 is internally implemented as CLOB. By using it you combine disadvantages of both and moreover you add few more problems. – ibre5041 Sep 07 '20 at 09:52
  • 1
    The first question to ask about _any_ proposed change is "why do you want to make this change?" – EdStevens Sep 07 '20 at 12:35
  • Because I have UNION in my select and in one select it is VARCHAR and in another it is CLOB –  Sep 08 '20 at 06:01
  • If the CLOB has less than 32768 characters, then you can use `dbms_lob.substr` in your query to make the union's content uniform. – Jeff Holt Dec 20 '20 at 04:08

1 Answers1

0

You can technically modify columns datatype from clob to varchar2. But there are some conditions about data size. Maximum size is 4000 bytes or characters for VARCHAR2. But in Oracle Database 12c you can specify maximum size by setting the initialization parameter MAX_STRING_SIZE as follows;

  • If MAX_STRING_SIZE = STANDARD, then the size limits 4000 bytes for the VARCHAR2. This is the default.
  • If MAX_STRING_SIZE = EXTENDED, then the size limit is 32767 bytes for the VARCHAR2.

Also I recommend you to examine this too, there some examples about different datatypes.

Tomer Shetah
  • 8,413
  • 7
  • 27
  • 35
ecemturkay
  • 1
  • 2