PL/SQL: CLOB data type

Hi there,
I have varchar2 output parameter in the stored procedure. In the sproc it's changed many times by concatenating new string values. The string became longer than 4000 characters that results in the error. I replaced varchar2 declaration with CLOB. Looks like it works fine without any changes in the code required.
I am wondering if any potential problems can be created by doing this? Is it a good idea to replace varchar2 with CLOB without any code modifications?
Who is Participating?
depending on your application and clients that receive the final string it may or may not be a problem.

Within your pl/sql code, it should be fine but if you return it as function result or OUT parameter it can result in issues.

First, older clients may not even recognize CLOBs as a supported type.

If your code used to have a limited output of 4000 characters or less and other parts of the application expected a string of that size or smaller
you may have errors or truncation if they can't handle the extra size regardless of the data type.

And, clobs have restrictions in their use across database links, indexes and other features.

So, yes it "can" work and yes it "can" cause problems.  If you need a larger string than a varchar2 can handle though then a CLOB is probably your only option anyway.
To ensure your app will work, examine the "seams" that is, the places where your procedures connect and pass the CLOB around or out to another application or client.  Those will be the areas where it breaks if it will.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.