hi
does anyone know about job package. I want to know how can i use it to do following:
I want to write a procedure in oracle which will monitor a table which
contains a date field (containing a date>= system date). When the date
specified in the date field matches with the system date the row
containing the date should be deleted from the table.
As you must have noticed that the procedure should run 24x7 and
continuously monitor the table.
How to do that?
Please help

use the DBMS_JOB package, which is documented here:

http://download-west.oracle.com/docs...job.htm#999107

i am new to using these packages.
Could you please tell me how to use the DBMS_JOB package to do the aforementioned task.
thanks

This will set up a job to run the stored procedure called myproc immediately, and then every minute thereafter (1 minute = 1/1440 days). Change the frequency to whatever value you require.

Put whatever you need to do in the stored procedure called myproc (well, probably called something more sensible than that).

declare
job integer;
begin
dbms_job.submit( job, 'myproc;', sysdate, 'sysdate+1/1440' );
commit;
end;
/

You need to check that your database is configured to run job queues:

select name, value from v$parameter
where name like 'job_queue%';

You should see a value for job_queue_processes > 0 otherwise no jobs will run. job_queue_interval is the number of seconds Oracle waits between running jobs. If you schedule your job to run more frequently than that, it won't.

it did not work. i did the following to check the package
SQL> create table temp
2 (
3 current_date date
4 );

Table created.

SQL> create or replace procedure temp_precedure
2 is
3 begin
4 INSERT INTO TEMP VALUES(SYSDATE);
5 END;
6 /

Procedure created.
SQL> declare
2 job integer;
3 begin
4 dbms_job.submit(job,'temp_precedure;',sysdate,'sys date+1/1440');
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

I monitored the table for 5 min and no rows where added to the table.
When i manually executed the procedure it successfully added new row to the table.
also i got following error
SQL> select name, value from v$parameter where name like 'job_queue%';
select name, value from v$parameter where name like 'job_queue%'
*
ERROR at line 1:
ORA-00942: table or view does not exist


I was working on a remote database on the intranet. I have a student login ( I am a student). I am using Oracle 8i Enterprise Edition installed on Sun Solaris. I access it on win2kpro using oracle 8i enterprise ed. client(sqlplusw).

please help
thanks

You'll need to contact your DBA about this.

Thanks it worked. i did not have the priveledge of issuing a job.
But what about the following:

SQL> select name, value from v$parameter where name like 'job_queue%';
select name, value from v$parameter where name like 'job_queue%'
*
ERROR at line 1:
ORA-00942: table or view does not exist

I searched oracle 8i complete reference and could not find the aforementioned table.
Thanks

Hi andrewst,

Is there any processing time taken by oracle which increases the interval by a second?

I kept the interval as sysdate+1/1440

And the time stamp of the records inserted by procedure in job queue is

TO_CHAR(START_DATE,'
--------------------
15-dec-2003 19:15:56
15-dec-2003 19:16:57
15-dec-2003 19:17:59
15-dec-2003 19:19:00
15-dec-2003 19:20:02
15-dec-2003 19:21:03
15-dec-2003 19:22:05

As we can see at every step, its either 61 seconds of 62 seconds. What could be the reason?

Thanks,

The reason would be that your job takes 1-2 seconds to run, so that by the time it evaluates sysdate+1/1440, sysdate is 1-2 seconds later than the start of this run.

You can compensate for that like this:

trunc(sysdate,'mi')+1/1440

Now it will always run exactly on the minute. Of course, if the job ever takes more than 1 minute to run then it will skip a run.