Re: Query occasionally returning empty results in 8i

BoukeB wrote:
> We have a simple query (SELECT * FROM..,no WHERE clause) in 8i that
> sometimes returns empty results and ORA-01403: no data found. We have
> clues that it might be caused by settings in the init.ora, particularly
> for the sort area or the optimizer. The result of the query should be
> some 800 rows.
>
> We have found a workaround by chance and that is to add a blank to the
> query at the end. The results are never empty then!

Are you using sqlplus or something else? If you are using jdbc, see bug 1841512.

Do you mean adding a comma then a blank to a select clause, or what? This all sounds familiar somehow, but I can't put my finger on it...

> open_cursors = 500 open_cursors = 100000

Whoa, where'd you come up with _that_?

> optimizer_mode = CHOOSE

If any table has statistics, the CBO will be used. Otherwise, RBO will be used. Have you checked the statistics?

See what explain plan has to say, while you are at it. Try using a PL exception handler. As someone else suggested, a trace will likely say exactly what the problem is.

jg

--
@home.com is bogus.
"A lot of people think of Einstein as a mathematical genius - he
wasn't." - David McMullan