This content has been marked as final. Show 13 replies
start_date and end_date parameters are not of DATE datatype but of TIMESTAMP datatype.
You may want to pass systimestamp instead of sysdate for them. It would work.
On the related notes, instead of calling the create_job directly from forms, it is better to create a procedure in your database and call it from forms.
Reference : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1000363
I am having a problem with dbms_scheduler.create_job also.
Using forms6i, on oracle 10g database. I created a database procedure which calls dbms_scheduler.create_job.
When the database procedure is called from SQLPlus - it works Ok
When the database procedure is invoked from the oracle form, the job executes immediately even when start_date is in the future.
I am logged in as the owner in both tests. I have also granted the following privileges explicitly:
GRANT CREATE ANY JOB TO <owner login>
GRANT EXECUTE ON DBMS_SCHEDULER TO <owner login>
GRANT SCHEDULER_ADMIN TO <owner login>
Here is basically what my database procedure looks like:
CREATE OR REPLACE PROCEDURE s_job
vDate DATE := SYSDATE+1;
,job_action=>'begin NULL; end;'
The reason the job was running immediately was because the value I was passing for "start_date" did not include the timezone, so it was defaulting the timezone to +00:00. Our timezone is -04:00 so anything scheduled to run within 4 hours of the current time was being run immediately.
Finally, I got it to work by building a timestamp string and concatenating the local timezone
Hope this helps someone else.
CREATE OR REPLACE FUNCTION do_schedule_job(pPLSQL_Block VARCHAR2,pStart_Date DATE)
vStartTimeStr VARCHAR2(40) := NULL;
vTimeZone VARCHAR2(6) := NULL;
SELECT CAST(pStart_Date AS TIMESTAMP) INTO vStartTimeStr FROM DUAL;
SELECT LTRIM(SUBSTR(SYSTIMESTAMP,-6)) INTO vTimeZone FROM dual;
(job_name => DBMS_SCHEDULER.generate_job_name('MY_JOB')
,job_type => 'plsql_block'
,job_action => pPLSQL_Block
,start_date => vStartTimeStr||' '||vTimeZone
,enabled => TRUE);
WHEN OTHERS THEN