13 Replies Latest reply on May 19, 2010 12:53 PM by jl1997

    dbms_scheduler.create_job

    abcdxyz
      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çois Degrelle
          Hello,

          <p>Read this article</p>

          Francois
          • 2. Re: dbms_scheduler.create_job
            Arunkumar Ramamoorthy-Oracle
            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
              Is this 'dbms_scheduler.create_job' compatible with Forms 6i ?

              - Sachin
              • 4. Re: dbms_scheduler.create_job
                InoL
                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
                  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
                    log in as sys and create synonym for the dbms_scheduler package.
                    • 7. Re: dbms_scheduler.create_job
                      InoL
                      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
                        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
                          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
                            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
                              as sys: grant scheduler_admin to <user>;
                              • 12. Re: dbms_scheduler.create_job
                                jl1997
                                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
                                  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;
                                  /