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

  • Date : January 21, 2011
  • Categories : SQL

One response

21 01 2011
Martin Berger (15:09:10) :

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.

Reply

Leave a reply to Martin Berger Cancel reply