Reputation: 8850
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.
Upvotes: 0
Views: 281
Reputation: 43553
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:
Upvotes: 2
Reputation: 132750
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.
Upvotes: 3
Reputation: 55467
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 CLOB
s 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.
Upvotes: 11