SQL proc Diagram generating Software of a Programm Flow.

Question

I have a couple of very long procs in Oracle 2000+ lines with lots of calls. And I'd like to generate programm flow Diagram (algorithm) for better understanding of the process for further Refactoring.

It's not the code I wrote so I dont know the logic enough.

What would you advice to do in this case? I tried to draw a text-like flow but it takes lots of time and still hard to cover all the logic for understanding.

The best approach I see would be flow chart generated from SQL proc with links to "jump" between code and chart.

UPD: Found couple of software doing the same:

1. ClearSQL - makes CRUD Diagramms, Call Map, and Flow Chart.

2. Quest SQL Navigator Expert (using it now): it has Outline ( makes code flow with ability to collapse-expand blocks of code - really cool one! ), Code Explorer (enumerates all func, params with links ti SQL text - just in interface) features

Answer 1

I doubt that such tool, even if it exists could help better understanding. I think, time of big flow-charts is over.
I would advice to understand logic with step-by-step refactoring:
iteratively extract parts of procedure to smaller procedures and add tests.

Answer 2

There is a product, Code Visual to Flowchart, which can take code in various languages and do something like what you're describing. Unfortunately though Oracle doesn't appear to be in the list of supported languages, but Microsoft TSQL is; maybe you could at least translate your proc from Oracle into MS and use this to roughly visualize your proc's flow.

Failing that burnall's suggestion sounds like the best way to go, essentially divide and conquer.