This discussion is archived
13 Replies Latest reply: May 19, 2010 5:53 AM by jl1997 RSS

dbms_scheduler.create_job

abcdxyz Newbie
Currently Being Moderated
Hi all

I am trying to use this to create a job in when button pressed trigger in forms 6i

sys.dbms_scheduler.create_job(
job_name => 'iiii',
job_type => 'EXECUTABLE',
job_action => '/prod1/glif/byer_pr/jobs/glif1sfpay.job' ,
number_of_arguments => 2,
start_date => NULL, --SYSDATE,
repeat_interval => NULL,
end_date => NULL, --'01/01/2006 02:00 AM', --SYSDATE,
job_class => 'DEFAULT_JOB_CLASS',
enabled => FALSE,
auto_drop => TRUE,
comments => NULL);

when i am compiling the form i am getting below Error
"Cannot pass NULL to a NOT NULL constrained formal parameter"

If i pass the parameter sysdate or any date to the start_date parameter then while compiling it gives me the error wrong number of arguments

Please let me know if we can use dbms_scheduler.create_job in forms 6i and if yes how can we use it ?

thanks in advance
Sachin
  • 1. Re: dbms_scheduler.create_job
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,

    <p>Read this article</p>

    Francois
  • 2. Re: dbms_scheduler.create_job
    Arunkumar Ramamoorthy Guru
    Currently Being Moderated
    Hi,

    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

    -Arun
  • 3. Re: dbms_scheduler.create_job
    abcdxyz Newbie
    Currently Being Moderated
    Is this 'dbms_scheduler.create_job' compatible with Forms 6i ?

    - Sachin
  • 4. Re: dbms_scheduler.create_job
    InoL Guru
    Currently Being Moderated
    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
  • 5. Re: dbms_scheduler.create_job
    744707 Newbie
    Currently Being Moderated
    Hello,

    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.

    Any suggestion...

    -Sachin...
  • 6. Re: dbms_scheduler.create_job
    frm40735 Journeyer
    Currently Being Moderated
    log in as sys and create synonym for the dbms_scheduler package.
  • 7. Re: dbms_scheduler.create_job
    InoL Guru
    Currently Being Moderated
    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>
    or
    grant create job to <schema owner>
  • 8. Re: dbms_scheduler.create_job
    Andreas Weiden Guru
    Currently Being Moderated
    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
  • 9. Re: dbms_scheduler.create_job
    abcdxyz Newbie
    Currently Being Moderated
    dbms_scheduler.create_job has the public grant to execute and the synonym also created in schema but not working....

    -Sachin...
  • 10. Re: dbms_scheduler.create_job
    InoL Guru
    Currently Being Moderated
    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.
  • 11. Re: dbms_scheduler.create_job
    748217 Newbie
    Currently Being Moderated
    as sys: grant scheduler_admin to <user>;
  • 12. Re: dbms_scheduler.create_job
    jl1997 Explorer
    Currently Being Moderated
    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
    IS
    vDate DATE := SYSDATE+1;
    BEGIN
    DBMS_SCHEDULER.CREATE_JOB(job_name=>DBMS_SCHEDULER.GENERATE_JOB_NAME('JOB$_')
    ,job_type=>'PLSQL_BLOCK'
    ,job_action=>'begin NULL; end;'
    ,start_date=> vDate
    ,enabled=>TRUE);

    END s_job;
    /
  • 13. Re: dbms_scheduler.create_job
    jl1997 Explorer
    Currently Being Moderated
    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.

    Jim

    ------
    CREATE OR REPLACE FUNCTION do_schedule_job(pPLSQL_Block VARCHAR2,pStart_Date DATE)
    RETURN BOOLEAN
    IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    vStartTimeStr VARCHAR2(40) := NULL;
    vTimeZone VARCHAR2(6) := NULL;
    BEGIN
    SELECT CAST(pStart_Date AS TIMESTAMP) INTO vStartTimeStr FROM DUAL;
    SELECT LTRIM(SUBSTR(SYSTIMESTAMP,-6)) INTO vTimeZone FROM dual;

    DBMS_SCHEDULER.CREATE_JOB
    (job_name => DBMS_SCHEDULER.generate_job_name('MY_JOB')
    ,job_type => 'plsql_block'
    ,job_action => pPLSQL_Block
    ,start_date => vStartTimeStr||' '||vTimeZone
    ,enabled => TRUE);

    COMMIT;

    RETURN(TRUE);

    EXCEPTION
    WHEN OTHERS THEN
    RETURN(FALSE);

    END do_schedule_job;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points