Last post Aug 27, 2017 02:44 AM by Lannie
Aug 21, 2017 10:16 AM|Sanfoor|LINK
I need to perform an oracle transaction to insert into 4 tables; master table and three details tables.
how to make the function return this values? I need an example please
0- Fail : in case if error exist in any insertion ( must do rollback)
3- the record is already exist
Aug 22, 2017 03:54 AM|Lannie|LINK
Use .NET to pass your variables to an Oracle PL/SQL Package.
The Package contains a procedure.
Start the procedure as a transaction
Followed by four ORACLE MERGE commands to UPDATE if record exists (or ignore) or INSERT if new record.
At the end of the transaction you can then COMMIT if success or ROLLBACK if failure.
The return the final status of success, failure or inserted new record to your .NET for display to users.
Aug 22, 2017 09:51 AM|Sanfoor|LINK
i need to do this in .NET
Aug 23, 2017 09:10 PM|Lannie|LINK
Yes it can be done on .NET side not using PL/SQL package but using transaction wapped blocks of SQL MERGE COMMANDs.
Study ORACLE ODP.NET .BEGINTRANSACTION with COMMIT and ROLLBACK
And use of Oracle ODP.NET ExecuteNonQuery
all surrounded by TRY for capturing your success with COMMIT and failure (CATCH) for ROLLBACK
Aug 26, 2017 05:03 PM|Sanfoor|LINK
i need an example please :)
Aug 27, 2017 02:44 AM|Lannie|LINK
What is your programming environment?
Console app, WPF app, ASP.NET app, etc.
Is the database local to the application server or remote?
What version of ODP.NET are you using?
Are you using the ODP.NET Managed Driver or the Unmanaged Driver?
What version of Oracle database are your using?
And bitness of all these layers, operating system 32-bit or 64-bit, app 32-bit or 64-bit, database 32-bit or 64-bit.
It matters as the capability for .NET transactions changed over versions and time.