Search code examples
sqloracle-databasesql-updateplsqldeveloper

Used an Update Statement without Where clause in PL/SQL Developer


I executed an update statement mistakenly without any where clause in pl/sql developer.I did click on the break button when I found what had happened after some time when the execution was taking time to complete, commit and rollback buttons were grayed out after breaking the execution hence could not roll back the transaction. I am worried if break button commits the transaction in pl/sql developer or it roll backs to the previous state.


Solution

  • PL/SQL Developer's Break key does not commit, it just breaks the current operation and leaves things as they were at the start of it, including any open transactions. For example, if you did three updates without committing, and then started a fourth but used the Break key to interrupt it, you would still have the first three uncommitted updates, and the Commit and Rollback icons would be active.

    The icons were greyed out because there was no transaction to commit. PL/SQL Developer sets their status by calling dbms_transaction.local_transaction_id after every statement to check for an open transaction.

    If you didn't see the "Are you sure?" warning popup, you might check Configure > User Interface > Options > DSA Dialogs in case you previously clicked "OK" on a "Don't show this message again" popup.