start, restart, stop and status stored procedure - howto?
PaavoJul 13 2009 — edited Jul 13 2009First:
- this and other otn forums are awesome!
- apex is neat
Second:
- I have O'Reilly books: "Oracle pl/sql programming" and "Mastering Oracle SQL"
- Also tried to search / google best practices around stopping 'hanging stored procedures' and creating logics for such happening
Challenge:
I would like to implement simple solution for starting, stopping, restarting and maybe even getting status of stored procedures. Sounds like unix runlevels but in "userspace" without the sys rights.
Finally I would like to have this functionality in apex-application where predefines stored procedures could be started, restarted, stopped and maybe even status could be retrieved.
start - this is simple, start - but maybe check if such stored procedure is already started ( so this start will fail)
stop - send signal to certain procedure(s) that now it/they should exit immediately
restart - actually stop+start
status - maybe via status table indicating e.g. records_processed/records_to_be_processed
Mostly I need this stop so what would be the best way to write "exit" for stored procedure? I would like to avoid the following approach:
-----
SELECT sid, serial# , username, schemaname, osuser FROM gv$session;
ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;
-----
/paavo
Edited by: paavo on Jul 13, 2009 11:35 AM - added status to subject :)