pl/sql job package

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 helpuse the DBMS_JOB package, which is documented here:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_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
Originally posted by andrewst
use the DBMS_JOB package, which is documented here:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_job.htm#999107|||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
Originally posted by andrewst
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.|||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
Originally posted by andrewst
You'll need to contact your DBA about this.|||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,

Originally posted by andrewst
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.|||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.

PK's

I just started a new job and the data warehouse was constructed a bit
different than what I have seen.
Some of the dimension tables had so many FK's that they had to create a view
on top of 3 dimension tables to simulate it as one.
For example - there are assesments questions for clients whith aproximately
45 unique questions. To accomodate the dimension table the model was
Assesment1, Assesment2, Assesment3 - then a view on top of all 3 tables to
simulate it as one. The reason is all the FK's were part of the PK. The PK
limitiation is 16 columns.Hello Joe,
This sounds like one of the following:
1. This could be a Junk Dimension use to join other fact tables and
other dimensions. Junk dimensions are generally non-conforming.
2. This is possibly a snow flake star schema. The principle is to
normalize the dimensions. The idea is make the ETL process easier to
maintain and save space. But really it creates a complex query
environment for users and doesn't save space.
3. Most likely this is Bad design, if the dimensions have the same
columns and unique keys across the three tables then creating one
dimension table should improve query performance and reduce the load
times.
The 16 column Primary key limit is a limitation of SQL Server 2000. In
SQL 2005 you can extend non clustered indexes with the new include
index function. The Maximum number of columns is 1023.
Hope this helps
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
Joe wrote:
> I just started a new job and the data warehouse was constructed a bit
> different than what I have seen.
> Some of the dimension tables had so many FK's that they had to create a vi
ew
> on top of 3 dimension tables to simulate it as one.
> For example - there are assesments questions for clients whith aproximatel
y
> 45 unique questions. To accomodate the dimension table the model was
> Assesment1, Assesment2, Assesment3 - then a view on top of all 3 tables to
> simulate it as one. The reason is all the FK's were part of the PK. The
PK
> limitiation is 16 columns.|||Hello Joe,
This sounds like one of the following:
1. This could be a Junk Dimension use to join other fact tables and
other dimensions. Junk dimensions are generally non-conforming.
2. This is possibly a snow flake star schema. The principle is to
normalize the dimensions. The idea is make the ETL process easier to
maintain and save space. But really it creates a complex query
environment for users and doesn't save space.
3. Most likely this is Bad design, if the dimensions have the same
columns and unique keys across the three tables then creating one
dimension table should improve query performance and reduce the load
times.
The 16 column Primary key limit is a limitation of SQL Server 2000. In
SQL 2005 you can extend non clustered indexes with the new include
index function. The Maximum number of columns is 1023.
Hope this helps
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
Joe wrote:
> I just started a new job and the data warehouse was constructed a bit
> different than what I have seen.
> Some of the dimension tables had so many FK's that they had to create a vi
ew
> on top of 3 dimension tables to simulate it as one.
> For example - there are assesments questions for clients whith aproximatel
y
> 45 unique questions. To accomodate the dimension table the model was
> Assesment1, Assesment2, Assesment3 - then a view on top of all 3 tables to
> simulate it as one. The reason is all the FK's were part of the PK. The
PK
> limitiation is 16 columns.