Re: SQLDeveloper and CLOBs

From: Kim Berg Hansen <>
Date: Fri, 25 Apr 2014 18:04:32 +0200
Message-ID: <>

Hi, Nico

A LOB datatype (CLOB or BLOB) is really just a pointer to where the data resides in the database.
When you have selected a LOB, you have really selected that pointer. Then to get the actual data in the LOB, you read chunks using that pointer.

Whether you actually need to read the chunks yourself is a matter whether the client understands the LOB datatype. SQLDeveloper (and TOAD and probably other similar clients) understands the LOB datatype and gets you the data automatically. SQL*Plus can do some as well, but not as easy as the GUI clients where you can open the LOB content in a seperate window and save the LOB content to a file - but that is just the client being intelligent and handling things for you.

If you write code in PL/SQL to manipulate the LOB data there may be times where you need to do such code manually using DBMS_LOB calls and fetch chunks of the CLOB into VARCHAR2 variables one chunk at a time. But in newer versions of Oracle there tends to be fewer and fewer cases where that is needed and more and more cases where LOBs can be handled natively.

Similarly if you write code in for example C#, you will use whatever that language offers in ways of handling the LOB datatypes. Typically such languages will have the LOB as an object type with member methods to read the data either in one go or in chunks.

So depending on your environment you do not necessarily have to worry about fetching a LOB in chunks yourself - your client often handles it for you.


Kim Berg Hansen

On Fri, Apr 25, 2014 at 5:19 PM, nico torres <> wrote:

> Hi, I am new to LOB datatypes and I have a question about it:
> I've read that in order to get clob data from the database, you need to
> get it done in groups of 4000 characters, because of VARCHAR2 limitations.
> Now, to my surprise, I just made a select clobcolumn from mytable, without
> anything else, in Sqldeveloper, and it got the whole file
> Could anyone explain this to me?
> Thanks!

Received on Fri Apr 25 2014 - 18:04:32 CEST

Original text of this message