Oracle job creation, Oracle Exception Handling

Run the command in the Command window.

Variable names after variable must be the same as those after job =>: job. Otherwise, the error "Not all variables are bound" will be reported. After successful execution, the job number is automatically generated by Oracle.

Generally, Oracle exception handling is added to the stored procedure when a job is called. Otherwise, the error cannot be found after a job error occurs.

1. Create a job(Note that next_date must be written! Time is required)

variable job1 number;begin  sys.dbms_job.submit(job => :job1,                      what => 'p_main3_main1;',                      next_date => to_date('01-02-2012 11:47:25', 'dd-mm-yyyy hh24:mi:ss'),                      interval => 'SYSDATE+4/86400/5');  commit;end;/

2. Oracle Exception Handling (during storage)

2.1 create an error log table first

create table t_sys_errors(e_user varchar2(100),e_date date,error_code number,error_message varchar2(255)) ;

2.2 exception records in the log table during storage

Declare error_code number; --> defines the variable, error_message varchar2 (255); --> 11g is 512, 10g is 255 begin... exception when others then rollback; error_code: = sqlcode; --> put the error code in the variable error_message: = sqlerrm --> put the error message into the variable and insert into errors (e_user, e_date, error_code, error_message) values (user, sysdate, error_code, error_message); --> put the error code and information in the log table end;