1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Error While Dropping a table from database

Discussion in 'SQL PL/SQL' started by sambuduk, Nov 4, 2013.

  1. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Friends,

    While Dropping a table I am getting the following error.

    Can any please let me know why it is giving error....

    SQL> drop table tab1;

    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01422: exact fetch returns more than requested number of rows




    Regards
    Sambasiva Reddy.K
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Sambuduk,

    Check Below link.

    Drop Error

    Hope this may help you....
     
    sambuduk likes this.
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Friends

    I found the reason.

    What happend is few days back i have inserted a value in dual table.

    like below

    SQl> insert into dual values('Y');

    SQL> commit;

    after that i have checked the dual table like below

    SQL> select * from dual;

    DUMMY
    -----
    X

    'Y' value is not showing. Actually that 'Y' value is inserted in dual table. check the below steps


    SQl> create table xx_dual_copy as select * from dual;

    sql> select * from xx_dual_copy;

    DUMMY
    ------
    X
    Y

    SQL> DROP TABLE xx_dual_copy;

    Error at line 1

    ORA-00604: error occurred at recursive SQL level 1
    ORA-01422: exact fetch returns more than requested number of rows

    SQL> delete from dual where dummy = 'Y';

    SQL> commit;

    SQL> drop table xx_dual_copy;

    Table dropped.


    Regards,
    Sambasiva Reddy.K
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    DUAL is programmed to return ONE row in later releases of Oracle, and it is NOT a table to be inserting records into as you have found out by experience.