Need to Increase varchar2 limit beyond 4000

Abhishekghosh

Hi ,

 

We have a client request that we need to check if user has pushed more than 5000 characters in a text field well that is ok and can be done.

 

Now the issue is that when we insert this data to  the table , we are not able to do so as , the coloum is specified as  varchar2(3000) , but as we want to insert 5000 that is also not possible as varchar2 has a limit of 4000.

 

In the current scenarios lots of jsp and code is using this table to get  values and even if we change the datatype to may be long to clob , as the coloum is not empty we are not able to change the datatype ,

 

Iis there any way we can increase the varchar2 size with out effecting the data in the table and breaking the existing code which is using  this coloum ?

 

Any suggestion will be good ...

 

 

Rick Poulin

This depends on your particular database vendor, which you failed to specify.. but generally varchars have a 4000 char ceiling. Some databases may have tools to help you convert the data (create the new CLOB column, copy the data into it, then delete the old column). The other solution is to split up your content into 4000char chunks at deployment time, but this requires extra preprocessing (which is non-trivial if you don't already have a hook for that in place) and your schema still needs to change to add a 'sort order' column for multiple values belonging to whatever parent. In both cases, your runtime code will need to change accordingly.

 

Sorry, but there's no painless answer here.