Hello,
I'll try my luck one more time by posting this again.
I do hope somebody has already solved my problem.
I have a Java application that spawns 4 different threads. Each thread
will perform it's own task which is calling a stored procedure. Each
stored procedure could possibly run a very long time since it will be
processing millions of rows. The user should
be able to stop any of the tasks he started. Stopping a task means
stopping a stored procedure and doing the clean up of the affected
tables.
What are my options in terms of stopping/cancelling a running stored
procedure from Java code?
I would appreciate any help on this matter.
Thanks in advance.
NK
basically a stored procedure is usually called , in a standard sql statement.
there is provision for cancelling a statement.
I.E
String The_qry = "{?=call
external_user.PACKAGE_02.RETURN_reporttablearray(?,?,?)}";
cstmt = (OracleCallableStatement) dbconn.prepareCall(The_qry);
cstmt.execute();
cstmt.cancel();
notice that the use of cancel relies on you writing your SQL so that it is in
callback mode, I.E you submit sql , then go away & come back to check it has
finished.
Or that you correctly thread your sql calls. (after the .execute() the thread
is tied up , until completion)
heres what oracle say about this
Using statement.cancel
The JDBC standard method Statement.cancel attempts to cleanly stop the
execution of a SQL statement by sending a message to the database. In
response, the database stops execution and replies with an error message. The
Java thread that invoked Statement.execute waits on the server, and continues
execution only when it receives the error reply message invoked by the other
thread's call to Statement.cancel.
As a result, Statement.cancel relies on the correct functioning of the
network and the database. If either the network connection is broken or the
database server is hung, the client does not receive the error reply to the
cancel message. Frequently, when the server process dies, JDBC receives an
IOException that frees the thread that invoked Statement.execute. In some
circumstances, the server is hung, but JDBC does not receive an IOException.
Statement.cancel does not free the thread that initiated the
Statement.execute.
When JDBC does not receive an IOException, Oracle Net may eventually time out
and close the connection. This causes an IOException and frees the thread.
This process can take many minutes. For information on how to control this
time-out, see the description of the readTimeout property for
OracleDatasource.setConnectionProperties. You can also tune this time-out
with certain Oracle Net settings. See the Oracle Database Net Services
Administrator's Guide for more information.
The JDBC standard method Statement.setQueryTimeout relies on
Statement.cancel. If execution continues longer than the specified time-out
interval, then the monitor thread calls Statement.cancel. This is subject to
all the same limitations described previously. As a result, there are cases
when the time-out does not free the thread that invoked Statement.execute.
The length of time between execution and cancellation is not precise. This
interval is no less than the specified time-out interval but can be several
seconds longer. If the application has active threads running at high
priority, then the interval can be arbitrarily longer. The monitor thread
runs at high priority, but other high priority threads may keep it from
running indefinitely. Note that the monitor thread is started only if there
are statements executed with non zero time-out. There is only one monitor
thread that monitors all Oracle JDBC statement execution.
Statement.cancel and Statement.setQueryTimeout are not supported in the
server-side internal driver. The server-side internal driver runs in the
single-threaded server process; the Oracle JVM implements Java threads within
this single-threaded process. If the server-side internal driver is executing
a SQL statement, then no Java thread can call Statement.cancel. This also
applies to the Oracle JDBC monitor thread.
notice the words "attempts to cleanly stop" , and "This process can take many
minutes".
Personally I think you will have to be very careful how you use this
function.
steve
