This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Aug 29, 2010 12:24 PM by orawiss Go to original post RSS
  • 15. Re: Table with only one row
    794300 Newbie
    Currently Being Moderated
    Thanks, Charles!
    This is what I was looking for.. an unique constraint to prevent from inserting more than row.
  • 16. Re: Table with only one row
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Billy  Verreynne  wrote:
    Jonathan Lewis wrote:

    But what do you get if you try: "select count(*) from dual;" ?
    Sheez.. don't scare me like that! After trying it on my XE instance, I also tested in on a 11gr2 dev cluster just to confirm behaviour on 11g too! ;-)
    Be afraid, be very afraid - the reason I asked the question was that there have been versions of Oracle were "select *" returned one row while "select count(*)" returned two. I wouldn't be suprised if you could still find examples (try joins, maybe) where the second row suddenly becomes visible. I think Connor McDonald also has a story of some unexpected bit of Oracle giving a fatal error because someone had got a second row into dual.

    Regards
    Jonathan Lewis
  • 17. Re: Table with only one row
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Mark,

    Another (probably more appropriate) option :

    Application code and end-users should not be able to log in as the owner of the tables - so create the table, with the one row, then grant only select and update on the table to the application role (or users). Then lock the table-owner's account.

    Regards
    Jonathan Lewis
  • 18. Re: Table with only one row
    FahdMirza Oracle ACE
    Currently Being Moderated
    Hi,
    We can also implement this by using Virtual Column and creating unique index on that virtual column:
    oracle@test # sqlplus /nolog
    
    SQL*Plus: Release 11.1.0.7.0 - Production on Sat Aug 28 19:09:16 2010
    
    Copyright (c) 1982, 2008, Oracle.  All rights reserved.
    
    idle> conn test/test
    Connected.
    test@test> create table t1 (c1 number, c2 generated always as (1) virtual);
    
    Table created.
    
    test@test> create unique index idx1 on t1(c2);
    
    Index created.
    
    test@test> insert into t1(c1) values (1);
    
    1 row created.
    
    test@test> commit;
    
    Commit complete.
    
    test@test> insert into t1(c1) values (1);
    insert into t1(c1) values (1)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TEST.IDX1) violated
    
    
    test@test> insert into t1(c1) values (2);
    insert into t1(c1) values (2)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TEST.IDX1) violated
    regards

    http://fahdmirza.blogspot.com
  • 19. Re: Table with only one row
    CharlesHooper Expert
    Currently Being Moderated
    Fahd,

    Another creative solution. Thanks for posting the example.

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 20. Re: Table with only one row
    orawiss Oracle ACE
    Currently Being Moderated
    Connected to 
    Connected as SYS
     
    SQL> create table Onerow(a number);
     
    Table created
     
    SQL> insert into onerow values(1);
     
    1 row inserted
     
    SQL> commit;
     
    Commit complete
     
    SQL> alter table onerow read only;
     
    Table altered
     
    SQL> insert into onerow values(2);
     
    insert into onerow values(2)
     
    ORA-12081: no se permite la operación de actualización en la tabla "SYS"."ONEROW"
     
    SQL> 
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points