-
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...
Cheers!
OraKid.
-
Hi ,
check the table dual , only one record should be in there...
greetings,
Marc
-
YEAH THERE IS ONLY ONE ROW..
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
Cheers!
OraKid.
-
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;
-
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
-
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.
-
HI,
SQL> SELECT COUNT(1) FROM DUAL;
COUNT(1)
----------
3
I GOT HIS..
REGARDS
Cheers!
OraKid.
-
HI ,
PLZZ..TELL ME THE SOLUTION FOR THIS..
REGARDS
Cheers!
OraKid.
-
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
-
hi,
i delete all the recoreds in the dual table..and
insert into dual values ('x') ;
it is working fine..
regards
Cheers!
OraKid.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|