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 139 other followers

%d bloggers like this: