One Time Immediate Job in Oracle

One Time Immediate Job can be created by using "dbms_job" and "dbms_scheduler" both.

1) One Time Immediate Job using dbms_job
  declare
    l_jobid number := null;
  begin
    dbms_job.submit 
    (
      job       =>  l_jobid,
      what      =>  'sp_proc;',
      next_date =>  sysdate,
      interval  =>  null
    );
    commit;
  end;
  /

Always remember to issue a COMMIT statement immediately after dbms_job.submit.


2) One Time Immediate Job using dbms_scheduler
  begin
    dbms_scheduler.create_job 
    (  
      job_name      =>  'One_Time_Job',  
      job_type      =>  'PLSQL_BLOCK',  
      job_action    =>  'begin sp_proc; end;',  
      start_date    =>  sysdate,  
      enabled       =>  TRUE,  
      auto_drop     =>  TRUE,  
      comments      =>  'one-time job');
  end;
  /



Related Posts:
- dbms_scheduler repeat_interval tip with create_schedule
- How to connect SQLPlus without tnsnames.ora
- SQL Interview Question Answers
- Oracle Database 12c New Features for Developers

4 comments:

  1. Hi, can anyone tell me why user_tab_cols is showing an additional non existing column name?
    When I queried a table say ex. select COLUMN_NAME from user_tab_cols where table_name='LYT_CYCLE_SUMMARY'
    I am getting SYS_NC00011$,OPENING_PTS_BAL_N,CLOSING_PTS_BAL_N,CYL_SUMM_STATUS_V.......
    14 columns when there is only 13 columns. Can anyone explain why is this happening? SYS_NC00011$ is the additional column im getting and its data type is shown as RAW and it doesn't have a COLUMN_ID.

    ReplyDelete
    Replies
    1. use user_tab_columns
      https://forums.oracle.com/thread/310121

      Delete
  2. Thanks for posting this, I never thought about using Scheduler for a one-time job, but I needed to twice last week! Talk about putting new knowledge to use quickly, your post was very timely.

    ReplyDelete
  3. Ofcourse , It is nice posting about oracle jobs .

    ReplyDelete