6 Replies Latest reply: Feb 19, 2013 12:27 AM by jeneesh RSS

    Scenario in Merge

    9876564
       Oracle Version 11.2.0.2.0
      I have a condition where I need to update a column ( it is basically a flag which has to be set to “Y”) for a row and at the same time I need to take care that the number of rows having flag value = Y should not be more than 5.
      So if I try to update the 6th Row with Y it should throw an error message.
      If there are already 2 rows having the flag value =’Y’ , and if are trying to update it for 4 more rows then after updating 3 rows for the 4th row it should throw an error and the whole transaction should be reverted back.

      I want to use the
       Merge
      statement.
      Please let me know how to achieve the same

      Edited by: AbSHeik on Feb 18, 2013 1:30 AM
        • 1. Re: Doubt in a scenario
          jeneesh
          Provide sample data (CREATE TABLE and INSERT statements), expected output and your DB version..
          • 2. Re: Doubt in a scenario
            John Stegeman
            Because Oracle is inherently a multi-user DBMS and you cannot see other users' changes until they commit, there is no straightforward way to implement your requirement.

            The one way that I have seen suggested is to use a refresh-on-commit materialised view that would count the number of rows with 'Y' and a check constraint on the MV to ensure the count is 5 or less.
            • 3. Re: Doubt in a scenario
              9876564
              The structure of the table is
               Create table test1 
                 ( a_var    number,
                 a_flag    varchar2(1)
                 );
              
                 insert into test1 values (1, 'Y');
                 insert into test1 values (1, 'Y');
                 insert into test1 values (2, 'N');
                 insert into test1 values (2, 'N');
                 insert into test1 values (2, 'N');
                 insert into test1 D values (3, 'Y');
              U can see there are 3 rows having the flag values is equal to "Y" , now if i try to update the flag for a_var =2 , 3 more values will get updated but my requirement is if at any point of time the number of rows having flag = "Y" is more then 5 then an error message should be thrown to the use.

              I hope the question is clear.
              • 4. Re: Doubt in a scenario
                9876564
                Kindly Reply
                • 5. Re: Doubt in a scenario
                  523861
                  you can try a trigger - this has its own disadvantages but you may find this works for your requirements:

                  create or replace trigger test1_max_y
                  for insert or update on test1
                  COMPOUND TRIGGER 
                  
                  
                     type t_test1 is table of test1%rowtype index by pls_integer;
                     
                     g_test1 t_test1;
                     c_index pls_integer := 0;
                     
                      procedure check_y_number (p_new_values in test1%rowtype)
                      IS
                      --This procedure is overloaded so that it can be used for multiple tables
                        v_count number;
                      BEGIN
                         -- 
                        SELECT count(*)
                          INTO v_count
                          from test1 
                         WHERE a_flag = 'Y';
                         --
                         if v_count > 5 then
                                raise_application_error(-20000,'Statement will result in more than 5 y''s');
                         end if;
                         --
                      END;
                  
                  
                      before statement is
                      begin
                      
                         --initialise the collection
                         g_test1.delete;
                         c_index := 0;
                         
                      end before statement;
                      
                      after each row is
                        c_index constant pls_integer := g_test1.count + 1;
                      begin
                      
                       g_test1(c_index).a_var := :new.a_var;                                                                                                                                                  
                       g_test1(c_index).a_flag := :new.a_flag;
                      
                      end after each row;
                  
                      after statement is 
                      begin
                  
                      for i in 1..g_test1.count loop
                         check_y_number(g_test1(i));
                      END loop;
                  
                      end after statement;
                  
                  
                  
                  end;
                  /
                  show errors;
                  results in this:
                  SQL> select *
                    2    from test1
                    3  /
                  
                       A_VAR A
                  ---------- -
                           1 Y
                           1 Y
                           2 N
                           2 N
                           2 N
                           3 Y
                  
                  6 rows selected.
                  
                  SQL> update test1 set a_flag = 'Y' where a_var = 2;
                  update test1 set a_flag = 'Y' where a_var = 2
                         *
                  ERROR at line 1:
                  ORA-20000: Statement will result in more than 5 y's
                  ORA-06512: at "WHITEHAT.TEST1_MAX_Y", line 21
                  ORA-06512: at "WHITEHAT.TEST1_MAX_Y", line 49
                  ORA-04088: error during execution of trigger 'WHITEHAT.TEST1_MAX_Y'
                  be aware of the concurrency issues though.
                  • 6. Re: Doubt in a scenario
                    jeneesh
                    AbSHeik wrote:
                    Kindly Reply
                    Did you try what John suggested?
                    create materialized view log on test1 with rowid;
                    
                    materialized view LOG created.
                    
                    create materialized view test_mview refresh on commit as
                    select sum(decode(a_flag,'Y',1,0)) y_cnt
                    from test1;
                    
                    materialized view TEST_MVIEW created
                    
                    alter materialized view test_mview add constraint 
                     chk_y_vnt check(y_cnt <= 5 );
                    
                    materialized view TEST_MVIEW altered.
                    
                    select *
                    from test_mview;
                    
                    Y_CNT
                    -----
                        3 
                    
                    update test1 set a_flag='Y' where a_flag='N' and rownum=1;
                    
                    
                    1 rows updated.
                    
                    update test1 set a_flag='Y' where a_flag='N' and rownum=1;
                    
                    1 rows updated.
                    
                    commit;
                    
                    committed.
                    
                    
                    select *
                    from test_mview;
                    
                    Y_CNT
                    -----
                        5
                    
                    update test1 set a_flag='Y' where a_flag='N' and rownum=1;
                    
                    1 rows updated.
                    
                    commit;
                    
                    SQL Error: ORA-12008: error in materialized view refresh path
                    ORA-02290: check constraint (HR.CHK_Y_VNT) violated
                    12008. 00000 -  "error in materialized view refresh path"