Re: Cancel Operation when procedure is running

From: Ken Shirey <kshirey_at_commercial-data.com>
Date: 1998/01/06
Message-ID: <01bd1a6f$6d9a1b30$c7e940c6_at_kshirey>#1/1

Do this:

---

Create table myControlTable ( username varchar2(30), status varchar2(10));

---

procedure myProc (.....)
...
LOOP
  text_io.put_line......
  LineCounter := lineCounter + 1;
  if LineCounter > 500 -- checks only after every 500 rows. Change to suit you.

    select status
    into theStatus
    from myControlTable
    where username = USER;
    if theStatus = 'ABORT' then
      exit_loop;
    end if;
  end if;
END LOOP;
etc...

---

for the "cancel" routine, update MyControlTable set status = 'ABORT' where username = USER,
and do a commit.

This will force the routine to exit the next time LineCounter reaches the specified value.

Hope this helps.
Ken

YyWong <yywong_at_math.hkbu.edu.hk> wrote in article <68c7e6$7fk$1_at_power42t.hkbu.edu.hk>...
> I have a procedure that use TEXT_IO package to read in a data file
> and insert records into database. This operation, by estimation,
> need about 10 - 15 mins to finish. During the operation, the user
> wants to set a [Cancel] button to cancel the whole operation and
> rollback all change.
>
> My question is how can I interrupt the procedure or is there a way
> to send a system message to stop the procedure when the [Cancel]
> button is pressed? Just like the "On Error" command in Visual
> Basic!
>
> Thanks!
>
>
  Received on Tue Jan 06 1998 - 00:00:00 CET