• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1524
  • Last Modified:

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?
Thanks!
0
quasar_ee
Asked:
quasar_ee
1 Solution
 
sdstuberCommented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now