Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    52

    Talking Unanswered: How to stop an Stored Procedure from SQL Plus

    Hi all friends:


    Sometimes i run an stored procedured, it lasts sometimes about 30 minutes, but also sometimes i want to stop this stored procedured before it finishes, how can i do this from Sql Plus, what are the instructions code????


    Regards!!!!!

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    By killing the session running that perticular procedure.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Nov 2003
    Posts
    52

    Unhappy

    OK hings, but there is a way using code?

    Regards!!!!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Depends on how the procedure is written. If it has a loop in it, you could check on each loop iteration (or every 100th iteration or whatever) whether a request had been made to abort. This request could be via a row in a table, a pipe (DBMS_PIPE package), perhaps an alert (DBMS_ALERT).

  5. #5
    Join Date
    Feb 2004
    Posts
    108
    Just to share.
    In past, i do remember facing such situation.
    My code was going thru some 30 million recs. Based on certain other table values it updated few columns.
    Under test environment many times I had to "kill" the process. I did'nt wanted to bother the DBAs.
    So I created a single record single column table - had initial value say '0'.
    In the procedure, after every commit, after say 'n' updates/inserts, I checked this flag. If it's not '0' then exit from the procedure.
    Performance wise it may not be a good solution. But was best under my circumstances.

  6. #6
    Join Date
    Nov 2003
    Posts
    52
    thank you andrew, thank you pinakdb

    it really helps me!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •