suhinrasheed
asked on
Control Mechanism while running PL/SQL Code
Hi
I want to place a monitoring mechanism like below and would appreciate if experts can throw how can i acheive this.
I have a pl/sql anonymous code which contains 4 procedures .
Each Procedure is doing some Insert,Update on a table say
Procedure A - Table X
Procedure B - Table Y
Procedure C - Table Z
Procedure D - Table M
I have a monitoring table called LOGGER with the structure
TABLENAME STARTTIME ENDTIME STATUS
Whenever one of my procedure finishes it inserts a record in this table say when Procedure A finishes it inserts a record with table name as X.
Usually when I run my program i run the pl/sql anonymous script in one shot so that all 4 procedures get triggered sequentially.
Now assume there is a case when Procedure A executed successfully but in middle of Insert in Procedure B there was a failure.In the latte case I dont
want to run my Procedure A again rather once I rectify the issue in Procedure B i want to run my master script itself but it should bypass procedure A
call and goto B directlyWhat I want to know is whetehr there is a way we can write some global code in pl/sql whereby my LOGGER table is referred by my
pl/sql code and my latte control need is achieived.
Also would like to know whether instead of anonymous block whether this entire mechanism can be achieved by creating a Oracle Package
I want to place a monitoring mechanism like below and would appreciate if experts can throw how can i acheive this.
I have a pl/sql anonymous code which contains 4 procedures .
Each Procedure is doing some Insert,Update on a table say
Procedure A - Table X
Procedure B - Table Y
Procedure C - Table Z
Procedure D - Table M
I have a monitoring table called LOGGER with the structure
TABLENAME STARTTIME ENDTIME STATUS
Whenever one of my procedure finishes it inserts a record in this table say when Procedure A finishes it inserts a record with table name as X.
Usually when I run my program i run the pl/sql anonymous script in one shot so that all 4 procedures get triggered sequentially.
Now assume there is a case when Procedure A executed successfully but in middle of Insert in Procedure B there was a failure.In the latte case I dont
want to run my Procedure A again rather once I rectify the issue in Procedure B i want to run my master script itself but it should bypass procedure A
call and goto B directlyWhat I want to know is whetehr there is a way we can write some global code in pl/sql whereby my LOGGER table is referred by my
pl/sql code and my latte control need is achieived.
Also would like to know whether instead of anonymous block whether this entire mechanism can be achieved by creating a Oracle Package
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
awking good idea.One doubt wat is the best way to generate runid while triggering the logger procedure call
ASKER
I have multiple procedures so I want dont want run id to be unique across the table ,combination of procedure name and runid can be unique in logger table
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
comment.txt