Oracle PL/SQL is Oracles extension for SQL and Oracle relational database. Oracle PL/SQL adds support for procedural language features such as control statements, loops and classes. This allows the developer to further optimize and refine queries at the database, rather than application layer.
A transaction contains a set of modifications to be made to a database. A transaction can be committed to make its modifications to a database permanent, or rolled back to cancel any changes. Transactions are an important part of ensuring modifications made to a database are treated in a coherent, reliable, and error-free way.
Public questions (free account) are common interview questions. They are great for practicing, or if you want to filter candidates using the classic problems.
This is a part of latest question addition to our question library.
Consider the following code:
CREATE OR REPLACE PROCEDURE log_transaction(log_message VARCHAR2, log_level NUMBER) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO logs(log_text, log_level) VALUES(log_message, log_level); IF log_level = 0 THEN ROLLBACK WORK; ELSIF log_level <= 5 THEN COMMIT; END IF; END; /
Select all the statements that are correct when calling this function within another transaction.
The procedure defines a savepoint named AUTONOMOUS_TRANSACTION.
The procedure can be called within another transaction without affecting the main transaction.
If log_level is 0, the transaction will be rolled back to the WORK savepoint.
If log_level is greater than 5, then an exception will be raised when the procedure exits.
The COMMIT statement will commit all active transactions.