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 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
When the users table is dropped, the count_users function is invalidated.
When the users table is dropped any function, procedure, or package using count_users is invalidated.
When the users table is dropped, any function, procedure, and package using count_users, inside dynamic SQL is invalidated.
When the users table is dropped, the count_users function is also dropped.
As the count_users function is dependent on the users table, any statement dropping the users table will fail.
   

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
NO_DATA_FOUND
TOO_MANY_ROWS
VALUE_ERROR
DUP_VAL_ON_INDEX
INVALID_NUMBER
   

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

Easy 
3min
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.
   

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

Hard 
3min
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.
   

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.