I have an Oracle Stored Procedure that does some inserts and updates on a table in DB. There is no explicit Commit or Rollback statement at the end of the procedure. However, when I call this SP through a java class, I see that the inserts and updates are committed into the DB.
So can anyone help me understand if we really need a commit statement at the end of the stored procedure in Oracle?
I am not java experience but as far as I know when you close the connection of the database the data are committed (unless if you rollback them). Now to return into your question is when to use the commit in SP.
When you use DML(insert,update,delete) operation in the procedure on a table, the table will be Locked therefore if any other user try to access the locked table, it has to wait till you commit/rollback your operation. so if your procedure was taking time, due to a long loop or bad optimized query then the user will be blocked. So if you had a commit before the DMl, the no blocks will happen.
Other reason, is the undo tablespace, where all the data not committed will wait there till you commit them, so if for example you inserted lot of data (millions), your undo might get full depend on your size and youll get an error.
so short answer , if your procedure doesn't has lot of operations on big tables and it fast then you can pass by the commit , otherwise it better to add commits.
