I need a little bit of advice. Let me start by saying that I am fairly new to SQL and PL/SQL, so dont be too harsh ;)
I am going to provide a little bit of background here in case my approach could be improved on this.
The situation is that I have an app that allows users to create Processes as a sequence of steps from Start->Finish. There may be dozens of steps with multiple paths between the start and finish steps of the process. Each step has an estimated duration in # of days, so some Processes may take 10, 20 or 60 days to complete etc depending on the number of steps, their relative durations and the available paths between the first and last steps.
My objective has been to create a report to show the maximum delay from any step in the process to the finish step.
To do this, I have created a PL/SQL procedure that accepts three parameters Process ID, Start Step ID and Finish Step ID.
The procedure is recursive it starts with the Finish Step of the given process and progresses towards the Start step, calculating the maximum delay for all steps along the way.
Currently I use five DB tables (Oracle 10g R2).
Table 1: End Result (three columns of Numbers)
Table2 5: Temporary tables (all have between 3 and 6 columns of numbers)
This Procedure currently works fine for 1 process at a time.
However the report may be called concurrently by several users which would lead to inconsistent results. Furthermore, each time I call this procedure I first truncate the temporary tables.
So this is where I need the advice&
I have been trying to read up on several possibilities including global temporary tables, collections, records and objects etc. and now that my mind feels saturated with this maze of information I would really appreciate a pointer in the right direction.
What makes it difficult for me is that the procedure is recursive, so any structure (GTT, array) needs to be accessible on each call.
Any (constructive) advice would be most welcome.
If possible, please give me some examples of the structure and syntax or point me to a URL.