Skip to Main Content
  • Questions
  • To stop the execution of a procedure

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Michel.

Asked: September 13, 2000 - 11:56 pm UTC

Last updated: July 27, 2004 - 9:27 am UTC

Version: 8.0.4

Viewed 10K+ times! This question is

You Asked

Good morning Tom,

I hope this finds you well;
With Designer(2.0.2) i built a form (Ver. 5.0.6.16) that has 2 buttons. Start and Stop. A program unit (start_button),called by the when-button-pressed trigger in the form, uses a for_loop cursor to build a data set of 35 procedures to be called dynamically in a particular order. Some of these procedures will run for up to 4 hours. I don't need to stop a procedure in ' mid-air', but i need to be able to access the stop button on the form and click on it while one of these procedures is running so that when the procedure has completed successfuly the execution will stop even if the data set build by the cursor has not run to the end. There is a check point in the loop when a procedure ends; If it fails, i exit the loop if it succeeds, i get the next one.
I have no idea how to enable the stop button while a procedure is running
I hope you can give me a hand!
Michel

and Tom said...

Here is how I would do this -- use dbms_job to run the job in the background in the database. Don't run it in the client (for hours and hours).

If you use dbms_job, and you instrument your stored procedures with calls to the dbms_application_info package, your client can monitor its progress by querying the v$session table. You can then show people how far the procedure is and allow the client to go about its work. Additionally -- to "stop" the running program, your client can simply insert a row into a table and your procedure can "look" for that row and stop when it sees it (or use DBMS_ALERT or any method to get a message to this other session).

Other then that, if you are really going to run this on the client, I would use 2 forms and 2 sessions. Using logic just like above - the first form would run the second form which would run the procedures. The first form would not be blocked. The first form can signal the other form via a DBMS_ALERT or insert into a message table to tell it to "stop". Additionally you would still use dbms_application_info to allow form1 to display the progress of form2.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to stop executing query?

Teymur, July 27, 2004 - 3:15 am UTC

Dear Tom.

If I started simple query on a big table (e.g: select * from t) which have 24.000.000.000 records, is it possible to stop it?..

Thanks inn advance.

Your site is ORACLE UNIVERSITY and FREE!!!

Tom Kyte
July 27, 2004 - 7:22 am UTC

ctl^c

or in your client, just stop fetching from it - close the cursor

How to stop executing query

Teymur, July 27, 2004 - 8:07 am UTC

Thanks for anwser.

I now this option in sqlplus. I am going to develop applicaton and could you please give me real world example how to write it (procedure or something else)?

Thanks in advance.

Tom Kyte
July 27, 2004 - 9:27 am UTC

depends on the language.


Java -- there is a "cancel" subroutine associated with statements.

ctl-c gets it in most cases.

totally depends on the environment however.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library