CODEWITHSUNDEEP
oracle-databaseplsql
Ray Wong
Ray Wong

Reputation: 13

Terminate a running sub procedure call

I have a procedure which will call another procedure, but sometime the sub-procedure takes more than 2 mins or more to complete. I trying to find a way to:

Is there any way to do achieve this?

Caller Function :

 IF .... THEN ...
   l_result := PKG_B.TRXN_PROC (PI_QUOT_NUM => pi_quot_num );    
END IF;

Upvotes: 1

Views: 80

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

One option might be to run the procedure as a background job using dbms_scheduler, sleep for 20 seconds and then terminate the job.

dbms_scheduler.run_job('MY_JOB_NAME');
DBMS_LOCK.sleep(seconds => '20');
SELECT STATE INTO v_state FROM USER_SCHEDULER_JOBS
   WHERE JOB_NAME = 'MY_JOB_NAME';
IF v_state = 'RUNNING' THEN
   dbms_scheduler.STOP_JOB(job_name=>'MY_JOB_NAME',force=>true);
END IF;

This is just to give you an idea and not a fully functional code. It is recommended to add further checks/ exceptions if required. It's also advisable to check every second in a loop or so instead of waiting for full 20, the choice is with you.

Following grants may be required to the user running it.

grant create job , manage scheduler to your_user;
grant execute on dbms_lock to your_user;

Upvotes: 4

Related Questions