Re: Problem with PL/SQL / nextval

Niklas Mehner wrote:

> I'm using Oracle 8.1.5.
>
> When executing the following script I get a "ORA-01422: exact fetch
> returns more than requested number of rows" :
>
> SQL> run
> 1 DECLARE
> 2 icounter number(11);
> 3 BEGIN
> 4 Select SEQ_LEITUNG.NEXTVAL INTO iCounter FROM Dual;
> 5* END;
> DECLARE
> *
> ERROR at line 1:
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at line 4
>
> ... but how can there be more than one row !?!
>
> SQL> select seq_leitung.nextval from dual;
>
> NEXTVAL
> ---------
> 1636
>
> Please help !
>
> thnx, Niklas

This is a PL/SQL issue. PL/SQL always executes a SELECT ... INTO twice in order to throw this exception. To repevent this, create an explicit cursor for the query, open it, fetech from it, and close it.

Some advice on PL/SQL...

1. NEVER use implcit cursors for queries.
2. ALWAYS put an exception handler in your code
3. ALWAYS close your cursor before the PL/SQL program unit completes (both in the body of the code and the exception handlers).

--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C. Received on Thu Aug 19 1999 - 07:15:17 CDT