Re: FETCH Troubles!

slittle_at_rmc1.crocker.com (Scott J. Little) wrote:

>Hi,
 

>This is my first posting to this newsgroup, but I've got a weird problem.
>I've written a report in Pro*C using embedded SQL. I go through all the
>proper steps (just like I have a hundred times before):
 

> DECLARE myCurs CURSOR FOR
> SELECT blah, blah, blah...
> ...
> ORDER BY 1, 2, 3;
 
> error checking
 

> OPEN myCurs;
 

> error checking
 

> FETCH myCurs INTO
> :hostVar1 :indVar1,
> :hostVar2 :indVar2;
 

>Seems correct, yes?
 

>Well I keep getting the following error when I run it:
 

>ORA-01630: max # extents (101) reached in temp segment in tablespace TEMPORARY
 

>ERR-ERRSTMT: Error occurred on following statement:
> SELECT SIRASGN_CRN,SIRASGN_PIDM FROM SATURN.SIRASGN,SATURN.SSBSECT,SAT
>%SYSTEM-F-ABORT, abort
 

>There is no way. The select is only supposed to return *** 10 *** rows -
>that's all. In fact if I take the select statement and run the exact
>statement in SQL*Plus I get my results back in no time flat.
 

>I can't understand why there would be such a great discrepency between the two.
 

>Any help would be greatly appreciated.

i'm not an expert on this subject but this does not sound like a problem with your sql statement. i suppose to check the size of your temporary tablespace. did you execute your program and sql*plus session under the same user_id (as you will already know each user can be assigned a temporary tablespace) ? also try to execute your program without the 'order by'-clause (the temp segs will be used for ordering...). if this also does not help try to modify the maxextents and pctincrease parameters.

hope this helps

by the way: i suffered similar problems with rollback segments when using the 'for update'-clause. Received on Sat May 06 1995 - 00:00:00 CEST