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

    Cool Unanswered: system level stored procedure

    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
    Dec 2003
    Posts
    18

    Re: system level stored procedure

    Hi,

    I created one table which is account (no number, dt date);
    Insert into account(1,’10-DEC-2003’);
    Insert into account(2,’11-DEC-2003’);
    Insert into account(3,’12-DEC-2003’);
    Commit;

    This is the procedure dt_check I created for continuously check the dt column in account table if the column value is match current date than it will delete the row.

    For this I used user_lock.sleep and passed 30 (means process will stop till next 30 sec and start again);

    For enabling this user_lock.sleep u have to run %oracle_home%rdbms/admin/usrlock.sql;

    Than run this procedure: -

    Create or replace procedure dt_check
    as
    cursor cur_dt_check
    is
    select no,dt from account;
    begin
    loop
    for for_cur in cur_dt_check loop
    dbms_output.put_line('1'||for_cur.dt);
    if for_cur.dt = to_date(sysdate,'DD-MM-RRRR') then
    dbms_output.put_line(for_cur.dt);

    delete from account where no=for_cur.no and dt= to_date(for_cur.dt,'DD-MM-RR
    RR');
    commit;
    end if;
    end loop;
    user_lock.sleep(30);
    end loop;
    end ;

    The drawback for this procedure it will held one session and it’s not a background process.



    Originally posted by bobd303
    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

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

    Question why session

    See I want to just run the query in the database on which it shall run forever. The users are going to add rows to the table. It just has to delete the outdated entries. Where does session comes into picture.
    The database is always running. I can start the procedure whenever the database is started.
    Ok.

  4. #4
    Join Date
    Dec 2003
    Posts
    18

    Re: why session

    if u carefully see this procedure the is two loops but outer loop doesn't have a break condition b'coz i want to make it as a cyclic procedure.
    if u want to put some condition which match to ur requirment then ok otherwise this infinite loop will hold one session untill u will kill the session.



    Originally posted by bobd303
    See I want to just run the query in the database on which it shall run forever. The users are going to add rows to the table. It just has to delete the outdated entries. Where does session comes into picture.
    The database is always running. I can start the procedure whenever the database is started.
    Ok.

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    There are 2 alternatives for scheduling data maintenance programs.
    1. Let there be a infinite loop in stored procedure - as suggested by rishikant_tiwar
    2. Write a stored procedure that does the delete. Schedule this to be executed by your O/S scheduler say a cron job or your windows scheduler.

    Pros-Cons:
    Option 1) If ever the DB is re-started, the procedure needs to be manually executed. If the session is killed by any other reason, again procedure needs to be started. Would require a check or a monitoring that the procedure is always running.

    Option 2) This will have a little hit on the DB server performance as every 30 seconds (or as required) a new connection will be opened to DB, records deleted and then connection closed.

    Which one to use is your call ... !!

    Regards,
    Oracle can do wonders !

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

    Thumbs up

    refer to http://www.dbforums.com/t974079.html

    Originally posted by cmasharma
    There are 2 alternatives for scheduling data maintenance programs.
    1. Let there be a infinite loop in stored procedure - as suggested by rishikant_tiwar
    2. Write a stored procedure that does the delete. Schedule this to be executed by your O/S scheduler say a cron job or your windows scheduler.

    Pros-Cons:
    Option 1) If ever the DB is re-started, the procedure needs to be manually executed. If the session is killed by any other reason, again procedure needs to be started. Would require a check or a monitoring that the procedure is always running.

    Option 2) This will have a little hit on the DB server performance as every 30 seconds (or as required) a new connection will be opened to DB, records deleted and then connection closed.

    Which one to use is your call ... !!

    Regards,

Posting Permissions

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