1 2 Previous Next 26 Replies Latest reply: Feb 26, 2007 5:12 AM by 387878 Go to original post RSS
      • 15. Re: How to limit rows for a table?
        387878
        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
          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
            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
              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
                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
                  Materialized view and constraint would work.
                  • 21. Re: How to limit rows for a table?
                    MichaelS
                    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
                      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
                        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
                          Rownum should solve your purpose.
                          • 25. Re: How to limit rows for a table?
                            533484
                            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
                              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