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.
Microsoft AX 2012, X++, C#, SQL server, SSRS, Java, JavaFX, Oracle, PL/SQL, Delphi - codes examples, step-by-step tutorials, experiences.
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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment