This discussion is archived
2 Replies Latest reply: Nov 29, 2012 9:25 AM by rp0428 RSS

Oracle session destructor mechanism

578896 Newbie
Currently Being Moderated
Hi,

I am having a .NET application calling PL/SQL procedures in Oracle 10g.

I want a procedure to be sequentially accessed by multiple users. This procedure performs a bulk upload and I want only one user to perform a bulk upload at any instant. So, I want the procedure to insert a row in a table and commit on starting. Any other instance of the procedure would check the existence of this row and get rejected.

But sometimes due to network failures, I see that the row which got inserted is getting untouched. Because I have the piece of code which removes the row at the end of the pl/sql procedure. Is there any other mechanism to delete this row for instance any Oracle Session Destructor which shall make sure the row gets removed at any cost.

Regards,
RajaGopal Maddi
  • 1. Re: Oracle session destructor mechanism
    Marwim Expert
    Currently Being Moderated
    Hello,

    this is the forum for the tool {forum:id=260}. Please ask in {forum:id=75} and mark this question as answered so others can spend their time on answering open questions.

    Best Regards
    Marcus
  • 2. Re: Oracle session destructor mechanism
    rp0428 Guru
    Currently Being Moderated
    >
    I want a procedure to be sequentially accessed by multiple users. This procedure performs a bulk upload and I want only one user to perform a bulk upload at any instant. So, I want the procedure to insert a row in a table and commit on starting. Any other instance of the procedure would check the existence of this row and get rejected.
    >
    That is the wrong solution for your problem. Just serialize the procedure using functionality that Oracle already provides.

    See my reply in this thread for how to use DBMS_LOCK to serialize that procedure. You don't need to use a table like you are doing.
    Re: possible to lock stored procedure so only one session may run it at a time?

    DO NOT follow up in this thread. As already said this forum is for SQL developer questions only.

    Mark this thread ANSWERED and post in the sql and pl/sql forum if you need more help with this issue.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points