11 Replies Latest reply: Nov 15, 2012 1:10 AM by 871594 RSS

    How to use bind variable :old in update statement ?

    871594
      Hi Friends,


      In Finacle we have Account Num column(FORACID) in Account master table(GENERAL_ACCT_MAST_TABLE ),Transaction table(MINI_STMNT_DETAILS_TABLE ),etc.
      i need to masking bank Account number from Master account table and also need to mask Account number in all related tables.Wherever we have old account number we need to replace with new random generated value.

      Eg:-
      in Account master table Account number is masking to some random number.i have to apply the same number in transaction table also to same account number.

      My code look like this. But not able to use bind variable in update statement.

      Declare
      cursor c1 is select acid,foracid,cif_id from tbaadm.GENERAL_ACCT_MAST_TABLE where acct_ownership not in ('O') order by foracid;

      l_err_code NUMBER;
      l_err_msg VARCHAR2(2000);

      tmp_foracid tbaadm.GENERAL_ACCT_MAST_TABLE.foracid%type;
      masked_foracid tbaadm.GENERAL_ACCT_MAST_TABLE.foracid%type;
      tmp_acid tbaadm.GENERAL_ACCT_MAST_TABLE.acid%type;
      tmp_cif tbaadm.GENERAL_ACCT_MAST_TABLE.cif_id%type;

      begin
      open c1;

      loop

      fetch c1 into tmp_acid ,tmp_foracid,tmp_cif;
           select round(dbms_random.value(81000000,85000000)) into masked_foracid from dual;
           update tbaadm.GENERAL_ACCT_MAST_TABLE set foracid=masked_foracid where acid=tmp_acid;
      update tbaadm.MINI_STMNT_DETAILS_TABLE set foracid=masked_foracid where  :old.foracid=tmp_foracid;

      end loop;
      commit;
      EXCEPTION
      WHEN OTHERS
      THEN
      l_err_code := SQLCODE;
      l_err_msg := SUBSTR(SQLERRM,1,128);

      Dbms_output.put_line(l_err_code || l_err_msg);
      rollback;

      end;


      in above bold line how to compare and update same column value at a same time ?
        • 1. Re: How to use bind variable :old in update statement ?
          jeneesh
          It should be,
          where foracid=tmp_foracid;
          But few questions:
          Declare
               --"You should search for - CURSOR FOR UPDATE and WHERE CURRENT of features in PL/SQL
                          cursor c1 is select acid,foracid,cif_id 
                         from tbaadm.GENERAL_ACCT_MAST_TABLE 
                         where acct_ownership not in ('O') 
                         order by foracid;
               l_err_code NUMBER;
               l_err_msg VARCHAR2(2000);
               tmp_foracid tbaadm.GENERAL_ACCT_MAST_TABLE.foracid%type;
               masked_foracid tbaadm.GENERAL_ACCT_MAST_TABLE.foracid%type;
               tmp_acid tbaadm.GENERAL_ACCT_MAST_TABLE.acid%type;
               tmp_cif tbaadm.GENERAL_ACCT_MAST_TABLE.cif_id%type;
          begin
               open c1;
               loop 
          
                    fetch c1 into tmp_acid ,tmp_foracid,tmp_cif;
                         --"The random number you are selecting below cannot be unique 
                         --"random numbers are in random - they can be duplicate also.
                         --"And whey are you selecting form dual?
                         --You can directly write masked_forcid := dbms_random.value(81000000,85000000);
                    select round(dbms_random.value(81000000,85000000)) 
                    into masked_foracid 
                    from dual;
               --"You should search for - CURSOR FOR UPDATE and WHERE CURRENT of features in PL/SQL
                    update tbaadm.GENERAL_ACCT_MAST_TABLE 
                    set foracid=masked_foracid 
                    where acid=tmp_acid;
                         --"Why yoiu need :OLD here? This is not a trigger.
                    update tbaadm.MINI_STMNT_DETAILS_TABLE 
                    set foracid=masked_foracid 
                    where foracid=tmp_foracid;
               end loop;
               commit;
          EXCEPTION
               WHEN OTHERS THEN
                            --"How will this exception handler help you? 
                            --"You should think of error logging.
                    l_err_code := SQLCODE;
                    l_err_msg := SUBSTR(SQLERRM,1,128);
                    Dbms_output.put_line(l_err_code || l_err_msg);
                    rollback;
          end;
          And above all, cant you do this in plain SQL?
          Start with storing IDs and masked IDs in a temporary table.
          Do a direct update on the tables you want...
          • 2. Re: How to use bind variable :old in update statement ?
            sb92075
            below is a bug waiting to bite YOU!
            EXCEPTION
                 WHEN OTHERS THEN
                              --"How will this exception handler help you? 
                              --"You should think of error logging.
                      l_err_code := SQLCODE;
                      l_err_msg := SUBSTR(SQLERRM,1,128);
                      Dbms_output.put_line(l_err_code || l_err_msg);
                      rollback;
            delete, remove, & eliminate all EXCEPTION code
            For reason why check these links.

            http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

            http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

            http://tkyte.blogspot.com/2007/03/challenge.html
            • 3. Re: How to use bind variable :old in update statement ?
              Purvesh K
              It seems like you have a serious issue of Understanding. You need to understand the difference between Triggers and general PL/SQL.

              The bind variables :NEW/:OLD are applicable only in ROW Level Triggers (For more info, read here).
              update tbaadm.MINI_STMNT_DETAILS_TABLE set foracid=masked_foracid where :old.foracid=tmp_foracid;
              Moreover, you have used the ":old.foracid" instead of a Column Name; Even in a trigger the :old.foracid will refer to the previous value of FORACID and not the FORACID column name.

              Hence, you will have to modify it to
              update tbaadm.MINI_STMNT_DETAILS_TABLE set foracid=masked_foracid where foracid=tmp_foracid;
              to get it working.

              Going through your code, the performance would not be very good and can be improved.
              I would suggest you to get rid of cursor for updating the records and use SQL to perform the operation.

              Here is a sample stub that may help you.
              begin
              
                /* Now, Update MINI_STMNT_DETAILS_TABLE with the Master Acc No */
                update tbaadm.MINI_STMNT_DETAILS_TABLE mini set foracid=round(dbms_random.value(81000000,85000000)) 
                 where foracid = (select foracid from tbaadm.GENERAL_ACCT_MAST_TABLE mast where acct_ownership not in ('O') where mast.primary_key = mini.primary_key);
              
                /* Update your Master Acc Table to Mask the Acc No */
                update tbaadm.GENERAL_ACCT_MAST_TABLE set foracid = round(dbms_random.value(81000000,85000000));
              
                commit;
              exception
                when others then
                  rollback;
                  your_exception_logger;
                  raise;
              end;
              • 4. Re: How to use bind variable :old in update statement ?
                jeneesh
                Purvesh,

                The above code cannot make sure that the similar foracid in both the tables are masked to a same random number.

                you will have to store the ID and curresponding masked ID in a temporary table first.

                And RANDOM numbers cannot be unique - so different IDs can be replaced by same masked values.

                Edited by: jeneesh on Nov 15, 2012 10:49 AM
                • 5. Re: How to use bind variable :old in update statement ?
                  Purvesh K
                  jeneesh wrote:
                  Purvesh,

                  The above code cannot make sure that the similar foracid in both the tables are masked to a same random number.
                  Yes, You are correct.
                  Sorry, that I over-looked the code and explanation to have same masked ID.
                  you will have to store the ID and curresponding masked ID in a temporary table first.

                  And RANDOM numbers cannot be unique - so different IDs can be replaced by same masked values.
                  I am not sure, but a Merge can be used to update both the tables.
                  Let me try and get back.

                  Edit:- Added demonstration using Global Temp Table.
                  Below is a sample way to performing the task:
                  drop table master_test_table;
                  drop table child_test_table;
                  drop table global_temp_tab;
                  
                  create table master_test_table
                  (
                    mast_acc_no       number
                  );
                  table master_test_table created.
                  
                  create table child_test_table
                  (
                    acc_no            number
                  );
                  table child_test_table created.
                  
                  create global temporary table global_temp_tab
                  (
                    mast_acc_no       number,
                    mask_acc_no       number
                  )
                  on commit delete rows;
                  
                  global temporary table created.
                  
                  insert into master_test_table values (1234567890);
                  insert into master_test_table values (1234756890);
                  insert into master_test_table values (1234765890);
                  insert into master_test_table values (1234657890);
                  insert into master_test_table values (1234675890);
                  
                  1 rows inserted.
                  1 rows inserted.
                  1 rows inserted.
                  1 rows inserted.
                  1 rows inserted.
                  
                  
                  insert into child_test_table values (1234567890);
                  insert into child_test_table values (1234657890);
                  insert into child_test_table values (1234765890);
                  
                  1 rows inserted.
                  1 rows inserted.
                  1 rows inserted.
                  
                  commit;
                  
                  select *
                    from master_test_table;
                  MAST_ACC_NO            
                  ---------------------- 
                  1234567890             
                  1234756890             
                  1234765890             
                  1234657890             
                  1234675890             
                  
                  
                  select *
                    from child_test_table;
                  ACC_NO                 
                  ---------------------- 
                  1234567890             
                  1234657890             
                  1234765890
                  
                  insert into global_temp_tab
                  select mast_acc_no, round(dbms_random.value(81000000,85000000))
                    from master_test_table;
                  
                  5 rows inserted.
                  
                  select *
                    from global_temp_tab;
                  
                  MAST_ACC_NO            MASK_ACC_NO            
                  ---------------------- ---------------------- 
                  1234567890             84007005               
                  1234756890             83727389               
                  1234765890             81567038               
                  1234657890             81292846               
                  1234675890             81144846
                  
                  update child_test_table c
                     set acc_no = (
                                    select mask_acc_no
                                      from global_temp_tab t
                                     where t.mast_acc_no = c.acc_no
                                  );
                  3 rows updated.
                  
                  update master_test_table c
                     set mast_acc_no = (
                                    select mask_acc_no
                                      from global_temp_tab t
                                     where t.mast_acc_no = c.mast_acc_no
                                  );
                  5 rows updated.
                  
                  select *
                    from master_test_table;
                  MAST_ACC_NO            
                  ---------------------- 
                  84007005               
                  83727389               
                  81567038               
                  81292846               
                  81144846
                  
                  select *
                    from child_test_table;
                  ACC_NO                 
                  ---------------------- 
                  84007005               
                  81292846               
                  81567038
                  
                  commit;
                  Edited by: Purvesh K on Nov 15, 2012 11:08 AM
                  • 6. Re: How to use bind variable :old in update statement ?
                    jeneesh
                    Purvesh K wrote:
                    insert into global_temp_tab
                    select mast_acc_no, round(dbms_random.value(81000000,85000000))
                    from master_test_table;
                    This cannot guarantee unique numbers for each acc numbr.

                    Something like below will give UNIQUE values
                    insert into global_temp_tab
                    select mast_acc_no, 
                            rownum+100000000 --"some big value, which is not expected as actual value, or use -ve numbers or rownum as it is"
                      from master_test_table
                      order by dbms_random.value--"not required, use it if some "randomness" is required;
                    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                    • 7. Re: How to use bind variable :old in update statement ?
                      Purvesh K
                      jeneesh wrote:
                      This cannot guarantee unique numbers for each acc numbr.
                      Yes, Unique will not it be.
                      But OP mentions Random numbers not unique, although I wonder why would it be. If OP does have a Unique index on account no column and is under an impression that random shall give a unique output, the (s)he is wrong.

                      Why not use a Sequence instead?
                      • 8. Re: How to use bind variable :old in update statement ?
                        jeneesh
                        Purvesh K wrote:
                        jeneesh wrote:
                        This cannot guarantee unique numbers for each acc numbr.
                        Yes, Unique will not it be.
                        Unique will be required - othrwise joins to the child table will give wrong output
                        But OP mentions Random numbers not unique, although I wonder why would it be. If OP does have a Unique index on account no column and is under an impression that random shall give a unique output, the (s)he is wrong.

                        Why not use a Sequence instead?
                        I am still not able to understand, actual requirement of the OP...
                        If he mask the account number in DB by this way, where will he have the original account numbers?

                        Requirement seems strange any how..!
                        • 9. Re: How to use bind variable :old in update statement ?
                          Purvesh K
                          Maybe, OP wants to encrypt the data and is using Random numbers to attain the same.

                          @OP,
                          If this is the case, and you are on Oracle 11g, then Transparent Data Encryption may be a better and a robust solution. Read here for more info.
                          • 10. Re: How to use bind variable :old in update statement ?
                            jeneesh
                            Purvesh K wrote:
                            If this is the case, and you are on Oracle 11g, then Transparent Data Encryption may be a better and a robust solution. Read here for more info.
                            But, I dont think TDE can be used for encrypting values where FK relationships are defined. I am not an expert in that, not sure whether work arounds are available.
                            • 11. Re: How to use bind variable :old in update statement ?
                              871594
                              Purvesh,


                              Thanks for your feedback.

                              /* Now, Update MINI_STMNT_DETAILS_TABLE with the Master Acc No */
                              update tbaadm.MINI_STMNT_DETAILS_TABLE mini set foracid=round(dbms_random.value(81000000,85000000))
                              where foracid = (select foracid from tbaadm.GENERAL_ACCT_MAST_TABLE mast where acct_ownership not in ('O') where mast.primary_key = mini.primary_key);

                              /* Update your Master Acc Table to Mask the Acc No */
                              update tbaadm.GENERAL_ACCT_MAST_TABLE set foracid = round(dbms_random.value(81000000,85000000));


                              Here i will not get the same masked value by generating random number twice for one in MINI_STMNT_DETAILS_TABLE and another in GENERAL_ACCT_MAST_TABLE .So both tables will have different data.

                              Thanks,
                              Venkat Vadlamudi.