How to call an Oracle procedure and commit from .net app

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

How to call an Oracle procedure and commit from .net app

Postby tev » Thu Oct 04, 2012 10:58 am

We developed a procedure that performs some DML transactions and commit if the DML is successful or rollback if otherwise. We also developed a .net application to call the procedure and display the return status to the application user. The major challenge we have now is that some calls to the procedure may time out as a result of network connectivity which the application interprete as transaction failure whereas the procedure may still go ahead to complete the transaction and subsequently commit it. The result of
Customer Problem Description
---------------------------------------------------

Problem Summary
---------------------------------------------------
How to call an Oracle procedure and commit/rollback from a .net application

Problem Description
---------------------------------------------------
We developed a procedure that performs some DML transactions and commit if the DML is successful or rollback if otherwise. We also developed a .net application to call the procedure and display the return status to the application user. The major challenge we have now is that some calls to the procedure may time out as a result of network connectivity which the application interprete as transaction failure whereas the procedure may still go ahead to complete the transaction and subsequently commit it. The result of this is that user may end up carrying out the same transaction over and over again based on the failure message displayed by the application. They get to know that all the transactions were successful when they go back to display all the transaction done from the database.
We need to know how to ensure that the .net application take control of the commit and rollback operation after calling the DML. How can we go about this? We will appreciate a sample .net calls snippet
tev
Member
 
Posts: 30
Joined: Wed May 19, 2010 5:51 pm

Re: How to call an Oracle procedure and commit from .net app

Postby tev » Thu Oct 04, 2012 1:30 pm

Also, in some situation , the procedure may take longer that the time out of the calling application to process and in this situation , we noticed that the procedure may process the transaction while the application may return a failed message to the user. What is your advice ? How can we avoid this scenario
tev
Member
 
Posts: 30
Joined: Wed May 19, 2010 5:51 pm

Re: How to call an Oracle procedure and commit from .net app

Postby Tim... » Thu Oct 04, 2012 3:59 pm

Hi.

Most client languages, include those supported by .NET allow you to manage transactions. To do this, you need to remove any commit/rollback control from the procedure.

The client language will need to issue a "begin transaction" before calling the procedure, then a commit or rollback depending on the return status of the procedure. In the even of a failure, rollback should be issued.

I'm not a .NET guy so I can't give you actual code syntax, but I guess this should be obvious to your developers.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 6 guests

cron