Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Lightbulb Unanswered: Dual Table: Too Many Rows

    As we all know Dual table always have 1 row and 1 column. Can a simple select from this table throw "too_many_rows" exception. lately I have seen some codes handling too_many_rows for Dual. Is it just a stupid mistake by programmer or there is some logic in this ?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Normally this happens when the programmer sets his default status to one-D,ten-T

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    thanks

    I can understand your humour :-) but can you please explain a bit more.
    Thanks for ur help

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    DUAL table should NEVER return more than one row (too_many_rows exception).
    If this is not the case, then you must IMMEDIATELY correct the issue.


    Last edited by LKBrwn_DBA; 03-03-06 at 13:34.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by varun_751980
    As we all know Dual table always have 1 row and 1 column.
    OK for "always one row", but not "always one column".

    Code:
    rbaraer@Ora10g> select 'col1' c1, 'col2' c2, sysdate c3 from dual;
    
    C1   C2   C3
    ---- ---- ---------
    col1 col2 03-MAR-06
    
    rbaraer@Ora10g>
    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    The DUAL table definition does have ONE column even if you can generate any number of pseudo columns:
    Code:
    SQL>desc dual
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     DUMMY                                              VARCHAR2(1)


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Feb 2006
    Posts
    15
    Here is a short piece on dual and too many rows from an article on self-managing pl/sql by Steven Feuerstein. Apparently he has encountered this issue before - folks inserting rows into dual.

    http://www.oracle.com/technology/ora...ech_plsql.html


    Scroll down to the fourth heading called: Use Your Own DUAL Table

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by LKBrwn_DBA

    The DUAL table definition does have ONE column even if you can generate any number of pseudo columns:
    Code:
    SQL>desc dual
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     DUMMY                                              VARCHAR2(1)


    Thanks, good to know. Sorry for my mistake .

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Red_Dog
    Here is a short piece on dual and too many rows from an article on self-managing pl/sql by Steven Feuerstein. Apparently he has encountered this issue before - folks inserting rows into dual.

    http://www.oracle.com/technology/ora...ech_plsql.html


    Scroll down to the fourth heading called: Use Your Own DUAL Table

    Quote Originally Posted by o34tech_plsql.html
    Because I have much more control over the contents of onerow, this is a better solution than querying from DUAL
    This statement from the article you posted is completely FALSE! (crap) how can anyone have more control over a user defined table than one which resides in the SYS schema?

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Feb 2006
    Posts
    15
    Quote Originally Posted by LKBrwn_DBA


    This statement from the article you posted is completely FALSE! (crap) how can anyone have more control over a user defined table than one which resides in the SYS schema?

    Hmmmm.
    Are you saying that triggers don't give you more control over the contents of a table?

    Who mentioned more control than sys?

  11. #11
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Using your own "onerow" table may seem like a smart idea until you get to 10g, which has a "fast dual" optimization that can cut out a LIO when it sees you are querying sys.dual. I guess Feuerstein's solution would be to change the NEXT_PKY function code back to using dual once you get to 10g, though you are still stuck with dynamic code. Seems like a terrible idea to me.

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The trigger in that article doesn't actually prevent insertion of more than one row into the ONEROW table anyway:
    Code:
    SQL> CREATE TABLE onerow (dummy VARCHAR2(1))
      2  /
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER enforce_onerow
      2     BEFORE INSERT
      3     ON onerow
      4  DECLARE
      5     PRAGMA AUTONOMOUS_TRANSACTION;
      6     l_count PLS_INTEGER;
      7  BEGIN
      8     SELECT COUNT (*)
      9       INTO l_count
     10       FROM onerow;
     11 
     12     IF l_count = 1
     13     THEN
     14        raise_application_error
     15                        (-20000
     16                       , 'The onerow table can have only
    one row.'
     17                        );
     18     END IF;
     19  END;
     20  /
    
    Trigger created.
    
    SQL> insert into onerow values ('x');
    
    1 row created.
    
    SQL> insert into onerow values ('x');
    
    1 row created.
    
    SQL> select * from onerow;
    
    D
    -
    x
    x
    If the AUTONOMOUS_TRANSACTION pragma were removed, then it
    would be harder but still not impossible to create 2 or more
    records: 2 or more sessions could simultaneously insert 1 row
    into the empty table, and each would think it was the only
    record.

    A much simpler and fool-proof solution would be to drop the
    trigger and add the following constraints:
    Code:
    ALTER TABLE onerow ADD CONSTRAINT onerow_pk PRIMARY KEY
    (dummy);
    ALTER TABLE onerow ADD CONSTRAINT onerow_chk CHECK (dummy
    = 'x');
    Not that I am suggesting this - like some others above, I think the ONEROW table is a daft idea altogether.

  13. #13
    Join Date
    Feb 2006
    Posts
    15
    I was reminded of this article by the OP's question and it is interesting to read other opinions.

    WilliamR considers it from a performance point of view but perhaps Feuerstein could argue that he is writing in the context of self-managing pl/sql code where robustness takes priority over performance.

    andrewst suggests a different approach to Feuerstein's but only as an academic argument rather than a practical solution.

    I personally think Feuerstein takes his self-managing pl/sql way beyond what is practical and engineers a solution to skip over the underlying problem.

    LKBrwn_DBA hits the nail on the head:


    Quote Originally Posted by LKBrwn_DBA

    DUAL table should NEVER return more than one row (too_many_rows exception).
    If this is not the case, then you must IMMEDIATELY correct the issue.


    If dual (or any other sys table for that matter) is changed then you will not be supported. Oracle Support will disown you until you fix it.

    Should I really care that my code fails to run on such a system?

  14. #14
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565

    Arrow

    Another way to prevent INSERTs:
    Code:
    SQL> CREATE TABLE onerow
      2  ( dummy VARCHAR2(1) NOT NULL
      3          CONSTRAINT onerow_no_changes_allowed_chk CHECK(1=2) DISABLE );
    
    Table created.
    
    SQL> INSERT INTO onerow VALUES ('X');
    
    1 row created.
    
    SQL> ALTER TABLE onerow ENABLE NOVALIDATE CONSTRAINT onerow_no_changes_allowed_chk;
    
    Table altered.
    
    SQL> INSERT INTO onerow VALUES ('Y');
    INSERT INTO onerow VALUES ('Y')
    *
    ERROR at line 1:
    ORA-02290: check constraint (WILLIAMR.ONEROW_NO_CHANGES_ALLOWED_CHK) violated

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why not use a simple trigger. Build and populate the table and then create the trigger.

    CREATE OR REPLACE TRIGGER enforce_onerow
    BEFORE INSERT UPDATE DELETE
    ON onerow
    BEGIN
    raise_application_error (-20000, 'The onerow table may NOT be modified');
    END;

    The oracle database since Oracle 8 has know that the sys.dual table is special and contains only one row and takes that into account when making the execution plan. It is just plain stupid to use your own table except if the sql code is being coded to be able to run in more then an oracle database.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •