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

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

Actions

Information

One response

21 01 2011
Martin Berger

I’d start with the ORA-00920 – so have a look at the WHERE clause.
the one ANSI-Join ‘and ito on scores.ips=ito.ips’ is somewhat confusing within all the other Oracle-Joins.
But there seems no PL/SQL invoved, so I have to look at something else.
The next candidate for me is a.question_type = ‘-ITO’. within this question_type the function bi_recurse is hiding. So I’d ask to check this function. Especially ‘Line: 30 Column: 4′ would be of some interest.
I don’t see a real reason why to suspect those subqueries. If they cause troubles I’d not expect any PL/SQL error.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 142 other followers

%d bloggers like this: