Thursday, January 18, 2018

ORACLE - How generate DML command to string and how execute it with parameters

When you need dynamically change name of the table in pl/sql procedure, one approach is generate command to string and then execute it.
function Sql_AnalyzeTurnover( pPeriod number ) return number
is
  sKey varchar2(4);
  str varchar2(4000);
begin
...
  str := 'update factory.cieb_an_turnover a ';
  str := str || 'set a.c_inventory = nvl( a.c_inventory, 0 ) ';
  str := str || 'from tiskman.uz_state_p' || sKey || ' ';
  str := str || 'where ';
  str := str || 'tl_nr = a.tl_nr and ';
  str := str || 'tl_fam = a.tl_fam and ';
  str := str || 'tl_atn = a.tl_atn ';
  str := str || 'and lag_ort not in ( :1, :2 ) ';
  str := str || ') ';
  str := str || 'where ';
  str := str || 'a.c_period = :3 ';

  execute immediate str
  using '700', '701', pPeriod;
...
In using part you can pass parameters.

No comments:

Post a Comment