Re: How to indicate end of clob from pl/sql?

From: Mark D Powell <>
Date: Tue, 16 Sep 2008 06:54:56 -0700 (PDT)
Message-ID: <>

On Sep 15, 2:32 pm, wrote:
> On Sep 15, 10:58 am, Mark D Powell <> wrote:
> > On Sep 15, 9:31 am, wrote:
> > > *snip* post
> > > Thanks to those who responded.
> > > It turns out that I was, in fact, getting eof, but that further on
> > > downstream the data was blocking waiting for an eof that *I* wasn't
> > > sending because I didn't close the stream ( though I did flush it ).
> > > Sorry.  But my debugging spidey-sense was kinda drawn to the CLOB
> > > stuff because they are 'weird' and I don't fully understand them.
> > > I really wish there were a nice simple type like postgres's TEXT type
> > > in oracle.  I've never had a need for larger than 2 gigs but I have
> > > had need of larger than 4000 characters on more than one occasion.
> > > Gees...
> > > I believe there is some behind the scenes magic whereby a clob can be
> > > written to by one thread of execution and read by another at the same
> > > time so that the reader will block until there more data has been
> > > written.  This would make a clob like a stream?   I've never had a
> > > need for this, but the fact that it is ( probably ) there, complicates
> > > the clob documentation.  Also there is something called chunks.  I
> > > have not messed with the chunksize either.
> > > Is there a limit to how much of a clob Oracle will buffer?  Say I read
> > > a whole clob and it happened to be a 3 gigger.  Does that mean the
> > > clob is stored in memory occupying 3 gigs?  If not, then what if I
> > > want to read it again?  I hope it is stored where I can get it again
> > > and not just one chunks worth or something annoyingly complicated like
> > > that.  Then *I* would be responsible for generating the data again.
> > > Sheesh!
> > > Something simple for bears-of-little-brain would be nice...
> > > And if there is a way to make such a type using clobs underneath, I
> > > would need to understand 'how it works' enough to trust 'that it
> > > works'.  For that I would need to understand clobs enough to write it
> > > myself, and then the benefit of it would be somewhat diminished.
> > > Oracle really ought to supply it documenting any performance
> > > limitations.
> > > And LONG doesn't work.  It's deprecated.
> > You post reads like you are just complaigning because you do not
> > understand how to properly deal with CLOBs.  The Oracle LOB data types
> > work fine.  To understand how the feature works read the Application
> > Developers Guide - Large Objects.  In other words there is an entire
> > manual on the subject of LOBs though only a portion of  the manual is
> > on CLOB.  You can skip over the BLOD and BFILE portions if you only
> > need to understand working with CLOBs.  But there is no substitute for
> > the knowledge contained in the manual.  It will be beneficial to you
> > to cover this material prior to designing and coding your application.
> > LOB IO bypasses the buffer cache.  The basic unit for LOB operations
> > is the chuck size.  When the actual length of the data is large
> > setting the chunk size up may be a reasonable decision.
> > HTH -- Mark D Powell --
> Yeah, that is what I was doing, complaining.  Bad form maybe if done
> impolitely,  but sometimes effective nonetheless.  I wasn't trying to
> be impolite.
> I was kinda trolling for that Clob expert out there that could
> concisely and completely distill all the important aspects of and
> gotchas surrounding LOBS into a couple of paragraphs that would be
> easier to read and grok than reams of scattered oracle reference docs
> many of which I have read but not all of which I have understood
> fully.
> That energetic expert's post would then be archived on google groups
> for eternity to assist the me and any other lob-confused coders of the
> world, as well as garnering them glory and acclaim for generations to
> come.
> ;-P
> Still, if there isn't a principle of "don't make me learn something
> complicated unless there is really no other option" in software
> design, perhaps there should be.  That's my only real issue with
> CLOBs.  Of course they do work, if you know how to use them.
> The other thing is lack of an alternative between VARCHAR2(4000) and
> CLOB.  Something with approximately the same limitations as a java
> string would be perfect..  There are alot of applications that aren't
> very performance sensitive but are time-to-code sensitive.  Premature
> optimization is the root of all evil and all that jazz.- Hide quoted text -
> - Show quoted text -

Actually I just saw a reference yesterday to an article that states that it takes 10 years to become a competent programmer. Unfortunately when something is easy there is also the liklihood that it is wrong or at least not the best approach. I just suggest you skim the manual reading the CLOB sections to ensure that the decisions you make today are going to be ones that one work for you now and two are likely to be decisions that you can live with down the road.

HTH -- Mark D Powell -- Received on Tue Sep 16 2008 - 08:54:56 CDT

Original text of this message