How to indicate end of clob from pl/sql?
bcla...@gmail.com
procedure using DBMS_LOB.CREATETEMPORARY and then I am passing the
clob into a Java stored procedure which is supposed to read all the
data from that clob ( doing stuff with it ) and then return when there
is no more data to read.
I get the oracle.sql.CLOB's InputStream with getAsciiInputStream and
then use read to read from it.
I am able to read all the data from the clob, but my procedure then
blocks waiting for eof ( or end of clob since there iis no file
involved ) that it never recieves.
Is there any way, from PL/SQL to tell oracle that the clob is finished
being written so that when it's passed and read from, eof will happen
when the end is reached?
Here's my PL/SQL code:
DECLARE
v_what VARCHAR2(1000) := 'This is a line of text, there are many
like it, but this one is mine
';
v_in CLOB := NULL;
v_out CLOB := NULL;
v_err CLOB := NULL;
BEGIN
DBMS_LOB.CREATETEMPORARY( v_in, TRUE, DBMS_LOB.CALL );
DBMS_LOB.OPEN ( v_in, DBMS_LOB.lob_readwrite );
DBMS_LOB.WRITEAPPEND( v_in, LENGTH( v_what ), v_what );
DBMS_LOB.CLOSE ( v_in );
my_java_stored_proc( v_in ); -- I never get to the next line
DBMS_LOB.FREETEMPORARY( v_in );
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_LOB.FREETEMPORARY( v_in );
RAISE;
END;
END;
My java code looks basically like this:
// This code is from the innards of a function
// data is the oracle.sql.CLOB that is passed in as a parameter
InputStream is = data.getAsciiStream();
InputStreamReader isr = new InputStreamReader( is, cs )
BufferedReader br = new BufferedReader( isr );
int charsRead = 0;
int offset = 0;
char[] buf = new char[bufSize];
while ( ( charsRead = br.read( buf, offset, bufSize ) ) >= 0 ) {
// do something
}
// The something happens, but then the function never returns.
// It blocks waiting for an 'eof' that never happens.
DA Morgan
Why don't you determine the size using DBMS_LOB?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Mark D Powell
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
I agree. Grab the length of the CLOB and use that to control
processing. It doesn't make sense to try to process the CLOB any
other way when you need to process to the end.
IMHO -- Mark D Powell --
bcla...@gmail.com
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.
Mark D Powell
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 --
bcla...@gmail.com
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.
sybr...@hccnet.nl
>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.
Actually it wouldn't. People who don't read manuals, usually also don'
t search google, but just ask the same old boring question again.
--
Sybrand Bakker
Senior Oracle DBA
gazzag
[snip]
> 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.
>
To echo Sybrand's sentiment, have you checked to see if such a post
_already_ exists in the archives?
HTH
-g
bcla...@gmail.com
Yes. I have, though I may have missed some obvious ( to some ) search
parameters. I didn't find it. Doesn't mean it's not there.
Mark D Powell
>
> - 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.
joel garry
Personally, I think my competency as a programmer peaked at 5 years,
and has been beaten down since. But then again, competency of self-
evaluation may have its own issues.
There are certainly different levels of competency required for
different types of programming. Nuclear power plant control systems
v. apex web reports, say. I heard someone from the NTSB yesterday
morning on the radio talking about how Amtrak was told years ago they
should have an automatic computer override when train engineers goof
up and miss train signals. My first thought was "I hope they have a
manual override for that, and it ignores the issues that have been
gone over repeatedly in comp.risks." (For those who woudn't know, 25
people killed on Friday afternoon Metrolink train, rumor has it the
engineer was texting some train fanboys at the time [and the
spokesperson who said that has resigned]. My train's schedule is
still messed up by it, amtrak web site can't handle it, I can't help
but wonder if I've seen some analytics posts in the past from the
people working on that site).
There have been many attempts at making things simpler for particular
programming functions, 4GL's and various GUI things, some work well
for what they are designed for. I think the CLOB stuff just hasn't
been around long enough to simplify, and the java crowd thinks they
are great programmers. Some of them are, some of them are kooks, some
web sites are just atrocious because of bad java programming.
jg
--
@home.com is bogus.
“Obviously I would change the title.” - David Lereah, the former chief
economist of the National Association of Realtors and author of “Why
the Real Estate Boom Will Not Bust – And How You Can Profit From It”
published 2006