BIK Information Services, Inc.

Oracle PL/SQL snippets
Submitting a job to survive a dblink being down
Problem at hand:

A system must submit a PL/SQL job that is supposed to either read from or write to an external database. The job must run only once but if the external system is down it must keep retrying until it succeeds.

Additional consideration:
- the solution must allow for multiple attempts to submit this job, but if the job is still waiting no need to submit it the second time around.

Explanation:
The problems with jobs like this is that mentioning a table with a dblink when the database in a link is down would make the job syntaxically invalid rendering the entire code broken.

Solution 

CREATE OR REPLACE PACKAGE EXTERNAL_INTERFACES AS 
   DEBUG VARCHAR2(10) := 'NO'; 
   PROCEDURE SUBMIT_ONCE(what IN VARCHAR2, when2run IN VARCHAR2
	, retryinminutes IN NUMBER DEFAULT 1); 
END EXTERNAL_INTERFACES; 
/ 

CREATE OR REPLACE PACKAGE BODY EXTERNAL_INTERFACES AS 
PROCEDURE SUBMIT_ONCE(what IN VARCHAR2, when2run IN VARCHAR2
	, retryinminutes IN NUMBER DEFAULT 1) AS 
        jobno INTEGER; 
        what1 VARCHAR2(4000); 
        retry VARCHAR2(4000); 
BEGIN 

what1 := 
    'BEGIN' 
	|| ' EXECUTE IMMEDIATE '''|| REPLACE(what, '''','''''') || '''; ' 
	|| ' next_date := NULL; ' 
        || ' EXCEPTION WHEN OTHERS THEN ' 
	|| ' NULL; ' 
        || ' END; ' 
; 

retry :='SYSDATE + '|| retryinminutes ||'/1440'; 
BEGIN 
      SELECT job INTO jobno FROM user_jobs WHERE what = what1; 
EXCEPTION WHEN OTHERS THEN 
     jobno := 0; 
END; 
IF (jobno = 0) THEN 
   DBMS_JOB.SUBMIT (jobno,what1, TO_DATE(when2run,'YYYYMMDD HH24:MI:SS'), retry, true); 
END IF; 

END SUBMIT_ONCE; 

END EXTERNAL_INTERFACES; 
/

Testing 
begin 
  DECLARE 
  jobno INTEGER :=0; 
  job_def VARCHAR2(4000); 
  when1s VARCHAR2(1000) := TO_CHAR(SYSDATE+1/1440,'YYYYMMDD HH24:MI:SS'); 
begin 

job_def := 
	  'begin declare i INTEGER;'
	  ||' begin select count(*) INTO i from something@dblink; end; end;' 

external_interfaces.submit_once(what=>job_def,when2run=>when1s); 
commit; 
end; 
end;

check whether the job is in the queue 
SELECT sysdate,uj.* FROM user_jobs uj

remove job from the queue
dbms_job.REMOVE(47); end;

We hope you found this snippet useful.
If so drop us a note to Boris Knizhnik  borisk@bikinfo.com.
We appreciate any links to this site as they show your appreciation and work as free ads.


Last modified on Saturday, 26-Feb-2005 08:07:06 EST


Back to BIK Information Services Home page Back to BIK Information Services home page