[ACCEPTED]-CLOB vs. VARCHAR2 and are there other alternatives?-dotconnect

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

This really seems like a problem 21 with the DevArt tool, or perhaps your understanding 20 of how to use it (no offence). There ought 19 to be some way for you to specify the datatype 18 of an entity's attribute and/or a way of 17 mapping those specifications to Oracle's 16 physical datatypes. I apologise if this 15 seems a little vague, I'm not familar with 14 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 13 can fix it by using DDL to alter the table 12 structure. Because the schema has many 11 such columns it is worthwhile automating 10 the process. This function drops the existing 9 column and recreates it as a VARCHAR2. It 8 offers the option to migrate data in the 7 CLOB column to the VARCHAR2 column; you 6 probably don't need this, but it's there 5 for completeness. (This is not production 4 quality code - it needs error handling, managing 3 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 2 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 1 be automated or scripted in the usual ways.

Using a CLOB for something like a Gender column 10 would, at a minimum, be extremely unusual. If 9 the DDL this tool generates specifies that 8 the LOB data should be stored inline rather 7 than out of line, I wouldn't expect to be 6 any horrible performance issues. But you 5 probably will create problems for other 4 tools accessing the database that don't 3 handle LOBs particularly well.

There is no 2 equivalent in Oracle to Tinytext in MySQL. A CLOB 1 is a CLOB.

A simpler solution is to go to Model Explorer 8 -> Model.Store -> Tables/Views, find the 7 necessary column and change the type of 6 this field to VARCHAR2.
Then run the Update 5 Database from Model wizard to persist the 4 changes to the database.
Don't forget to 3 set the MaxLength facet (however, the problem 2 with it is already fixed in the upcoming 1 Beta build).