Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_JOB Interval Question

Re: DBMS_JOB Interval Question

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Wed, 23 Aug 2000 23:16:53 -0500
Message-ID: <39A4A1B4.C1309373@ntsource.com>

It is possible to use a user-defined function as the interval, but you will have to grant execute on that function to sys before it will get executed.

You can later revoke execute of the function from sys, without affecting the performance of the job. (If someone understands why this occurs, please post and send me an email!)

   For example (NT, Oracle 8.1.6), I create the following table that a job will use to store a record:

SQL> create table testtable (n date);
Table created.

   Now I create a function that will insert the current date into the table every minute:

SQL> create or replace function mynewdate return date   2 is
  3 begin
  4 return sysdate + 1/1440;
  5 end;
  6 /
Function created.

   Finally, I create a job that uses the user-defined function as the interval:

SQL> declare
  2 x binary_integer;
  3 begin
  4 dbms_job.submit(x,'insert into testtable values (sysdate);',sysdate,'mynewdate');
  5 dbms_output.put_line(to_char(x));   6 end;
  7 /
5
PL/SQL procedure successfully completed.

   All seems to be fine, except that after waiting a reasonable time, nothing gets inserted into the table:

SQL> select to_char(n,'HH:MI:SS') from testtable; no rows selected

   However, if I grant execute on mynewdate to sys, then results start being inserted into the table:

SQL> grant execute on mynewdate to sys;
Grant succeeded.

SQL> select to_char(n,'HH:MI:SS') from testtable;

TO_CHAR(



10:58:03

   Figuring I could perhaps stop the execution of the job by revoking execute on mynewdate from sys, I find that I can't:

SQL> revoke execute on mynewdate from sys; Revoke succeeded.

SQL> select to_char(n,'HH:MI:SS') from testtable;

TO_CHAR(



10:58:03
10:59:05

   The records continue to be generated.

   Frank Hubeny

salman_z_g_at_my-deja.com wrote:

> Its mentioned in the DBMS_JOB documentation that:
> "The interval parameter must evaluation to a time in the future"
>
> So,
>
> Is this possible to use a user-defined function that returns time for
> interval parameter when assigning to a job?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Aug 23 2000 - 23:16:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US