Oracle SQL Scheduled Jobs - An Interesting Approach

 

Oracle SQL Scheduled Jobs

A DB Scheduler is the best way to automate any backend database job.

For instance, if you want to process the persisted data using and stored procedure, in the background mode and do want to bother about the end to end start and stop of the process, a database job can be scheduled which would invoke the stored procedure, which further would invoke the tables' data, process the data and persist the data in the formatted/ processed conditioned state in the target tables.

Some common usage of such pipelines is to process the dashboard numbers per the populated data.

This blog-post would now walk you through the simple process involved in implementing a basic scheduled database pipeline, and would be using Oracle SQL as the technology choice.

1. Check if you have create table access, per result of the following sql:

CREATE TABLE abc_test  
( id number(10),  
  name varchar2(50)
);  
commit;

2. Check if the table is created:

    select * from abc_test;

-- should return empty resultset if table exists

3. Create a stored procedure to do the activity required (like populate data in the table):

    create or replace procedure abc_test_proc as
        begin 
            insert into abc_test(id, name) values (1,'first_test');
            commit;
        end;
    commit;

4. Create a scheduled job, that will invoke the above created stored proc. at a specified timestamp:

5. Validate the data in the table: