PL/SQL is a database programming language, which means that whatever else you use it for, you definitely should be using it to manage the data in your database. And when you change data in a table, you must commit those changes for them to be saved, for others to see them.
It’s not hard to commit changes. You just use the commit; statement.
But when and where should you commit? Here are some thoughts on committing…
- Generally, commits should not be inside your procedures and functions. Leave it to the user to decide when to save their changes. Of course, this doesn’t apply to batch processing, in which case you will likely want to commit at the end.
- If you feel strongly enough about the above general principle, you can actually turn off the ability to execute a commit inside a stored program unit using the “alter session disable commit in procedure” command.
- When you execute a DDL statement (e.g., create table), Oracle database performs a commit before and after you execute the statement. More info here.
- If the DDL statement fails with a syntax error (e.g., table name is a reserved word), there is no commit. If it fails with a semantic error (e.g., table with that name already exists), then there is a commit. Demonstration of this effect here.
- Committing changes inside a stored program unit can make testing harder (have to rebuild contents of tables). Disabling the commit at the session level could help with this, but you also might want to consider encapsulating the call to commit to give you an API-based approach to turning off commits while testing. I built a my_commit package on LiveSQL in case you want to try this out.
- Exceptions generally do not cause a rollback of outstanding changes in your session. Check out this script.
- You can use PL/Scope to find commit and rollback statements in your code. A demo is available at LiveSQL!
- Use the autonmous_transaction pragma to restrict committing of changes to only those made in the specific procedure or function. We’ve got a script for that!
- Sometimes developers feel the need to incrementally commit (say, every 5,000 rows inserted). That’s fine, as long as the application logic (definition of a transaction) will support it. You can even do incremental committing with forall, though it’s not naturally or declaratively inclined that way.
- When running code in APEX, remember that even though you may define individual processes/steps to execute (let’s say, on submit), they are all part of a single transaction – unless your code executes a commit somewhere along the line. I’ll demonstrate this during the Feuertip session here.
