This discussion is archived
6 Replies Latest reply: Feb 18, 2013 10:27 PM by jeneesh RSS

Scenario in Merge

9876564 Newbie
Currently Being Moderated
 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 Guru
    Currently Being Moderated
    Provide sample data (CREATE TABLE and INSERT statements), expected output and your DB version..
  • 2. Re: Doubt in a scenario
    John Stegeman Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Kindly Reply
  • 5. Re: Doubt in a scenario
    WhiteHat Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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"

Legend

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