Query is Returning ORA-06502: Character String Buffer Too Small, Any Help for the OP?

21 01 2011

January 21, 2011

I found an interesting SQL statement on the OTN forums today.  When executing the SQL statement Oracle Database returns the following error:

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 12
ORA-00920: invalid relational operator
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
Error at Line: 30 Column: 4 

How would you help the original poster with this particular SQL statement (see the OTN forum thread for the SQL statement and my reformatted version of the SQL statement)?

My thoughts about the SQL statement (as posted in that thread):


  • Combining ANSI outer joins with Oracle specific outer joins in the same SQL statement
  • Combining ANSI joins with scalar subqueries (SELECTs listed in column positions)
  • Grouping on a.question_type, which could be up to 12,000 bytes long
  • Including SELECT statements in GROUP BY clauses