URGENT !!! EXACT FETCH RETURNS MORE THAN ONE ROW WHEN SELECTING SYSDATE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

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

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Angry

    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.

  2. #2
    Join Date
    Jun 2001
    Location
    Belgium
    Posts
    13
    Hi ,

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

    greetings,

    Marc

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    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.

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    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;

  5. #5
    Join Date
    Jun 2001
    Location
    Belgium
    Posts
    13
    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


  6. #6
    Join Date
    Jul 2000
    Posts
    296
    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.

  7. #7
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    HI,


    SQL> SELECT COUNT(1) FROM DUAL;

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


    I GOT HIS..

    REGARDS
    Cheers!
    OraKid.

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    HI ,


    PLZZ..TELL ME THE SOLUTION FOR THIS..


    REGARDS
    Cheers!
    OraKid.

  9. #9
    Join Date
    Apr 2001
    Posts
    24
    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

  10. #10
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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
  •  



Click Here to Expand Forum to Full Width