Shulou(Shulou.com)06/01 Report--

Job Chains

The order in which the chain is created

Create chain object

Define steps in chain

Add rules

Enable chain

Create a job that points to this chain.

Create Chain Object

Lab description:

ENV:create table test_chain (id number)

Createtable test_chain1 (id number)

Createtable test_chain2 (id number)

Insert a piece of data into the step1:test_chain1 table

After the step2:step1 executes successfully, when there are more than two pieces of data in the test_chain, insert a piece of data in the test_chain2 table.

Begin

Dbms_scheduler.create_chain (chain_name= > 'my_chain1',rule_set_name= > null,evaluation_interval= > interval' 1' minute

Comments= > 'Chain with 30 minuteevaluation interval')

End

/

PL/SQL procedure successfully completed.

Evaluation_interval refers to the scan interval of chain rule, which is customized here. Scheduler scans chain rule when starting job chain and every time chain step is executed. Here, in addition to starting job chain and ending chain step, it will automatically scan every minute to determine the correctness of rule. This parameter is enabled in this experiment for a special case.

Define Chain Steps to create program

Begin

Dbms_scheduler.create_program (program_name= > 'my_program1',program_type= >' PLSQL_BLOCK'

Program_action= > 'begin insert intotest_chain1 values (1); commit;end;')

End

/

PL/SQL procedure successfully completed.

Begin

Dbms_scheduler.create_program (program_name= > 'my_program2',program_type= >' PLSQL_BLOCK'

Program_action= > 'begin insert intotest_chain2 values (2); commit;end;')

End

/

PL/SQL procedure successfully completed.

-- enable program

SQL > begin

2 dbms_scheduler.enable ('my_program1,my_program2')

3 end

4 /

Create step

Begin

Dbms_scheduler.define_chain_step (chain_name= > 'my_chain1',step_name= >' my_step1',program_name= > 'my_program1')

Dbms_scheduler.define_chain_step (chain_name= > 'my_chain1',step_name= >' my_step2',program_name= > 'my_program2')

End

/

PL/SQL procedure successfully completed.

Note: program and chain may not exist when defining step, but program must be guaranteed when starting chain

And chain exist.

Add Chain rules

Rule description:

Step1 direct execution

Step2 execution condition: step1 executes successfully, and (selectcount (*) from test_chain) > = 2

Begin

Dbms_scheduler.define_chain_rule (chain_name= > 'my_chain1',condition= >' TRUE',action= > 'startmy_step1',rule_name= >' rule_1')

End

/

PL/SQL procedure successfully completed.

Begin

Dbms_scheduler.define_chain_rule (chain_name= > 'my_chain1',condition= >': my_step1.state=''SUCCEEDED''and (select count (*) from test_chain) > = 2'

Action= > 'startmy_step2',rule_name= >' rule_1_2')

End

/

PL/SQL procedure successfully completed.

Enable Chain

Begin

Dbms_scheduler.enable ('my_chain1')

End

/

PL/SQL procedure successfully completed.

Create Chain job

Note: if you directly call run_chain to run, you don't need enablechain, but you need to create job.

Begin

Dbms_scheduler.create_job (job_name= > 'chain_job_1',job_type= >' CHAIN',job_action= > 'my_chain1'

Start_date= > sysdate,repeat_interval= > 'freq=minutely;interval=1',enabled= > TRUE)

End

/

Note: start job separately. Note that the parameter USE_CURRENT_SESSION= > false

SQL > begin

2 dbms_scheduler.run_job (job_name= > 'chain_job_1',USE_CURRENT_SESSION= > false)

3 end

4 /

SQL > selectjob_name,job_subname,status,req_start_date,actual_start_date fromuser_scheduler_job_run_details where job_name='CHAIN_JOB_1'

JOB_NAME JOB_SUBNAME STATUS REQ_START_DATE ACTUAL_START_DATE

-

CHAIN_JOB_1 STOPPED 16-JUN-16 03.44.03.000000 PM+08:00 16-JUN-16 03.44.03.588735 PM+08:00

CHAIN_JOB_1 MY_STEP1 FAILED 16-JUN-16 03.44.03.682508 PM+08:00 16-JUN-16 03.44.03.693946 PM+08:00

CHAIN_JOB_1 MY_STEP1 SUCCEEDED 16-JUN-16 04.17.43.335679 PM+08:00 16-JUN-16 04.17.43.436069 PM+08:00

Found that my_step2 is not running yet

SQL > select * from test_chain1

ID

-

one

My_program1 has written data

Conditions for manufacturing my_step2 start-up

SQL > insert into test_chainvalues (9)

1 row created.

SQL > /

1 row created.

SQL > commit

Commit complete.

SQL > selectjob_name,job_subname,status,req_start_date,actual_start_date fromuser_scheduler_job_run_details where job_name='CHAIN_JOB_1'

JOB_NAME JOB_SUBNAME STATUS REQ_START_DATE ACTUAL_START_DATE

-

CHAIN_JOB_1 STOPPED 16-JUN-16 03.44.03.000000 PM+08:00 16-JUN-16 03.44.03.588735 PM+08:00

CHAIN_JOB_1 MY_STEP2 SUCCEEDED 16-JUN-16 04.30.43.064558 PM+08:00 16-JUN-16 04.30.43.152496 PM+08:00

CHAIN_JOB_1 MY_STEP1 FAILED 16-JUN-16 03.44.03.682508 PM+08:00 16-JUN-16 03.44.03.693946 PM+08:00

CHAIN_JOB_1 MY_STEP1 SUCCEEDED 16-JUN-16 04.17.43.335679 PM+08:00 16-JUN-16 04.17.43.436069 PM+08:00

SQL > select * from test_chain2

ID

-

two

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.