State Codes


Oracle PL/SQL Exceptions Transactions Public New

Easy 

3min


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.

The states table contains a state with the state_code 'NY'.
The states table contains a state with the with state_code 'CA'.
The statement "Unable to insert state." is displayed.
The statement "An error occurred." is displayed.
There are no active transactions when the code completes.