Menu
I created table
SQL>CREATE TABLE Student
(
StudID NUMBER(6),
StudName VARCHAR2(25),
JoinDate DATE
);
Table created.
SQL>INSERT INTO Student
VALUES (123,'JOHN',SYSDATE);
1 row created.
SQL>DELETE Student;
1 row deleted.
How can I get back the row ? If I use
SQL>ROLLBACK;
Rollback complete.
But after that
SQL>SELECT * FROM Student;
no rows selected.
Why is this coming?
694
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.
COMMIT commits the current transaction, making its changes permanent. ROLLBACK rolls back the current transaction, canceling its changes. SET autocommit disables or enables the default autocommit mode for the current session.
create or replace package pckg as procedure prc (column in number) as begin insert into smth(column1) SELECT column1 FROM somewhere; end prc; end emp_pac; --ROLLBACK?? DECLARE column1 NUMBER; BEGIN SAVEPOINT savepnt; column1 := NULL; pckg.
ROLLBACK tells Oracle to roll back the entire transaction. In your case, both the INSERT and the DELETE are part of the same transaction so the ROLLBACK reverses both operations. That returns the database to the state it was in immediately following the CREATE TABLE statement.
If you don’t commit the transaction and power goes off or system crashes then the transaction is roll backed. TCL Statements available in Oracle are COMMIT : Make changes done in transaction permanent. ROLLBACK : Rollbacks the state of database to the last commit point.
The Rollback method rolls back all pending transactions within the specified connection. This method has no effect if a transaction has not begun. When a session-wide transaction is in progress, you can use this call to prematurely roll back the transactions for the specified connection.
The Rollback method rolls back all pending transactions within the specified session. The Rollback method is valid only when a transaction has been started. If a transaction has not been started, the use of the Rollback method results in an error.
This might be a bit confusing if you are not doing rollbacks on a regular basis. I put here mistakes that you are likely to make if not using SAVEPOINTS / ROLLBACK on a regular basis.
1. This is your case. We rollback all transaction to the beginning, ie. to the last (implicit) commit
2. Here we have SAVEPOINT but it does not matter, as it is not mentioned in ROLLBACK statment. So we are rolling back to the last commit, as before.
3. This is tricky. The rollback still goes to the last commit, as the syntax was not correct (but no error reported by the 11g database). Correct syntax is ROLLBACK TO <SAVEPOINT-NAME>:
4. Finally we have correct SYNTAX: ROLLBACK TO <SAVEPOINT-NAME>
Some readings: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9021.htm
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
