CLOB vs. VARCHAR2 and are there other alternatives?

Question

I am using DevArt's dotConnect and Entity Developer for my application. I've created the tables using the Entity-First feature.

I notice that many of the column types are set to CLOB. I only have experience with MySQL and Microsoft SQL server, so I am not sure about using CLOB for the application. I did some reading, and found out that CLOB are for large chunk of data.

The questions are:

1. Is using CLOB for most fields, such as the user's gender (which should be a varchar (1) ) or the full name, feasible? The steps for converting a CLOB field to VARCHAR2 requires dropping the column then re-creating it, and is buggy in DevArt's Entity Explorer, so I would like avoid it if possible. Edit: I just found out that if you set a maximum length for a string field it'll automatically be a VARCHAR2.

2. Are there any equivalents for TINYTEXT in Oracle?

Answer 1

It is a very bad idea to use a CLOB datatype for a column which ought to be VARCHAR2(1). Apart from the overheads (which are actually minimal, as Oracle will treat inline CLOBs of < 4000 characters as VARCHAR2) we should always strive to use the most accurate representation of our data in the schema: it's just good practice.

This really seems like a problem with the DevArt tool, or perhaps your understanding of how to use it (no offence). There ought to be some way for you to specify the datatype of an entity's attribute and/or a way of mapping those specifications to Oracle's physical datatypes. I apologise if this seems a little vague, I'm not familar with the product.

---

So, this is the basic problem:

SQL> desc t69
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 COL1                                               CLOB

SQL>
SQL> alter table t69 modify col1 varchar2(1)
  2  /
alter table t69 modify col1 varchar2(1)
                       *
ERROR at line 1:
ORA-22859: invalid modification of columns


SQL>

We can fix it by using DDL to alter the table structure. Because the schema has many such columns it is worthwhile automating the process. This function drops the existing column and recreates it as a VARCHAR2. It offers the option to migrate data in the CLOB column to the VARCHAR2 column; you probably don't need this, but it's there for completeness. (This is not production quality code - it needs error handling, managing NOT NULL constraints, etc)

create or replace procedure clob2vc
  ( ptab in user_tables.table_name%type 
    , pcol in user_tab_columns.column_name%type
    , pcol_size in number
    , migrate_data in boolean := true )
is
begin
    if migrate_data
    then
        execute immediate 'alter table '||ptab
                    ||' add tmp_col varchar2('|| pcol_size|| ')';
        execute immediate             
                    'update '||ptab
                    ||' set tmp_col = substr('||pcol||',1,'||pcol_size||')';
    end if;
    execute immediate 'alter table '||ptab
                ||' drop column '|| pcol;

    if migrate_data
    then
        execute immediate 'alter table '||ptab
                    ||' rename column tmp_col to '|| pcol;
    else
        execute immediate 'alter table '||ptab
                    ||' add '||pcol||' varchar2('|| pcol_size|| ')';
    end if;
end;
/

So, let's change that column...

SQL> exec clob2vc ('T69', 'COL1', 1)

PL/SQL procedure successfully completed.

SQL> desc t69
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 COL1                                               VARCHAR2(1)

SQL>

Calling this procedure can be automated or scripted in the usual ways.