URGENT !!! EXACT FETCH RETURNS MORE THAN ONE ROW WHEN SELECTING SYSDATE

[balajiyes]

HI,

IF WE ARE SELECTING SYSDATE OR SEQ.NEXTVAL INSIDE A PLSQL BLOCK,WE ARE GETTING THE ERROR EXACT FETCH RETURNS MORE THAN REQUESTED NO OF ROWS..IF WE EXECTUE IN SQL PROMPT IT'S WORKING FINE..IT'S URGENT...

[mvander2]

Hi ,

check the table dual , only one record should be in there...

greetings,

Marc

[balajiyes]

HI,

I CHECKED IT ..THERE IS ONLY ONE ROW...THIS PROBLEM ARISES ONLY IN A PLSQL BLOCK..IF WE ISSUE IT IN SQL PROMPT..THEN NO PROBLEM..


ALSO IF WE SELECT

SELECT SEQ_ID.NEXTVAL FROM DUAL;

INSIDE A PLSQL BLOCK WE ARE GETTING THE SAME ERROR..EXACT FETCH RETURNS MORE THAN REQUESTED NO OF ROWS...

SAME ERROR COMES FOR


SELECT SEQ_ID.NEXTVAL FROM DUAL;


THANX

[akkerend]

Check table DUAL as mvander2 suggest. In 8i SQL*Plus returns one record if you try
SELECT SYSDATE FROM dual;
even if dual contains more rows, so try:
SELECT COUNT(1) FROM dual;

[mvander2]

You can try ...
select owner,table_name from dba_tables where table_name = 'DUAL';

maybe someone created an extra dual table ?

sorry to keep insisting on the dual table

[akkerend]

How did you check the number of rows in dual?
In 8i in SQL*Plus only a
SELECT COUNT(1) FROM dual;
gives the correct answer. Oracle returns always one row for statements like
SELECT * FROM dual;
In PL/SQL all rows are returned, so you get the ORA-01422: exact fetch returns more than requested number of rows.

[balajiyes]

HI,


SQL> SELECT COUNT(1) FROM DUAL;

COUNT(1)
----------
3


I GOT HIS..

REGARDS

[balajiyes]

HI ,


PLZZ..TELL ME THE SOLUTION FOR THIS..


REGARDS

[sasmita]

Hello,

Actually in dual table there should be only one row with value ‘X’.
If you say : SQL> select * from dual;

D
-
X
But if you say SQL> select count(*) from dual;

COUNT(*)
----------
3
This is because I have inserted 2 more rows in this dual table.. to find out solution for your problem.

If your dual table is having more than one row then ..hope below solution will help u ?

What I did to find only one row in my dual table is :
SQL> select rowid from dual;

ROWID
------------------
AAAADDAABAAAAHSAAA

Then
SQL> delete from dual where rowid <> 'AAAADDAABAAAAHSAAA';

1 row deleted.

SQL> delete from dual where rowid <> 'AAAADDAABAAAAHSAAA';

1 row deleted.

SQL> delete from dual where rowid <> 'AAAADDAABAAAAHSAAA';

0 rows deleted.
SQL> commit;

Commit complete.

SQL> select count(*) from dual;

COUNT(*)
----------
1

If you find any other solution please let me know..

Sasmita

[balajiyes]

hi,

i delete all the recoreds in the dual table..and

insert into dual values ('x') ;

it is working fine..

regards