Oracle PL/SQL Interview Questions

Want to become an expert in cracking Oracle PL/SQL interview questions?

Start with practicing the questions below. Whether a question involves multiple choice or live coding, we will give you hints as you go and tell you if your answers are correct or incorrect.

After that, take our timed public Oracle PL/SQL Interview Questions Test.

To use our service for testing candidates, buy a pack of candidates.


1. Count Users
Oracle PL/SQL Functions Public

Consider the following code:

CREATE OR REPLACE FUNCTION count_users 
RETURN NUMBER 
IS 
  user_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO user_count FROM users;
  RETURN user_count;
END;
/

Select the statements that are correct.

(multiple correct answers possible)

Easy 
2min
   

2. Couples
Oracle PL/SQL Exceptions Public

Consider the following table definition:

CREATE TABLE couples(
  id NUMBER PRIMARY KEY NOT NULL,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL
);

Later the following code is executed:

DECLARE
  husband_id NUMBER;
BEGIN
  SELECT id INTO husband_id FROM couples WHERE first_name = 'John' AND last_name = 'Johnson';
  INSERT INTO couples(id, first_name, last_name) VALUES(husband_id + 1, 'Jane', 'Johnson');
END;

Select the exceptions that might be raised.

(multiple correct answers possible)

Easy 
2min
   

3. State Codes
Oracle PL/SQL Exceptions Transactions Public New

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 the statements that are correct.

(multiple correct answers possible)

Easy  
3min
   

4. Log Transaction
Oracle PL/SQL Transactions Public New

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 the statements that are correct when calling this function within another transaction. 

(multiple correct answers possible)

Hard 
3min
   

If you feel ready, take one of our timed public Oracle PL/SQL Interview Questions tests:
  • Oracle PL/SQL and SQL Online Test (Easy)
Not exactly what you are looking for? Go to our For Jobseekers section.