Refactoring PL/SQL triggers - extract procedures

Question

Hello,

we have application where database contains large parts of business logic in triggers, with a update subsequently firing triggers on several other tables. I want to refactor the mess and wanted to start by extracting procedures from triggers, but can't find any reliable tool to do this. Using "Extract procedure" in both SQL Developer and Toad failed to properly handle :new and :old trigger variables.

If you had similar problem with triggers, did you find a way around it?

EDIT: Ideally, only columns that are referenced by extracted code would be sent as in/out parameters, like:

Example of original code to be extracted from trigger:

  .....
  if :new.col1 = some_var then
    :new.col1 := :old.col1
  end if
  .....

would become :

  procedure proc(in old_col1 varchar2, in out new_col1 varchar2, some_var varchar2) is
  begin
    if new_col1 = some_var then
      new_col1 := old_col1
    end if;
  end;
  ......
  proc(:old.col1,:new.col1, some_var);

Answer 1

This is not exactly the answer. I have not enough reputation to edit original question, obviously. The issue is that it is not a "refactoring" as we usually think of. Even when you'll create bunch of procedures from triggers, you'll need to make a proper framework to run them in order to achieve original functionality. I suspect that this will be a challenge as well.

As a solution proposal, I'd go with one python script, based on state machine (see http://www.ibm.com/developerworks/library/l-python-state.html for example). If you put strict definition of what should be translated and how, it will be easy to implement.

Answer 2

It sounds like you want to carry out transformations on PL/SQL source. To do this reliably, you need a tool that can parse PL/SQL to some kind of compiler data structure, pattern-match against that structure and make directed changes, and then regenerate the modified PL/SQL code.

The DMS Software Reengineering Toolkit is such a tool. It is parameterized by the programming language being translated; it has off-the-shelf front ends for many languages, including C, C++, C#, Java, COBOL and ... PL/SQL.