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.
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.
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;