Jason Lin 的資料庫

This happened when the remote procedure timestamp changed,
the local caller procedure will failed once.
Users complained this issue long time ago,
somehow IT team can't find a real automatic way to prevent this issue.
The workaround A: compile the local procedure too.
The workaround B: execute the remote procedure from local to update the timestamp.

A is simple, and the compile date (LAST_DDL_TIME) is changed,
but I don't want the compile date updated when the procedure has not been changed.
B is a little complex, and the procedure must have a way to execute wothout the business logic (like a bypass),
but the compile date will remain the same.

Both workarounds has one prerequisite that you must know the timestamp changed happened.
When the remote procedure is too complex, you might not know the the timestamp change happened.
For example the remote procedure used some procedures, and these procedures used some other procedures.
When any of these procedure is modified, the timestamp of all related procedures will be changed.
So pitiful IT members need a better solution to prevent customer complains.

Some day, this idea just occurred to me.
I have not find any solution like this on the internet yet.
Anyway, I post this on my blog to share this idea.

I'll use a simple case to demonstrate how to solve ORA-04062 timestamp changed exception.
If your program is complicated, you have to modify your program structure to fit this solution.

-- Sample Code: Local Caller --    
CREATE OR REPLACE PROCEDURE localCaller AS
  result varchar2(4000):='';
  n_jobNo number := '1';
BEGIN
  BEGIN
    remoteProcedure@remoteDbLink(result);
    IF 'OK' = result THEN
      -- actions : remote procedure result is success
    ELSE
      -- actions : remote procedure result is failure
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      IF 'ORA-04062' = substr(SQLERRM,1,9) THEN
        /*
        The sqlerrm is 'ORA-04062: timestamp of procedure 
        "remoteProcedure" has been changed.'
        When the caller procedure failed becaues ORA-04062, 
        schedule itselt to run again later.
        After this failure, the timestamp of remote procedure 
        has been updated by database automatically.
        */
        dbms_job.next_date(n_jobNo, SYSDATE+1/14440);
      ELSE
        -- handle other exceptions here
      END IF;
  END;
END;

-- Sample Code: Local Job --
begin
  sys.dbms_job.submit(job => :=job),
                     what => 'localCaller;'
		next_date => to_date('01-01-3000', 'mm-dd-yyyy'),
	         interval => 'sysdate+365000');
  commit;
end;