This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Feb 26, 2007 3:12 AM by 387878 Go to original post RSS
  • 15. Re: How to limit rows for a table?
    387878 Newbie
    Currently Being Moderated
    Here is the solution completely realized and working:
     
    --This is a normal user table
    create table testInsert (
            msg varchar2(10)
    )
    
    --instead of a sequence, use this
    create table mySeq(
            owner varchar2(20) not null,
            table_name varchar2(30) not null,
            progressive number default 0 not null,
            limit number default 1000 not null
    )
    
    --insert the user table in order to be row limited
    insert into mySeq values('ELETTRO', 'TESTINSERT', 0, 5);
    commit;
    
    --this is more or less a custom sequence, but can be decreased
    create or replace procedure newProg(m_owner varchar2, m_table_name varchar2, inc int) is
      pragma autonomous_transaction;
    begin
      update myseq set progressive = progressive+inc
      where owner = m_owner and table_name = m_table_name;
      if(SQL%ROWCOUNT = 0) then
        raise_application_error(-20001, 'Table ' || m_owner || '.' || m_table_name || 
           'not found in custom sequence');
      end if; 
      commit;
    end;
    /
    
    CREATE OR REPLACE TRIGGER ELETTRO.MYSEQCHKTRIGGER
    BEFORE INSERT OR UPDATE
    ON ELETTRO.MYSEQ 
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
      if :new.progressive > :new.limit then
        raise_application_error(-20001, 'Max rows limit reached');
      end if;
    END mySeqChkTrigger;
    /
    
    CREATE OR REPLACE TRIGGER ELETTRO.MYSEQIMPLEMENT
    BEFORE INSERT
    ON ELETTRO.TESTINSERT 
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
       newProg('ELETTRO', 'TESTINSERT', 1);
    END mySeqImplement;
    /
    
    CREATE OR REPLACE TRIGGER ELETTRO.MYSEQIMPLEMENT_DEL
    BEFORE DELETE
    ON ELETTRO.TESTINSERT 
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
       newProg('ELETTRO', 'TESTINSERT', -1);
    END MYSEQIMPLEMENT_del;
    /
    
    --TEST
    insert into testInsert values('tes');
    insert into testInsert values('tes');
    insert into testInsert values('tes');
    insert into testInsert values('tes');
    insert into testInsert values('tes');
    
    --this generate error 
    insert into testInsert values('tes');
    
    delete from testInsert;
    
    --insert freely again
  • 16. Re: How to limit rows for a table?
    6363 Guru
    Currently Being Moderated
    Here is the solution completely realized and working:
    Right up until the point that two users try it.
  • 17. Re: How to limit rows for a table?
    APC Oracle ACE
    Currently Being Moderated
    You neglected to test it with set-based operations...
    SQL> insert into testInsert 
      2  select 'tes' from all_objects
      3  where rownum < 100
      4  /
    insert into testInsert
    *
    ERROR at line 1:
    ORA-20001: Max rows limit reached
    ORA-06512: at "APC.MYSEQCHKTRIGGER", line 3
    ORA-04088: error during execution of trigger 'APC.MYSEQCHKTRIGGER'
    ORA-06512: at "APC.NEWPROG", line 4
    ORA-06512: at "APC.MYSEQIMPLEMENT", line 2
    ORA-04088: error during execution of trigger 'APC.MYSEQIMPLEMENT'


    SQL> select * from myseq;
    OWNER                TABLE_NAME                     PROGRESSIVE      LIMIT
    -------------------- ------------------------------ ----------- ----------
    ELETTRO              TESTINSERT                               5          5

    SQL> select count(*) from testInsert ;
      COUNT(*)
    ----------
             0

    SQL>
    Cheers, APC
  • 18. Re: How to limit rows for a table?
    APC Oracle ACE
    Currently Being Moderated
    Right up until the point that two users try it.
    Nope. The autonomous transaction will ensure that the limit is imposed across sessions.

    Cheers, APC
  • 19. Re: How to limit rows for a table?
    387878 Newbie
    Currently Being Moderated
    Thanks to all guys posting here,
    there is a major problem with my solution:
    - the custom sequence change is never rolled back, because of its pragma, in the event of a failed insert for other reasons
    - the triggers should not handle 'before' but 'after'

    ...I'm abandoning this issue because of no solution available

    thanks to all
    aldo
  • 20. Re: How to limit rows for a table?
    6363 Guru
    Currently Being Moderated
    Materialized view and constraint would work.
  • 21. Re: How to limit rows for a table?
    MichaelS Guru
    Currently Being Moderated
    Just a showcase what Rob and 3360 were proposing. Works very well in multiuser environment too:
    SQL> CREATE TABLE t (a INTEGER )
    Table created.
    
    SQL> CREATE MATERIALIZED VIEW LOG ON t WITH ROWID(a) INCLUDING NEW
    VALUES
    Snapshot log created.
    
    SQL> CREATE MATERIALIZED VIEW t_mv
    REFRESH FAST ON COMMIT
    AS
    SELECT   COUNT(*) c
        FROM t
    Snapshot created.
    
    SQL> "ALTER TABLE t_mv ADD CONSTRAINT t_mv_chk CHECK (c <= 10)"
    Table altered.
    
    SQL> INSERT INTO t
       SELECT ROWNUM
         FROM user_objects
        WHERE ROWNUM <= 10
    10 rows created.
    
    SQL> COMMIT
    Commit complete.
    
    SQL> INSERT INTO t
         VALUES (11)
    1 row created.
    
    SQL> COMMIT
    COMMIT
    
    Error at line 29
    ORA-12008: error in materialized view refresh path
    ORA-02290: check constraint (EM_ADMIN.T_MV_CHK) violated
  • 22. Re: How to limit rows for a table?
    387878 Newbie
    Currently Being Moderated
    Thanks for your post michaels.
    This looks like the best solution.

    Can you confirm that the materialized view log will not grow indefinitely because its cleaned up after commit?

    thanks a lot
    aldo
  • 23. Re: How to limit rows for a table?
    MichaelS Guru
    Currently Being Moderated
    SQL> CREATE TABLE t (a INTEGER )
    Table created.
    
    SQL> CREATE  MATERIALIZED VIEW LOG ON t WITH ROWID(a) INCLUDING NEW
    VALUES
    Snapshot log created.
    
    SQL> CREATE  MATERIALIZED VIEW t_mv
    REFRESH FAST ON COMMIT
    AS
    SELECT   COUNT(*) c
        FROM t
    Snapshot created.
    
    SQL> ALTER TABLE t_mv ADD CONSTRAINT t_mv_chk CHECK (c <= 10)
    Table altered.
    
    SQL> INSERT INTO t
       SELECT ROWNUM
         FROM user_objects
        WHERE ROWNUM <= 10
    10 rows created.
    
    SQL> "SELECT COUNT (*) FROM mlog$_t"
    
      COUNT(*)
    ----------
            10
    
    1 row selected.
    SQL> COMMIT
    Commit complete.
    
    SQL> "SELECT COUNT (*) FROM mlog$_t"
    
      COUNT(*)
    ----------
             0
    So the LOG entries are going to be deleted fine ..
  • 24. Re: How to limit rows for a table?
    563915 Newbie
    Currently Being Moderated
    Rownum should solve your purpose.
  • 25. Re: How to limit rows for a table?
    533484 Newbie
    Currently Being Moderated
    Hi aldo,

    here is suggestion...

    create a table name XYZ having single column of maximum value it can be 10 or 10000 what ever you want to put the limit.

    then create a function which returns a boolean value of T or F, actualy this function will count the total rows from the table where you want to insert the records.


    in procedure from where you are inserting record just put this condition.

    if totalval = 'T' then

    Insert into abcxyz

    else

    raise error.

    end if ..



    this is just an Idea, I wonder weather It will work for your scenrio or not.

    Regards

    Message was edited by:
    Shamsheer
  • 26. Re: How to limit rows for a table?
    387878 Newbie
    Currently Being Moderated
    Thanks for post Shamsheer,
    but I think the refresh fast on commit proposed above is much faster

    CREATE MATERIALIZED VIEW t_mv
    REFRESH FAST ON COMMIT
    AS
    SELECT COUNT(*) c
    FROM t

    and does not impact any logic, that mean you do not modify original table neither you have functions or trigger on it. Its only a dba issue not a project issue

    thanks
    aldo
1 2 Previous Next