If cursor not updating rollback is required
After closing the opened cursor or before closing the opened cursor?Below is the procedure im using, am i doing it in correct way?CREATE OR REPLACE PROCEDURE Proc_Update CSClient Count(in Merid IN VARCHAR2, out Cli Count OUT NUMBER, outretvalue OUT NUMBER) AS CURSOR c1 IS SELECT CLIENT_COUNT FROM OP_TMER_CONF_PARENT WHERE MER_ID = in Merid FOR UPDATE OF CLIENT_COUNT; BEGIN OPEN c1; IF SQL%ROWCOUNT = 1 THEN FETCH c1 INTO out Cli Count; out Cli Count := out Cli Count 1; UPDATE OP_TMER_CONF_PARENT SET CLIENT_COUNT = out Cli Count WHERE CURRENT OF c1; END IF; outretvalue := 0; CLOSE c1; COMMIT; EXCEPTION WHEN no_data_found THEN outretvalue := -1; END; loop.Perhaps you have heard that committing frequently is a good thing. The opposite is true in Oracle: committing involves extra work and therefore you should only commit when all the work is done, never before. It is self-contained, serverless, zero-configuration and transactional.
Another statement outside of the named transaction inserts two rows.For best practices always surround the database operations with a try clause or a context manager: In the example above if the insert statement raises an exception, the transaction will be rolled back and the message gets printed; otherwise the transaction will be committed.Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction.This message does not affect subsequent processing.If a ROLLBACK TRANSACTION is issued in a trigger: @@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode.
(The system treats a trigger as an implied nested transaction.) ROLLBACK TRANSACTION statements in stored procedures do not affect subsequent statements in the batch that called the procedure; subsequent statements in the batch are executed.