Using a Data Sandbox to allow users to push their own data changes to Prod

by MikeHogg 14. November 2011 19:03

Most Pl/Sql code is fairly boring, selecting lots of columns from lots of joins and maybe doing calculations and decodes on the fly, or groupings and other aggregations, and sub queries.  But one project I had was pretty neat.  Users had two lists of Products, and although the names and attributes were not usually the same, they were the same, just called different things by different companies. 

When we started using a new Product, we had to find the two matching ones and map them together.  This involved a process that ultimately led to what we called a data fix.  No code was changed, but data that the business process relied on had to be created or changed in the production database.  To do this required an IT resource to team up with a business resource and first make the changes in a test environment, and then test, and make adjustments, and test again, and then do the same exact cumulative change they did in the test environment, in the production environment. 

This was time intensive, and highly manual.  I wanted to make this quick, and automatic, without any room for manual errors.  So one of the features of the new tool I built was for the users to make these changes themselves, in a test environment, with full auditing and editing capability, and be able to see for themselves the effects their changes had, and when they were ready, push a button to have the exact same results applied to their production environment. This worked wonderfully, and worked with full auditing and history tracking in case any recovery was necessary. Besides some finely tuned controls in the new UI, all it required was an Oracle database link to the test environment.


  procedure PushToProd(p_audit_user varchar2,
                       p_note       varchar2) is
    insert into my_product_history
      (audit_user,  audit_date,  type,
       id,  name, pf_code,  pf_type,  
       cc_code, exch_id,  gs_name,
       pf_commodity, lot_size,  uom_cd, 
       some_lot_sz, term, opt_style,
       product_type,  calendar,  location,
       description_id, trader_group,  settle_date_rule,
       eot_date_rule,  uses_dst, exchange)
      (select p_audit_user,   sysdate, 'PTP',
              nvl(,,  nvl(,,  nvl(p.pf_code, pp.pf_code),  nvl(p.pf_type, pp.pf_type),
              nvl(p.cc_code, pp.cc_code),  nvl(p.exch_id, pp.exch_id),  nvl(p.gs_name, pp.gs_name),
              nvl(p.pf_commodity, pp.pf_commodity),  nvl(p.lot_size, pp.lot_size),  nvl(p.uom_cd, p.uom_cd),
              nvl(p.some_lot_sz, pp.some_lot_sz),  nvl(p.term, pp.term),   nvl(p.opt_style, pp.opt_style),
              nvl(p.product_type, pp.product_type),  nvl(p.calendar, pp.calendar),  nvl(p.location, pp.location),
              nvl(p.description_id, pp.description_id),  nvl(p.trader_group, pp.trader_group),  nvl(p.settle_date_rule, pp.settle_date_rule),
              nvl(p.eot_date_rule, pp.eot_date_rule),  nvl(p.uses_dst, pp.uses_dst), nvl(,
         from my_product p
         full outer join my_product@SOMEW_LINK pp on =
                                                         -- handles new 
        where = p_id
           or = p_id);
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' new record(s) inserted by User ' ||
                              p_audit_user || ' in my_product_history');
    delete from my_product where id = p_id;
    delete from some_fee where product_id = p_id;
    insert into my_product
      (select * from my_product@SOMEW_LINK where id = p_id);
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' new record(s) inserted by User ' ||
                              p_audit_user || ' in my_product');
    insert into some_fee
      (select *
         from SOME.some_fee@SOMEW_LINK
        where product_id = p_id);
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' new record(s) inserted by User ' ||
                              p_audit_user || ' in some_fee');
    when others then
  procedure PushMappingToProd(p_audit_user varchar2,
                              p_note       varchar2) is
    insert into my_values_history
      (audit_user,  audit_date,  type,
       id,   key,  value,
       val_type,   schema,  rule,  exch_id)
      select p_audit_user,  sysdate,  'PTP',
             nvl(,,  nvl(v.key, vv.key),  nvl(v.value, vv.value),
             nvl(v.val_type, vv.val_type),  nvl(v.schema, vv.schema),
             nvl(v.rule, vv.rule),  nvl(v.exch_id, vv.exch_id)
        from my_values v
        full outer join my_values@SOMEW_LINK vv on =
       where = p_v_id
          or = p_v_id;
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' record(s) inserted by User ' ||
                              p_audit_user || ' in my_values_history');
    delete from my_values where id = p_v_id;
    insert into my_values
      (select * from my_values@SOMEW_LINK where id = p_v_id);
                              'Note:' || p_note || '; ' || SQL%ROWCOUNT ||
                              ' new record(s) inserted by User ' ||
                              p_audit_user || ' in my_value');
    when others then


I also put some auditing features in the new UI.  The system relied on the premise that the two databases to start with exact primary key ids and to keep them in sync between the two systems.  I built several auditing screens, showing even the detail for each user’s changes in each environment, so that more thatn one user could see what other users had done.  Here’s one of the auditing tools that showed the user the differences between some of the aggregations of the two systems.  This is the boring sql I was talking about before.  This also works with that oracle database link.

  procedure ViewLotDiff(p_valuation_date in me_trade.valuation_date%type,
                        cur_out          out cur) is
    -- Added condition v.expiration_date >=  p_valuation_date    
    open cur_out for
      select gg.anothercompany_name,
               when sum(some_w_lots) <>  sum(gs_lots) then
                 case when sum(some_p_lots) <>  sum(gs_lots) then 'BOTH'
                 else 'some_W' end 
             end as issue,            
             sum(some_w_lots) total_some_w_lots,
             sum(some_p_lots) total_some_p_lots,
             sum(gs_lots) total_gs_lots            
        from (select some_w_lots,
                from (select round(sum(v.lots),1) some_w_lots,
                             0 some_p_lots,
                             0 gs_lots,
                             'some_W' src,
                        from some_.MAP_A_VW v
                       where 1 = 1
                         and v.VALUATION_DATE = p_valuation_date
                         and (v.term <> 'DAY' or
                             v.contract_month >= p_valuation_date)
                         and ( ( v.pf_commodity in ('WEA','WTI') and nvl(v.expiration_date,to_date('12/31/9999','mm/dd/yyyy')) >= p_valuation_date )
                             ( v.pf_commodity not in ('WEA','WTI') and nvl(v.settle_date,to_date('12/31/9999','mm/dd/yyyy')) >= trunc(to_date(p_valuation_date),'MM')))
                       group by v.anothercompany_NAME, v.PF_CODE )
              union (select 0 some_w_lots,
                           round(sum(d.lots),1) some_p_lots,
                           0 gs_lots,
                           'some_P' src,
                           p.GS_NAME anothercompany_NAME
                      from some_.me_trade_detail@some_P_LINK d
                      join some_.me_trade_header@some_P_LINK h on d.eti = h.eti
                      join some_.me_product@some_P_LINK p on d.pf_code = p.pf_code and d.pf_type = p.pf_type and =
                     where 1 = 1
                       and d.VALUATION_DATE = p_valuation_date
                       and nvl(d.some__status,'nvl') <> 'ORI'
                       and (h.term <> 'DAY' or
                           d.contract_month >= p_valuation_date)
                       and ( ( p.pf_commodity in ('WEA','WTI') and nvl(d.expiration_date,to_date('12/31/9999','mm/dd/yyyy')) >= p_valuation_date  )
                             ( p.pf_commodity not in ('WEA','WTI') and nvl(d.settle_date,to_date('12/31/9999','mm/dd/yyyy')) >= trunc(to_date(p_valuation_date),'MM')))
                     group by p.GS_NAME, d.pf_code )
              union (select 0 some_w_lots,
                           0 some_p_lots,
                           sum(g.net_qty) gs_lots,
                           'GS' src,
                           '' pf_code,
                           g.prod_description anothercompany_name 
                      from gsd_open_positions g
                     where 1 = 1
                       and g.stmt_date = p_valuation_date                       
                       and g.expiry_date >= p_valuation_date
                     group by g.prod_description )              
              ) gg 
       group by anothercompany_name
      having ( (sum(some_w_lots) - sum(some_p_lots) <> 0) or (sum(gs_lots) - sum(some_p_lots) <> 0) or (sum(gs_lots) - sum(some_w_lots) <> 0) )
       order by anothercompany_NAME;

About Mike Hogg

Mike Hogg is a c# developer in Brooklyn.

More Here

Favorite Books

This book had the most influence on my coding style. It drastically changed the way I write code and turned me on to test driven development even if I don't always use it. It made me write clearer, functional-style code using more principles such as DRY, encapsulation, single responsibility, and more.

This book opened my eyes to a methodical and systematic approach to upgrading legacy codebases step by step. Incrementally transforming code blocks into testable code before making improvements.

More Here