Transactions in Oracle APEX

Question

I am populating 3 tables from my APEX application:

Customer
Order
CustomerOrder

First record is inserted into customer table, then order table and then a record created in CustomerOrder, linking the first two tables together.

Do there are 3 inserts, one after another

Insert into Customer …
If cust_id is not null then
    Insert into Order..
    If order_id is not null then
        Insert into CustomerOrder
    End If
End If

But what if issue occurs when the record is being inserted into CustomerOrder? The record in Order table will be left not linked to any customer, isolated.

Can this be prevented? Meaning if an error occurs anywhere in the code, can the whole thing be rolled back like with the transactions in SQL?

Answer 1

I wonder why you have CustomerOrder at all. Can an Order belong to more than one Customer? If not, it seems that you could simply have a Customer ID column in Order.

That aside, the answer to your question depends on how you have the application laid out. If you have one page where a user enters all the order information, including what customer the order belongs to; and that page calls a PL/SQL block that does multiple INSERTs; and you don't explicitly COMMIT within that PL/SQL block; then all of that takes place in a single transaction. Apex will commit that transaction if it completes without errors, or roll it back if not.

If you are splitting the data entry across multiple pages, then each page submit is going to be a separately committed transaction.

It makes sense to me that you would have a separate page for entering customer information. But I see no issue with committing the Customer record before entering the order information.

I wouldn't make sense to me to have one page to enter the order, and then another page to map the order to a customer. If you are selecting the customer on the order entry page, and inserted into Order and CustomerOrder in one PL/SQL block, then you should not have any orphaned orders.