Re: FETCH Troubles!

Scott J. Little (slittle_at_rmc1.crocker.com) 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.
 

: sjl
 

: --
: Scott J. Little
: slittle_at_rmc1.crocker.com | http://crocker.com/~slittle
: .....................................................................
: You and I, we reject these narrow attitudes
: We add to each other, like a coral reef - Neil Peart, Rush
: Building bridges on the ocean floor "Alien Shore"
: Reaching for the alien shore
: [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[']]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]

I don't know any rational explaination for this but here is what i've found.

If your select is matching strings, probably VARCHAR, then try to use 'LIKE' instead of '='. Somehow this changes the way the statement is executed, many times eliminating the TEMP tablespace problem you mentioned...

Just a guess.
Post if it helps.

Good Luck --
Randy

--
---------------------x--------------------------------------------
Randy DeWoolfson :-) |                Success in all undertakings
     ergo sum        | My opinions are my own - you know the rest
---------------------x--------------------------------------------