This week's giveaway is in the Spring forum.
We're giving away four 3 month subscriptions to Marco Behler Videos and have Marco Behler on-line!
See this thread for details.
Win a 3 month subscription to Marco Behler Videos this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL statement with LOB  RSS feed

 
Tommy Griffith
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello. If oyu have a minute, I was given a SQL by the Oracle group which has worked for a couple of years. It took the first 4000 characters of a LOB field. However, today I encountered an issue where it returned ORA-06502 character string buffer too small.

Further testing revealed that the value in the LOB for this record is apparently <4000 characters so it's throwing the ORA error.

So, I was trying a CASE WHEN  condition in my SQL to try to resolve it, if length <4000, take the whole thing (this is the ELSE).

However, i've been around and around and I keep getting ORA 00905 missing keyword. Does anybody see anything?


rs=stmt.executeQuery("SELECT CASE WHEN DBMS_LOB.getlength > 4000 THEN DBMS_LOB.substr (n.note,4000,1) note_abbreviated ELSE DBMS_LOB.substr (n.note,n.note.getlength,1) note_abbreviated END FROM notes n WHERE note_key = 345")


Thank you so much.
 
Knute Snortum
Sheriff
Posts: 3837
91
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know a lot about Oracle SQL, but I suspect that n.note.getlength is the problem.  First, can you simplify your SQL statement to this?

If not, I would try

...though why that would be needed, I don't know.
 
Tommy Griffith
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much, Knute. I found out n.note CLOB has a 4000 length on it, so I think any condition I put in is redundant.

So i am back to this...

rs=stmt.executeQuery("SELECT DBMS_LOB.substr(n.note,4000,1) note_abbreviated FROM notes n WHERE note_key = 345")

It looks straight-forward...but DBMS_LOB.substr(n.note,4000,1) throws...


ORA 06502 - character string buffer too small


...for longer character lengths (presumably at or near the 4000 characters n.note length).


If I lower the 4000 to like 3000 (...DBMS_LOB.substr(n.note,3000,1)...) for the records that are erroring as above, the error goes away and it truncates at 3000 as expected.

It seems as though anything within a certain range of 4000 or close to it, the field length, throws ORA 06502 and I don't know why...


 
Dave Tolls
Ranch Hand
Posts: 2724
30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What character set are you using?

If I remember. substr returns a maximum of 4000 bytes of characters (32000 if in PL/SQL), but that's not necessarily 4000 characters.

If you look at the ones causing the issue I wouldn't be surprised if some of the characters in there were multi-byte ones.
 
Tommy Griffith
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I experimented on one long one I knew about and the following was occuring...

DBMS_LOB.substr(n.note,3769,1)...error getting thrown

DBMS_LOB.substr(n.note,3768,1)....returns string (returns truncated string, presumably at 3768, unsure oif bytes of characters or characters).

But it seems the DBMS_LOB.substr(n.note,4000,1) throws the error for all n.note over a certain length or size.



 
Knute Snortum
Sheriff
Posts: 3837
91
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm a little confused by some of your statements.  I'm assuming that you want note_abbreviated to be 4000 characters or less.  I'm also assuming that DBMS_LOB.substr(n.note, 4000, 1) will fail if n.note 's length is less than 4000.

But then you say that n.note has a length of 4000.  So why are you using DBMS_LOB.substr at all?

I guess I'm saying that I need to know what you are try to accomplish.
 
Tommy Griffith
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi. yeah, i apologize, it went out of hand as i was troubleshooting this.

1. original SQL statement has this...

DBMS_LOB.substr(n.note, 4000, 1)



2. n.note is Oracle CLOB.



3. For some records returned, the SQL threw this...

ORA 06502 - character string buffer too small



4. I removed the substr stuff and changed the SQL to simply return n.note.



5. This returns the entire CLOB string sans error.



6. For the records formerly returning the error with substr, I noticed the returned CLOB text is much longer than those which don't throw the error.



7. So via trial and error with substr to nail down the fail point, I found the following...

DBMS_LOB.substr(n.note,3769,1)...ORA 06502 - character string buffer too small

DBMS_LOB.substr(n.note,3768,1)....returns string (returns truncated string, presumably at 3768, unsure oif bytes of characters or characters).



8. To see if a character is tripping it or it's a size thing, I shift the start of substr to 10, or 20, it doesn't matter. The 3768 seems to be the max offset threshold.



9. When I shift the start, the returned text simply shifts down that many. The cutoffs are in the middle of words. But i can never exceed 3768 offest in substr.



10. To me, this looks like a substr size limitation, but i can't find a thing about it. not on 3768.



11. On another note, when I did #4 above, the returned strings from the n.note CLOB (with supposedly 4000 limit) look to be way over 4000 characters. i haven't done something to count but it's like 5x the text returned as when i apply DBMS_LOB.substr(n.note,3768,1).


So, that's where I'm at. It's working with jsut the n.note but I am wondering if substr was in there as a failsafe or for printing purposes and am trying to determine why it's acting like this. Thank you so much for reading, I hope this synopsis of activity makes some sense :-)


 
Knute Snortum
Sheriff
Posts: 3837
91
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think 3768 is just the length of your shortest note.  Possible?

If you still want to use the substr function, I would do something like the query I wrote on 6/12.  Have you tried it?  Does it work?
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!