The code generator takes the Apex generated table API package as the basis, not the table definition. I don't know if Apex can generate code for every column data type. If Apex doesn't generate code for some column, the following code generator won't either.
Before generating the instead of triggers, you have to generate the package that lies on top of the apex API package.
There are some constants in the declaration section of this script that you have to modify. An important one is v_execute. When set to TRUE, the generated CREATE PACKAGE code will be executed in the database.
set scan off
declare
-- Create a custom package that call table API's that are generated using the Apex table API generator
-- (in SQL Workshop -> Methods on Tables).
-- Modify this package, instead of the Apex generated package, to add business rules.
-- Change these constants before generating
-- Table on which view is based
v_base_table constant varchar2(30) := 'emp';
-- Name of the Apex generated package
v_pkg constant varchar2(30) := 'pkg_emp';
-- Run execute immediate for generated code or not
v_execute constant boolean := false;
-- Apex generated API prefixes
v_upd_procedure constant varchar2(30) := 'UPD_';
v_ins_procedure constant varchar2(30) := 'INS_';
v_del_procedure constant varchar2(30) := 'DEL_';
-- For code formatting
v_indent constant integer := 3;
cr constant varchar2(10) := chr(10);
v_body varchar2(32767);
cursor c_args(b_procedure in varchar2) is
select ua.sequence
,lpad(' ',3*v_indent)||rpad(lower(ua.argument_name),20)||' '||
lower(ua.in_out)||' '||lower(ua.data_type)||','||cr code
from user_arguments ua
where ua.object_name = upper(b_procedure||v_base_table)
and ua.package_name = upper(v_pkg)
order by 1
;
cursor c_bind(b_procedure in varchar2) is
select ua.sequence
,lpad(' ',3*v_indent)||rpad(lower(ua.argument_name),20)||' => '||
lower(ua.argument_name)||','||cr code
from user_arguments ua
where ua.object_name = upper(b_procedure||v_base_table)
and ua.package_name = upper(v_pkg)
order by 1
;
procedure create_procedure_spec (i_procedure in varchar2)
is
begin
v_body := v_body || lpad(' ',v_indent)||'procedure '||
lower( i_procedure||v_base_table||' (' )||cr;
-- Procedure arguments
for r in c_args(i_procedure)
loop
v_body := v_body || r.code;
end loop;
v_body := rtrim(v_body,cr||',')||cr||lpad(' ',v_indent)||');';
end create_procedure_spec;
procedure create_procedure_body (i_procedure in varchar2)
is
begin
v_body := v_body || lpad(' ',v_indent)||'procedure '||
lower( i_procedure||v_base_table||' (' )||cr;
-- Procedure arguments
for r in c_args(i_procedure)
loop
v_body := v_body || r.code;
end loop;
-- Procedure body
v_body := rtrim(v_body,cr||',')||cr||lpad(' ',v_indent)||') is' || cr ||
lpad(' ',v_indent) || 'begin'||cr ||
lpad(' ',2*v_indent) || '-- Add business rules code here before calling table API.' || cr ||
lpad(' ',2*v_indent) || 'null;' || cr || cr ||
-- Call to Apex generated API
lpad(' ',2*v_indent)||lower( v_pkg||'.'||i_procedure||v_base_table||' (' )||cr;
-- Bind procedure arguments to Apex generated API arguments
for r in c_bind(i_procedure)
loop
v_body := v_body || r.code;
end loop;
v_body := rtrim(v_body,cr||',')||cr||lpad(' ',2*v_indent)||');'||cr;
v_body := v_body || lpad(' ',v_indent) || 'end '||lower( i_procedure||v_base_table)||';';
end create_procedure_body;
begin
/*---- Package specification ----*/
v_body := 'create or replace package '||lower(v_pkg)||'_br is' || cr ||
'-- Package generated on: ' || to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') || cr ||
'-- Manually add programmer defined code before call to table API.' || cr ||
'-- Date Author Remarks' || cr ||
'-- =========== ====== =======================================================' || cr ||
'-- ' || to_char(sysdate,'dd-mon-yyyy') || ' Initially generated package'|| cr || cr
;
-- Update procedure
create_procedure_spec (v_upd_procedure);
-- Insert procedure
v_body := v_body || cr || cr;
create_procedure_spec (v_ins_procedure);
-- Delete procedure
v_body := v_body || cr || cr;
create_procedure_spec (v_del_procedure);
-- End package specification
v_body := v_body || cr || 'end;';
dbms_output.put_line(v_body);
if v_execute then
execute immediate v_body;
end if;
/*---- Package body ----*/
v_body := null;
v_body := 'create or replace package body '||lower(v_pkg)||'_br is'||cr;
-- Update procedure
create_procedure_body (v_upd_procedure);
-- Insert procedure
v_body := v_body || cr || cr;
create_procedure_body (v_ins_procedure);
-- Delete procedure
v_body := v_body || cr || cr;
create_procedure_body (v_del_procedure);
-- End package body
v_body := v_body || cr || 'end;';
dbms_output.put_line(v_body);
if v_execute then
execute immediate v_body;
end if;
end;
The code for generating the instead of triggers is similar, and so are the constants in the declaration section that you have to set.
set scan off declare -- Create instead of triggers on a view that is defined as "create view as select * from" -- The instead of triggers call table API's that are generated using the Apex table API generator -- (in SQL Workshop -> Methods on Tables) -- Change these constants before generating -- View for which you are creating the instead of triggers v_view constant varchar2(30) := 'emp_v'; -- Table on which view is based v_base_table constant varchar2(30) := 'emp'; -- Name of the Apex generated package v_pkg constant varchar2(30) := 'pkg_emp'; -- Run execute immediate for generated code or not v_execute constant boolean := false; -- Argument in API that does not correspond to a column v_non_column constant varchar2(30) := 'P_MD5'; -- Apex generated API prefixes v_upd_procedure constant varchar2(30) := 'UPD_'; v_ins_procedure constant varchar2(30) := 'INS_'; v_del_procedure constant varchar2(30) := 'DEL_'; -- For code formatting v_indent constant integer := 3; cr constant varchar2(10) := chr(10); v_trigger varchar2(32767); v_body varchar2(32767); cursor c_args (b_procedure in varchar2) is select ua.sequence ,lpad(' ',3*v_indent)||rpad(lower(ua.argument_name),20)||' => '|| decode(b_procedure,v_del_procedure,':old.',':new.')|| substr(lower(ua.argument_name),3)||','||cr code from user_arguments ua where ua.object_name = upper(b_procedure||v_base_table) and ua.package_name = upper(v_pkg) and ua.argument_name <> v_non_column union select ua.sequence ,lpad(' ',3*v_indent)||rpad(lower(ua.argument_name),20)||' => '||'null'||','||cr code from user_arguments ua where b_procedure = v_upd_procedure -- UPD procedure has non-column argument and ua.object_name = upper(b_procedure||v_base_table) and ua.package_name = upper(v_pkg) and ua.argument_name = v_non_column order by 1 ; procedure create_procedure_body (i_procedure in varchar2) is begin v_body := lpad(' ',v_indent)|| lower( v_pkg||'_br'||'.'||i_procedure||v_base_table||' (' )||cr; for r in c_args(i_procedure) loop v_body := v_body || r.code; end loop; v_body := rtrim(v_body,cr||',')||cr||lpad(' ',v_indent)||');'; end create_procedure_body; begin -- Instead of update create_procedure_body(v_upd_procedure); v_trigger := 'create or replace trigger iur_'||lower(v_view)||cr ||'instead of update on '||lower(v_view)||cr ||'for each row'||cr ||'begin'||cr ||v_body||cr ||'end;'; dbms_output.put_line(v_trigger); if v_execute then execute immediate v_trigger; end if; -- Instead of insert create_procedure_body(v_ins_procedure); v_trigger := 'create or replace trigger iir_'||lower(v_view)||cr ||'instead of insert on '||lower(v_view)||cr ||'for each row'||cr ||'begin'||cr ||v_body||cr ||'end;'; dbms_output.put_line(v_trigger); if v_execute then execute immediate v_trigger; end if; -- -- Instead of delete create_procedure_body(v_del_procedure); v_trigger := 'create or replace trigger idr_'||lower(v_view)||cr ||'instead of delete on '||lower(v_view)||cr ||'for each row'||cr ||'begin'||cr ||v_body||cr ||'end;'; dbms_output.put_line(v_trigger); if v_execute then execute immediate v_trigger; end if; end;
No comments:
Post a Comment