This discussion is archived
11 Replies Latest reply: Nov 14, 2012 11:10 PM by 871594 RSS

How to use bind variable :old in update statement ?

871594 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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