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

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):

Possibilities:

• 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