Re: PL/SQL job failing - extents?

Andrew wrote:
> First up - sorry if this is a daft question - got a fair bit of SQL Server
> and Informix background - Oracle - total newbie.
> Got a PL/SQL run which fails fairly regularly with a fetch out of sequence
> error. Usually I know this is because you try to fetch after closing a
> cursor - particularly during nested fetches.
> However - this problem - without changing the data at all (transfer and
> additional information from one table - plus lookups to another) runs OK
> most of the time when re-run. So - my admittedly rough guess is some kind
> of contention.

What kind of contention would cause a fetch out of sequence error?

Maybe the following would help:

http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

Additionally look at the actions from this ORA-1002 error message:

oerr ora 1002
01002, 00000, "fetch out of sequence"

// *Cause: This error means that a fetch has been attempted from a cursor
//         which is no longer valid.  Note that a PL/SQL cursor loop
//         implicitly does fetches, and thus may also cause this error.
//         There are a number of possible causes for this error, including:
//         1) Fetching from a cursor after the last row has been retrieved
//            and the ORA-1403 error returned.
//         2) If the cursor has been opened with the FOR UPDATE clause,
//            fetching after a COMMIT has been issued will return the error.
//         3) Rebinding any placeholders in the SQL statement, then issuing
//            a fetch before reexecuting the statement.
// *Action: 1) Do not issue a fetch statement after the last row has been
//             retrieved - there are no more rows to fetch.
//          2) Do not issue a COMMIT inside a fetch loop for a cursor
//             that has been opened FOR UPDATE.
//          3) Reexecute the statement after rebinding, then attempt to
//             fetch again.

Ensure you are following the suggested actions.

 > Now - in some cases in SQL Server - Informix, this can be
> caused by the table having a new extent applied part way through the job -
> and again the job will go ok on re-run as there's now enough space for the
> job. I asked our Oracle bods whether this may be the case, and when the
> last extent was allocated to the target table - to see if it co-incided with
> the job failure. Got back a snotty "I've been working with Orcle for 10
> years" reply "and there's no easy way to do this"
> The two questions are - is the additional extent issue likely, secondly -
> how can you check the date and time of the last extent allocation for a
> given table?
>
> cheers
> Andrew
>
>

The correct answer from your "Oracle bods" should have been that extent allocation does not contribute to the ORA-1002 error.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown