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  
suhinrasheedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01IT-specialistCommented:
1. Depending on the duration of your procedures I would only commit after succesfull completion of the masterscript and perform a rollback at any failure.
    After fixing the wrong procedure you can rerun the whole script.
2. If you want to use inbetween commits you can create a fairly general logger procedure. Since  you are logging the results of procedures and may be want to bypass procedures a would make it a procedure logging instead of a table logging.
    procedurename starttime endtime status
   If  you want to register incomplete procedure processing  (status = error)  you will have to rollback partional results (I you insert a record on table Y and there is an
   error on the next update of table Y the insert should be undone to be able to rerun procedure b) and then perform and commit the logging procedure.
   Oracle doesn't provide information on the procedure name that is current running so you should provide that information in your code

   declare
      v_procedurename varchar2(20) := 'Procedure B';
      ..
   begin
      logg_procedure(v_procedurename,'START');
      COMMIT; -- for fixation of logger *1
      ..
       your_code;
       ..
      logg_procedure(v_procedurename,'SUCCESS');
      COMMIT;  -- for fixation of your table changes including logger *1 *2
   exception when others then
      ROLLBACK;  -- to undo partial table changes within the procedure
      logg_procedure(v_procedurename,'ERROR');
      COMMIT;  --  for fixation of logger *1
   end;

PROCEDURE LOGG_PROCEDURE(p_procedurename varchar2, p_status varchar2)
IS
BEGIN
   CASE p_status
   WHEN 'START'
      INSERT INTO LOGGER
         (PROCEDURENAME, STARTTIME, ENDTIME,STATUS)
      VALUES
        (p_procedurename, sysdate, null,p_status);
   ELSE
      UPDATE LOGGER
      SET STATUS = p_status
      WHERE status = 'START';
      IF SQL%ROWCOUNT = 1 THEN
          NULL;
      ELSE
          RAISE_APPLICATION_ERROR(-20000,'More then 1 logger-record with status START'); *3
   END CASE;
END;

*1)  to avoid the commit of logger here see the concept of using a discrete transaction
*2)  with the use of a discrete transaction  the commit should stay here to commit the regular table changes
*3)  if the main procedure should be able to run more then 1 at a time : you will need another tracking mechanism that identifies a "main run"


anonymus block to package

DECLARE  -- procedure a
    ...
BEGIN
    ...
END;


CREATE OR REPLACE PACKAGE  yourpackage
IS  

PROCEDURE PROCMAIN;
PROCEDURE PROCA;  -- if you don't want to able to execute proca standalone comment it out here
PROCEDURE PROCB;
PROCEDURE PROCC;
PROCEDURE PROCD;


CREATE OR REPLACE PACKAGE  BODY yourpackage
IS  

PROCEDURE PROCA
IS   --  naam the procedure and substitute the declare  by IS
BEGIN
  ..
END;

PROCEDURE PROCMAIN
IS
  v_next_program varchar2(20) := 'PROGA';
BEGIN
    --
    BEGIN
      SELECT procedurename
      into v_next_program
      FROM LOGGER
      WHERE STATUS = 'ERROR';

    UPDATE LOGGER
    SET STATUS = 'ERROR/RESTARTED'
    WHERE STATUS = 'ERROR';

      EXCEPTION WHEN NO_DATA_FOUND THEN
          NULL;
      END;
       
   -- add your self there must be no logger with  status = 'START' and at most 1 with 'ERROR'

    IF v_next_program = 'PROGA' THEN
        PROGA ;
        v_next_program := 'PROGB';
    END IF;

    IF v_next_program = 'PROGB' THEN
        PROGB ;
        v_next_program := 'PROGC';
    END IF;
    .. etc

   END;
END;  



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
See attached.
comment.txt
suhinrasheedAuthor Commented:
awking good idea.One doubt wat is the best way to generate runid while triggering the logger procedure call
suhinrasheedAuthor Commented:
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
awking00Information Technology SpecialistCommented:
See attached.
comments.txt
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.