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 developers, purchase one of the paid plans for companies.


1. Count Users
Oracle PL/SQL Functions Language features Public New

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

Easy  
2min
Solve question

2. Couples
Oracle PL/SQL Exceptions Language features Public New

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 all the exceptions that might be raised.

Easy  
2min
Solve question

3. Patient
Oracle PL/SQL Conditional statements Language features Public

A hospital stores the names of patients and doctors in the following table:

TABLE patients
  id INTEGER NOT NULL PRIMARY KEY
  patient VARCHAR(100)
  doctor VARCHAR(100) NOT NULL

The hospital refers to a patient whose name is unknown as 'John Doe'. These patients are represented as null in the patients table.

Which of the following queries can be used to select a list of patients ('John Doe' if patient is null) and their doctors?

Easy  
2min
Solve question

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

Easy  
3min
Solve question

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

Hard  
3min
Solve question

If you feel ready, take one of our timed public Oracle PL/SQL interview questions tests:
  • Oracle PL/SQL and SQL (quick test) ( Easy )
Not exactly what you are looking for? Go to our For developers section.