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
Is this 'dbms_scheduler.create_job' compatible with Forms 6i ?
Dbms_scheduler is a package since Oracle 10 database. Forms 6i has a much older pl/sql engine, so there might be a compatibility problem. Go for Arun's advise: put the procedure in the database.
SYSTIMESTAMP will probably not be recognised in Forms 6i.
Edited by: InoL on Jan 5, 2010 11:13 AM
I did the same but after doing this i am getting error' Ora-27486 Insufficient privileges' while calling from form. Even there is execute privilege is given. I am able to create the job from SQL-Plus/Toad but not able from form.
log in as sys and create synonym for the dbms_scheduler package.
Are you logging in with the same user in Forms and sqlplus? Is this the same user as the owner of the schema?
There are some privileges involved, like "create job":
grant create job to <user>
grant create job to <schema owner>
If you want to use DBMS_SCHEDULER inside a stored procedure, you will have to give an explicit GRANT EXECUTE ON DBMS_SCHEDULER TO THE_USER_WHO_OWNS_THE_STORED_PROCEDURE as SYS
dbms_scheduler.create_job has the public grant to execute and the synonym also created in schema but not working....
Grant to public doesn't work for any stored procedure that calls another procedure. You explicitly have to grant to the procedure owner, as indicated by Andreas.
as sys: grant scheduler_admin to <user>;
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