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.
Exceptions exist in most modern programming languages, making it important for a programmer to understand them and know how to handle them.
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.
Consider the following database objects:
CREATE TABLE states ( state_code VARCHAR2(2) PRIMARY KEY NOT NULL ); CREATE OR REPLACE PROCEDURE insert_state(new_state_code IN VARCHAR2) IS BEGIN INSERT INTO states(state_code) VALUES (new_state_code); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Unable to insert state.'); ROLLBACK; END; /
Immediately upon their creation, the following code is executed:
BEGIN insert_state('NY'); COMMIT; insert_state('CA'); insert_state('CA'); COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('An error occurred.'); ROLLBACK; END; /
Select all the statements that are correct.