locked
Oracle Transaction return value RRS feed

  • Question

  • User-1141190189 posted

    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)

    1- success

    3- the record is already exist

    Monday, August 21, 2017 10:16 AM

All replies

  • User269602965 posted

    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.

    Tuesday, August 22, 2017 3:54 AM
  • User-1141190189 posted

    i need to do this in .NET

    Tuesday, August 22, 2017 9:51 AM
  • User269602965 posted

    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

    Wednesday, August 23, 2017 9:10 PM
  • User-1141190189 posted

    i need an example please :)

    Saturday, August 26, 2017 5:03 PM
  • User269602965 posted

    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.

    Sunday, August 27, 2017 2:44 AM