Skip to Main Content
  • Questions

  • Restartable Logic in a Batch Script(PLSQL)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anand.

Asked: September 24, 2011 - 2:45 am UTC

Last updated: September 25, 2011 - 11:53 am UTC

Version: 11G

Viewed 1000+ times

You Asked

Hello Tom

I have a question regarding restartable logic to be applied when i run a batch script.

Here is my question.

I have a PLSQL Batch script which transfers the data from Staging to Target tables(Around 10 tables) and runs on a daily basis through DBMS JOBS.

This script(Package) consists of 10 procedures and each procedure is responsible for moving the data from Staging to Target table.I would like to implement a restartable logic in this batch script so that whenever a failure is encountered in the batch process, i would like to restart the process from the point where it got failed.

For this, i have created a table named JOB_LOG with the following contents.

RUN_ID --- Unique
TABLE_NAME --- Table Name
RUN_STATUS --- C for Completed, F for Failure
RUN_DATE --- Run Date

Below are the sample rows.

RUN_ID TABLE_NAME RUN_STATUS RUN_DATE
------ --------- ---------- --------
1 Table1 C 22-Sep-11
2 Table2 C 22-Sep-11
3 Table3 F 22-Sep-11

So before executing the procedure i am checking whether the record exists for the particular table_name with the status C on the particular Run_date. If exists, i am skipping that procedure else i am calling that procedure and loading the required data.

COnsider the third row Table3 which got failed for some reason on 22-Sep-11. Lets say the issue is fixed the next day. On successful completion, i am updating the RUN_STATUS as C and RUN_DATE as SYSDATE for the row TABLE3.Ie,

3 Table3 C 23-Sep-11

So when i run the batch script for the next day(On 23-Sep-11), it checks for the RUN_STATUS C for the table TABLE3 and it is skipping that part.ACtually it should not skip.

Could you please provide me an approach to solve this?

Regards
Anand.S




and Tom said...

Make a new last step - that last step would delete the records from this table.

That way - until the job runs all of the way through - you know if a step has completed or failed. The run_data becomes "not relevant", the existence of a "C" record for a step is all that matters.

If the table is empty - all steps run.

If the table has a C record for Table1 - table1 is not done, you start at table2

And so on.

If you decide to restart the job from the beginning - perhaps because too much time has passed, just delete from the table and all steps will run.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot

Anand Srikanthan, September 25, 2011 - 2:13 am UTC

Thanks a lot for your response.

I did consider your idea by truncating the table. But what if i would like to keep history of runs?

Lets say, i would like to see the list of jobs completed for a particuar day.

Could you please shed light on this?

Regards
Anand Srikanthan
Tom Kyte
September 25, 2011 - 11:52 am UTC

I didn't say truncate, I said delete - which would let you MOVE the data as well.

It seems pretty darn straightforward to solve this problem doesn't it?

You have a table "current_job"

You have a table "history_of_jobs"

You move the data from current_job into history_of_jobs and then delete from current_job.

Now you have your history, and you have a simple solution to your current problem.


Is it a Typo?

Rajeshwaran, Jeyabal, September 25, 2011 - 11:11 am UTC

If the table has a C record for Table1 - table1 is not done, you start at table2


If the table has a C record for Table1 - table1 is **done**, you start at table2
Tom Kyte
September 25, 2011 - 11:53 am UTC

correct, thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library