Re: Problem with PL/SQL / nextval

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 20 Aug 1999 11:51:00 GMT
Message-ID: <7pjff4$s4j$4@news.seed.net.tw>

Ron Reidy <rereidy_at_uswest.net> wrote in message news:37BBF555.3DBF1A41_at_uswest.net...
> 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.

No.
Even thouth if PL/SQL executes SQL twice, it would not cause this error. In fact, PL/SQL does NOT execute SQL twice.

Oracle generates a SQL
"SELECT SEQ_LEITUNG.NEXTVAL FROM DUAL" during executing this PL/SQL code. The object names analyzing are the same in SQL and PL/SQL. I have no idea why this error occured. Received on Fri Aug 20 1999 - 06:51:00 CDT