Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Location
    Gandhinagar India
    Posts
    22

    Cool Unanswered: 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 help

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pl/sql job package

    use the DBMS_JOB package, which is documented here:

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

  3. #3
    Join Date
    Dec 2003
    Location
    Gandhinagar India
    Posts
    22

    Re: pl/sql job package

    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...job.htm#999107

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pl/sql job package

    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.

  5. #5
    Join Date
    Dec 2003
    Location
    Gandhinagar India
    Posts
    22

    Question Re: pl/sql job package

    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.
    Last edited by bobd303; 12-12-03 at 20:42.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pl/sql job package

    You'll need to contact your DBA about this.

  7. #7
    Join Date
    Dec 2003
    Location
    Gandhinagar India
    Posts
    22

    Thumbs up Re: pl/sql job package

    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.

  8. #8
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: pl/sql job package

    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.
    Oracle can do wonders !

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pl/sql job package

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •