Characters spilled over multiple columns in Oracle 11g?

Question

This is related to question: How to store unlimited characters in Oracle 11g?

If maximum I need is 8000 characters, can I just add 3 more varchar2 columns so that I will have 4 columns with 2000 char each to get 8000 chars. So when the first column is full, values would be spilled over to the next column and so on. Will this design have any bad side effects? Please suggest.

Answer 1

Why not just use a CLOB column instead? I read your other link, and I don't understand why your DBA's don't like these types of columns. I mean, CLOB is an important feature of Oracle just for this exact purpose. And your company paid good money for that feature when buying Oracle, so why not leverage Oracle to it's fullest capabilities instead of trying to come up with hacks to do something that the DB is not really designed to do?

Maybe instead of spending time trying to devise hacks to overcome limitations created by your DBAs, you should spend some time educating your DBA's on why CLOBs are the right feature to solve your problem.

I would never be satisfied with a bad design when the DB has the feature I need to make a good design. If the DBA's are the problem, then they need to change their viewpoint or you should go to senior level management in my opinion.

Answer 2

I agree with dcp that you should be using CLOB. But if against all sense you are forced to "roll your own" unlimited text using just VARCHAR2 columns then I would not do it by adding more and more VARCHAR2 columns to the table like this:

create table mytable
  ( id integer primary key
  , text varchar2(2000)
  , more_text varchar2(2000)
  , and_still_more_text varchar2(2000)
  );

Instead I would move the text to a separate child table like this:

create table mytable
  ( id integer primary key
  );
create table mytable_text
  ( id references mytable(id)
  , seqno integer
  , text varchar2(2000)
  , primary key (id, seqno)
  );

Then you can insert as much text as you like for each mytable row, using many rows in mytable_text.

Answer 3

To add to DCP's and Tony's excellent answers:

You ask if the approach you are proposing will have any bad side effects. Here are a few things to consider:

1. Suppose you want to perform a search of your text data for a particular string. Your approach requires repeating the search on each column containing your text - results in a convoluted and inefficient WHERE clause.
2. Every time you want to expand your text field you have to add another column, which now means you have to modify every place you coded to do (1).
3. Everyone who has to maintain this structure after you will curse your name ;-)