CODEWITHSUNDEEP
oracle-apexapexapex-codeoracle-apex21.2oracle-apex-23
Mateo Zambrano
Mateo Zambrano

Reputation: 11

How to disable autocommit in Oracle Apex

I am working on an Oracle Apex form where I have one field and two buttons. I need one button to perform an INSERT and then a COMMIT, while the other button only performs the INSERT without committing the changes.

PL/SQL code with commit

PL/SQL with not commit

Is it possible to disable autocommit in Oracle Apex to achieve this behavior?

Thanks in advance.

Upvotes: 0

Views: 40

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18650

The apex engine commits at fixed points and commit is also executed implicitely if other API's are called. There are a couple of blogs explaining the details, just google "when does Oracle APEX commit". Typically you do not bother about commits - APEX takes care of that for you. So as a best practice, don't ever put a COMMIT; statement in your APEX code. Uncommitted code is not possible in APEX since APEX is stateless. When a page is rendered, the APEX engine (in the database) generates the page and serves it to the browser. When you are in the APEX application page that is rendered, there is no connection to the database. A new connection is then established when the page is submitted or there is a ajax process. A "session" in APEX does not correspond to a single database session (docs)

This can be observed in the debug logs. At the start of every request a number of database session specific parameters are set. That indicates that it is a new (or re-used) database session.

The solution to this is using Collections. This allows storing and manipulating data during a users' session. Instead of inserting the data in the table and not committing, insert it into a collection using the APEX_COLLECTION api. Then when you want to save the data in the table, select it from the collection and insert it into the table.

Upvotes: 2

Related Questions