Re: help: cursor does not want to fetch ....ORA-03232 ???

A copy of this was sent to jkucharski_at_altavista.net (Jan Kucharski) (if that email address didn't require changing) On Fri, 18 May 2001 10:52:42 +0100, you wrote:

>Hi
>
>In my plsql code I have the following:
>
>procedure test is
>--
>cursor get_configuration_cur is -- (i_restart integer := 0)
> select * from t_name;
>--
>get_configuration_rec get_configuration_cur%rowtype;
>
>--
>begin
> open get_configuration_cur;
> fetch get_configuration_cur into get_configuration_rec;
> close get_configuration_cur;
> --
> dbms_output.put_line('success and commit ');
> commit;
> --
>exception
> when others then
> raise_application_error ( -20000, SQLCODE || SQLERRM );
> rollback;
>end;
>
>
>And the problem is that the cursor does not want to fetch !!!.
>I can open it and close but when I enable fetch line it hangs.
>
>I do not (!) get the error message either, but am suspicious about
>ora:ORA-03232
>

if you do not get that error, why are you suspicious of it??

>I tried to run the 'select * from t_name' on its own ( outside
>procedure) and it is blazing quick. It seems that the action of FETCHING
>INTO RECORD causes the problem.
>
>What can be a problem ? Anybody has any ideas ?

is t_name a view? the 3232 is about hash space allocation, indicative of a hash join. What is probably happening is that when you simply issue:

select * from t_name;

you are doing so in sqlplus and it is using CHOOSE optimization and it is using a different plan then is being used in PLSQL which uses ALL_ROWS optimization. Since you are interested in ONLY the first record, try coding:

  select /*+ FIRST_ROWS */ * from t_name

(keep the spaces AFTER the + and before the FIRST!).

Short of that, use sql_trace+timed_statistics to see what the query does in SQLPlus as "select * from t_name" and in plsql. see http://asktom.oracle.com/~tkyte/tkprof.html for details on using that. that will be very useful in helping us to diagnose whats going on.

If t_name is a view -- tell us what the view is.

>
>
>Thanks,
>
>Jan
>jkucharski_at_altavista.net

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp