job为什么就是建立不起来？请指教。

Question

begin
  sys.dbms_job.submit(job => :job,
                      what => 'declare
                                 mydate date;
                               begin
                                 select sysdate
                                   into mydate
                                   from dual;
                               end;',
                      next_date => to_date('25-03-2005', 'dd-mm-yyyy'),
                      interval => 'null');
  commit;
end;
/

Answer 1

what 段需要满足pl/sql 标准

Answer 2

begin
dbms_job.submit(:job_no,'PRO_TRF_I_PTORDM02_AUTO(TO_CHAR(SYSDATE,''YYYYMMDDHH24MISS''));',TO_DATE(TO_CHAR(sysdate,'YYYYMMDD')||'161001','YYYYMMDDHH24MISS'),'SYSDATE+1');
COMMIT;
END;
/

Answer 3

dbms_job.submit(:job,'myprocedure;',to_date('24-03-2005 14:27:03', 'dd-mm-yyyy hh24:mi:ss'),'sysdate + 1/1440');

Answer 4

dbms_job.submit(:job,'myprocedure;',to_date('24-03-2005 14:27:03', 'dd-mm-yyyy hh24:mi:ss'),'sysdate + 1/1440');

Answer 5

begin
  sys.dbms_job.submit(job => :job,
                      what => 'declare
                                 mydate date;
                               begin
                                 select sysdate
                                   into mydate
                                   from dual;
                               end;',
                      next_date => to_date('25-03-2005', 'dd-mm-yyyy'),
                      interval => 'null');
  commit;
end;报错说：ORA-010008 并非所有变量都已经关联

Answer 6

一个简单例子： 创建测试表
SQL> create table a(a date);表已创建。创建一个自定义过程
SQL> create or replace procedure test as
  2  begin
  3  insert into a values(sysdate);
  4  end;
  5  /过程已创建。创建JOB
SQL> variable job1 number;
SQL> 
SQL> begin
  2  dbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440');　　--每天1440分钟，即一分钟运行test过程一次
  3  commit;
  4  end;
  5  /PL/SQL 过程已成功完成。运行JOB
SQL> begin
  2  dbms_job.run(:job1);
  3  end;
  4  /PL/SQL 过程已成功完成。SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;时间
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24删除JOB
SQL> begin
  2  dbms_job.remove(:job1);
  3  end;
  4  /PL/SQL 过程已成功完成。

Answer 7

1. create jobsCREATE TABLE job_table (
now DATE);CREATE OR REPLACE VIEW job_view AS
SELECT TO_CHAR(now, 'DD-MON-YYYY HH:MI:SS')
FROM job_table;CREATE OR REPLACE PROCEDURE do_job ISBEGIN
   INSERT INTO job_table
   (now)
   VALUES
   (SYSDATE);
   COMMIT;END;
/CREATE OR REPLACE PROCEDURE job_call ASJobNo user_jobs.job%TYPE;BEGIN
   dbms_job.submit(JobNo,
                  'begin do_job; end;',
                   SYSDATE,
                  'SYSDATE + 36/86400');
   COMMIT;
END;
/exec job_callSELECT * FROM job_view;SELECT job, next_date, next_sec
FROM dba_jobs;2. remove jobsselect job from user_jobs
job
----
22begin
 dbms_job.remove('22');
 end;

Answer 8

子句 what => 'myprocedure', 要加分号 what=> 'myprocedure;'