oracle transaction for stored procedure?

Henry

My application uses Oracle stored procedure to modify multiple tables.
but OracleClient does not support table-like arrays in one execution,
therefore, I need call many times of the stored procedure. In this
case how can I use transaction to rollback all the executions if there
is error in the middle of calls? What I found is that the previous
succussful calls already commit the modification in Oracle side, my
application-level transaction could not rollback those.

Any help?

Nov 15 '05 #1

Subscribe Reply

9913

Nicholas Paldino [.NET/C# MVP]

Henry,

I can think of two solutions. The first would be to access the
BeginTransactio n method on the connection class that you are using. You
should be able to call this and begin a transaction on the database which
will be rolled back in the case of an error. This would return an
implementation of IDbTransaction. You can then call Commit or Rollback
depending on what happens.

Also, I believe Oracle is supported in COM+, which you can use to create
a serviced component. You can then set it so that the methods on this
component are transactional, in which case it will be handled automatically.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Henry" <we**********@y ahoo.com> wrote in message
news:b6******** *************** ***@posting.goo gle.com...

My application uses Oracle stored procedure to modify multiple tables.
but OracleClient does not support table-like arrays in one execution,
therefore, I need call many times of the stored procedure. In this
case how can I use transaction to rollback all the executions if there
is error in the middle of calls? What I found is that the previous
succussful calls already commit the modification in Oracle side, my
application-level transaction could not rollback those.

Any help?

Nov 15 '05 #2

Similar topics

9006

SQL Batch Script from MSSQL to Oracle 8i

by: Jeff Lambert | last post by:

We have a Windows client application written in delphi that connects to Oracle 8i w/ ADO. We accumulate a lot of SQL statements in a loop and finally send the strings list to be executed. What I really need help with are the first three and the last three statements. How would I rewrite those under Oracle to make sure they have the same behaviour? BTW I know this isn't the best way to learn Oracle PLSQL but I am under time constraints,...

Oracle Database

2374

Transaction through stored Procedure

by: Deepak Mehta | last post by:

i have to update two tables from ASP pages with same data but i want that both of them should be updated at one time. If either of them is not updated then my transaction should roll back.I want this thing to be in a stored procedure. so that i have to write an execute statement only on the ASP page and pass the parameters. Looking forward for ur reply DEEPAK

Microsoft SQL Server

528

How to pass an array from .net to oracle stored procedure

by: Nelson Xu | last post by:

Hi All Does anyone knows how to pass an array from .net application to oracle stored procedure Thank you in advance Nelson

.NET Framework

9995

deadlock - retrying the transaction

by: Zeng | last post by:

Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried) 3-4 times and give up if after that it's still in deadlock. I'm very sure that many experienced people out there already deal with this issue somehow. Is there an alternative to it? Thanks for your comments and suggestions.

C# / C Sharp

1229

Oracle - cannot ROLLBACK in a distributed tran

by: Jim Heavey | last post by:

I have tested a procedure in TOAD and it functions as expected, meaning for this particular transaction it returns and error message and performs a rollback within the procedure. When I call this stored procedure from within ASP.Net with the same data, The procedure returns an error which is caught by my try-catch block and it provides the following message...""Detail Error Message: ORA-02074: cannot ROLLBACK in a distributed...

ASP.NET

2224

Code works without transaction but not with transaction?

by: Crash | last post by:

C# VS 2003 ..Net Framework V1.1 SP1 SQL Server 2000 SP3 Enterprise Library June 2005 I'm working with some code {not of my creation} that performs the following sequence of actions: - Open SqlConnection using SQL authentication with UID & PWD in connect

C# / C Sharp

3508

Oracle PRAGMA AUTONOUMOUS_TRANSACTION equivalent in DB2?

by: Fred Morrison | last post by:

Does DB2 have anything equivalent to Oracle's PRAGMA AUTONOMOUS_TRANSACTION that allows a stored procedure to BEGIN, COMMIT or ROLLBACK a totally independent transaction while possibly (but not necessarily) nested inside a different ("parent") transaction? I need to write (INSERT) data to an EVENT_LOG table and COMMIT that INSERT without COMMIT'ing any other work in progress in the transaction my usp_write_event_log might be called from...

DB2 Database

6412

Oracle Sequence/Link 2 MS SQL SERVER 2005

by: ruediger.michels | last post by:

hi, 1. is there a statement in ms sql, what creates a sequence? cant find anything in web :-( -oracle: CREATE SEQUENCE XYZ INCREMENT BY 1 START WITH 1 NOCYCLE CACHE 20; -ms sql: ??? 2. hwo do i create a link to another ms-sql database

Microsoft SQL Server

3516

Transaction not rolling back

by: cricketweb | last post by:

I have a stored procedure that calls another stored procedure with the first stored procedure opening a transaction: BEGIN SET XACT_ABORT ON BEGIN TRANSACTION does various updates/inserts

Microsoft SQL Server

9343

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9104

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7973

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6646

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5967

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4477

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4738

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2541

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2119

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General