I have a number of PL/SQL procedures which I execute from a web browser via the Oracle Web Server. Some of the procedures can take a long time to execute and I may change my mind about running them, so I want to kill the long running session or sessions. The only way I have of identifying the session uniquely is to look at the v_$session view and find the active pl/sql initiated by the webserver that looks like mine. I can then use ALTER SYSTEM KILL SESSION with the SID/Serialno from v_$session. This is annoying -- I'd like to be able to click a customised "stop" button on my browser (which is waiting for a response, showing me an hourglass) to stop the PL/SQL session on the database, not just a client side stop that will drop the connection, displaying "navigation cancelled" on my browser, with the PL/SQL session continuing to run, which is what I get by default.
What I think I need is a piece of PL/SQL, initiated via the webserver by a button click on the browser, which kills the long-running web server initiated session, but I need to be absolutely sure that the session is the right one.
DBMS_SESSION gives me a UNIQUE_SESSION_ID which I could pass down a pipe when every long running session is initiated. The killing session could receive that id and act on it. Unfortunately DBMS_SESSION does not give me a kill method, nor does the UNIQUE_SESSION_ID appear to be the serial number/sid combination I require if I'm going to use DBMS_SQL to ALTER SYSTEM KILL SESSION (although it does look pretty similar, seemingly made up of XXXXYYYYZZZZ where XXXX is the SID in hex, and YYYY is the hex of the serial number reversed bytewise).
Does anybody know either:
1. a better way of doing this (am I missing something obvious) or
2. the exact make up of the unique_session_id in DBMS_SQL so that I can always reliably extract the serial number and sid from it.
It seems a waste of resources when PL/SQL sessions are left running on the database regardless of whether or not the http connection that initiated them has closed, though I can see why you might sometimes want to run a batch process this way.