Wednesday, February 9, 2011

Apex security and updatable views (2)

In my previous post, I indicated that the code for table API's and instead of triggers can easily be generated.

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