What will happen after commit statement ?

          Cursor C1 is          Select empno,          ename from emp;          Begin               open C1;            loop                    Fetch C1 into               eno.ename;                    Exit When               C1 %notfound;-----                    commit;               end loop;               end; The  cursor  having  query  as  SELECT  ....  FOR  UPDATE gets closed after COMMIT/ROLLBACK. The  cursor  having  query  as  SELECT....  does  not get closed even after COMMIT/ROLLBACK.
  • Interview Candidate
  • 46
  • 20548

Showing Answers 1 - 46 of 46 Answers

kishorebabukm

  • Sep 20th, 2005
 

The cursor having query will get closed, because once the for update clause is fired it locks all the rows which is been modified and once it encounters a commit/rollback it automatically comes out of that session, and will be processing a fresh loop altogether.

  Was this answer useful?  Yes

Bhanu

  • Oct 19th, 2005
 

DDL statements cannot be included in the pl/sql block.  Hence giving commit is not allowed.

  Was this answer useful?  Yes

sunil

  • Jun 12th, 2007
 

All the Database Locks are Released after commit....

Prajyot Shelar

  • Jun 28th, 2007
 

When u will executed this statement into the server that's time commit mechanism will start to save a record as permanently into the database.
COMMIT statement it's part of TCL Language.

  Was this answer useful?  Yes

askvenki

  • Jul 18th, 2007
 

We cannot give commit inside a loop statement. Then we have to commit out side loop

  Was this answer useful?  Yes

vickyjp

  • Jul 20th, 2007
 

If the data set is too big commiting with-in the cursor loop might lead to the `Snapshot too old Error`.

  Was this answer useful?  Yes

bambino chakma

  • Aug 12th, 2007
 

after the commit statement, the cursor get closed ...as the result no further rows can be fetched even though  there are rows to be fetched.

  Was this answer useful?  Yes

ANY

  • Nov 12th, 2007
 

CURSOR WONT CLOSE AFTER COMMIT TRY THIS CODE

DECLARE
CURSOR C IS
SELECT * FROM EMP
WHERE ROWNUM <4;
R C%ROWTYPE;
BEGIN
OPEN C;
LOOP
FETCH C INTO R;
DBMS_OUTPUT.PUT_LINE (R.EMPNO);
EXIT WHEN C%NOTFOUND;
UPDATE EMP
SET SAL = SAL-1;
COMMIT;
END LOOP;
CLOSE C;
END;

  Was this answer useful?  Yes

sharmasl

  • Mar 16th, 2009
 

In this PL/SQL Block the curosr?fetch the records in both the variable till the time it get record. After fetching all the records when the condition %not found is true it execute the commit and save all the record which is fetched at the time of execution.

  Was this answer useful?  Yes

rachit_srivastava

  • Aug 17th, 2009
 

Use of COMMIT statement end your current transaction and make permanent all changes performed in the transaction.
A transaction is a sequence of SQL statements, usually COMMIT is used for DML statements.

Cheers!!

  Was this answer useful?  Yes

kinkyfellow

  • Nov 18th, 2009
 

Commit in this context will not do anything except the commiting the changes into database, done using DML statements. However, if the cursor is created with FOR UPDATE  clause, it will raise runtime exception as commit, in that case, would also release all locks and thus close the cursor implicitly and user will get ORA-1002 "fetch out of sequence " error as after the loop is executed one time, with the commit statement, the cursor will be closed and fetch into statement will fail.

Dilip Lakshetty

  • Mar 27th, 2011
 

Whenever User executes COMMIT statement?



The log writer makes redo log entries in redo log files after that message comes commit complete. 

later DBWR writes changes into db files permanently from db buffer.

  Was this answer useful?  Yes

technofreek

  • May 26th, 2011
 

I want to modify the code mentioned in the thread as:
DECLARE
CURSOR C IS
SELECT * FROM EMP
WHERE ROWNUM <4;
R C%ROWTYPE;
BEGIN
OPEN C;
LOOP
FETCH C INTO R;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (R.EMPNO||'-'|| R.SAL);
UPDATE EMP
SET SAL = SAL-1
where empno = r.empno;
DBMS_OUTPUT.PUT_LINE (R.EMPNO||'-'|| R.SAL);
END LOOP;
COMMIT;
CLOSE C;
END;

Also by using commit the changes made by update statement will be seen in database permanently.

  Was this answer useful?  Yes

Vivek

  • Jun 29th, 2012
 

After committing first oracle will commit the transaction into redo log file and then Data file. The data block will remain into the memory until the logical reads are going on. Oracle use LRU algorithm to maintain the data block into the memory. Also oracle maintain rollback segment before committing the data to store original version of data.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions