PL/SQL: CLOB data type

Posted on 2009-02-17
Last Modified: 2013-12-19
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?
Question by:quasar_ee
    1 Comment
    LVL 73

    Accepted Solution

    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.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now