"do you know how to give your SQL Plus session a pop-up cancel button that will cancel the query currently running in that session? I used to have that capability and they upgraded my machine here and I have now lost that. Now if I want to cancel the query, I have to kill the session - this is tedious, expecially when I am trying to build a complex SQL statement"
I personally feel that because the control goes to the database server while it's building the execution path etc. for a complex query, there is no way to cancel it from the client (SQL/Plus)...the session will still be there until you kill it specifically.
I also remember a long time back in an older version of SQL/Plus (Oracle 7.x) when the query was run from the client a pop-up CANCEL window will appear giving you the option to cancel the query...though I am doubtful if it actually cancelled the query.
Thanks Chiappa but I think that "Cancel" thing is as good as the CTRL+C command but niether of them do what my developer was asking for(and I frankly don't think that is doable)
The cancel and crtl+c works fine if the query is simple and it comes into effect *only* when Oracle starts sending the resultset back to the client.
If the query is complex (that involves full tablescans of couple tables, joins and sorts) then the File-Cancel will be sort of disabled (windows will not give you control back to click anything on that SQL/Plus window) until unless the above mentioned process (parse, sorts etc.) has been done and Oracle starts sending the result back.
For such complex queries your wait time may be many many minutes and your only option will be to kill that session.