AbairTechSolutionDIGITAL SOLUTIONS FOR A SMARTER TOMORROW

Menu
HomeLive DemosMobile AppsBusiness SoftwareServicesKnowledge CenterInterview PrepContact

Home › Programming MCQs › PL/SQL

Oracle PL/SQL · 40 MCQs with explanations

PL/SQL MCQ Questions and Answers: 40 Practice Questions with Explanations

By AbairTech Solution · Written by Oracle developers who use PL/SQL every day

Published by AbairTech Solution · Reviewed November 2026 · Editorial policy
How to use this page. Skim the study notes, answer each question in your head, then open Show answer. The questions reflect what Oracle developer interviews actually ask: exceptions, cursors, bulk processing and triggers come up far more than syntax trivia.

PL/SQL study notes

PL/SQL is Oracle’s procedural extension to SQL. It runs inside the database, close to the data, which is why business rules, payroll calculations and integrations in Oracle systems (including Oracle Forms and Oracle APEX applications) are usually written in it. Every piece of PL/SQL is built from blocks:

DECLARE                       -- optional: variables, cursors, types
  v_name employees.last_name%TYPE;
BEGIN                         -- mandatory: the executable section
  SELECT last_name INTO v_name FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION                     -- optional: error handlers
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such employee');
END;
Common exception When it is raised
NO_DATA_FOUND SELECT … INTO returns no rows
TOO_MANY_ROWS SELECT … INTO returns more than one row
ZERO_DIVIDE Division by zero
DUP_VAL_ON_INDEX Insert or update violates a unique index
VALUE_ERROR Conversion, size or truncation error

Two performance ideas appear in almost every interview. First, each switch between the PL/SQL engine and the SQL engine costs time, so processing rows one at a time in a loop is slow; BULK COLLECT and FORALL move whole batches in one switch. Second, packages are loaded into memory once per session, so grouping related code in packages is faster and easier to maintain than many standalone procedures.

1–10 Blocks & subprograms11–20 Packages & exceptions21–30 Collections & triggers31–40 Advanced topics

Questions 1–10: blocks, variables, cursors and subprograms

1. Which section of a PL/SQL block is mandatory?

  1. DECLARE
  2. EXCEPTION
  3. BEGIN … END (the executable section)
  4. All three sections
Show answer

Answer: C. DECLARE and EXCEPTION are optional. A block needs at least one executable statement – even just NULL;.

2. A SELECT … INTO statement finds no rows. Which exception is raised?

  1. NO_DATA_FOUND
  2. TOO_MANY_ROWS
  3. VALUE_ERROR
  4. No exception; the variable becomes NULL
Show answer

Answer: A. Handle it, or use an aggregate such as MAX (which always returns one row) when “no row” is a normal case.

3. A SELECT … INTO returns three rows. What happens?

  1. The first row is used
  2. The last row is used
  3. NO_DATA_FOUND is raised
  4. TOO_MANY_ROWS is raised
Show answer

Answer: D. SELECT INTO expects exactly one row. To process several rows, use a cursor or BULK COLLECT.

4. What does v_sal employees.salary%TYPE; do?

  1. Copies the current salary into the variable
  2. Creates a new column
  3. Declares a variable with the same data type as the salary column
  4. Declares a record for the whole row
Show answer

Answer: C. If the column type later changes (say NUMBER(8,2) to NUMBER(10,2)), the code adapts without editing.

5. %ROWTYPE declares:

  1. A record with one field for each column of a table, view or cursor
  2. A number holding the row count
  3. A collection of rows
  4. A cursor
Show answer

Answer: A. r_emp employees%ROWTYPE; lets you write r_emp.last_name, r_emp.salary and so on.

6. After an UPDATE statement, which attribute gives the number of rows changed?

  1. SQL%FOUND
  2. SQL%COUNT
  3. SQL%ISOPEN
  4. SQL%ROWCOUNT
Show answer

Answer: D – SQL%ROWCOUNT. SQL%FOUND and SQL%NOTFOUND tell you whether any row was affected. Read the attribute straight after the statement, before running other SQL.

7. What is the main advantage of a cursor FOR loop?

  1. It can update the cursor’s query
  2. It opens, fetches and closes the cursor automatically
  3. It returns a single row only
  4. It runs in parallel
Show answer

Answer: B. FOR r IN (SELECT …) LOOP … END LOOP; also declares the record for you, and Oracle internally fetches in batches of 100.

8. How does a function differ from a procedure?

  1. A function cannot have parameters
  2. A procedure must return a value
  3. A function must return a value and can be called from SQL if it meets certain rules
  4. There is no difference
Show answer

Answer: C. Functions return one value through RETURN and can be used in SELECT statements. Procedures are called as statements and can pass values back through OUT parameters.

9. What is the default parameter mode?

  1. IN
  2. OUT
  3. IN OUT
  4. There is no default; the mode must be written
Show answer

Answer: A – IN. IN parameters are read-only inside the subprogram. OUT returns a value; IN OUT passes a value in and back out.

10. In a basic LOOP … EXIT WHEN cond; END LOOP; with EXIT WHEN as the last statement, how many times does the body run at minimum?

  1. Zero
  2. Once
  3. Twice
  4. It depends on the cursor
Show answer

Answer: B – once. The condition is only checked after the body runs. A WHILE loop checks first and can run zero times.

Questions 11–20: packages and exception handling

11. What belongs in a package specification?

  1. The full code of every procedure
  2. Only private variables
  3. Triggers
  4. Public declarations: the procedures, functions, types and variables other code may use
Show answer

Answer: D. The body holds the implementation and private helpers. You can change the body without invalidating code that depends on the specification.

12. Which is a genuine benefit of packages?

  1. They remove the need for exception handling
  2. They support overloading and session-level state, and load related code into memory together
  3. They make SQL statements run in parallel automatically
  4. They can be called from Oracle Forms only
Show answer

Answer: B. Packages also hide implementation details (encapsulation) and reduce dependency chains.

13. Where must the WHEN OTHERS handler appear?

  1. First, before specific handlers
  2. Anywhere in the EXCEPTION section
  3. Last, after all specific handlers
  4. In the DECLARE section
Show answer

Answer: C. It catches everything not handled above it. Always log or re-raise inside it – a WHEN OTHERS that silently swallows errors hides real bugs.

14. Which pair returns the error number and message inside a handler?

  1. SQLCODE and SQLERRM
  2. ERRNUM and ERRMSG
  3. SQL%ERROR and SQL%MESSAGE
  4. DBMS_ERROR.NUMBER and DBMS_ERROR.TEXT
Show answer

Answer: A. For logging, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE adds the line number where the error happened.

15. Which error numbers are allowed in RAISE_APPLICATION_ERROR?

  1. -1 to -999
  2. -20000 to -20999
  3. 1 to 20000
  4. Any negative number
Show answer

Answer: B. This range is reserved for application errors. Example: RAISE_APPLICATION_ERROR(-20001, 'Credit limit exceeded');

16. How do you raise a user-defined exception declared as e_low_stock EXCEPTION;?

  1. THROW e_low_stock;
  2. SIGNAL e_low_stock;
  3. EXECUTE e_low_stock;
  4. RAISE e_low_stock;
Show answer

Answer: D. RAISE transfers control to the matching handler. A bare RAISE; inside a handler re-raises the current exception.

17. What does PRAGMA EXCEPTION_INIT do?

  1. Creates a new error number
  2. Disables an exception
  3. Links a named exception to an Oracle error number, so you can handle it by name
  4. Logs every exception
Show answer

Answer: C. For example, map ORA-02292 (child record found) to e_child_exists and handle it clearly.

18. An exception is raised while initialising a variable in the DECLARE section. Who can handle it?

  1. Only an enclosing (outer) block
  2. The same block’s EXCEPTION section
  3. Nobody; it is ignored
  4. The next statement in the block
Show answer

Answer: A. Errors in the declaration section propagate immediately to the outer block. Do risky initialisation in BEGIN instead.

19. What does overloading mean in PL/SQL?

  1. Running a procedure twice
  2. Several subprograms in the same package with the same name but different parameter lists
  3. Passing too many parameters
  4. Calling a function from SQL
Show answer

Answer: B. Oracle picks the version whose parameters match the call, for example get_price(p_id NUMBER) and get_price(p_code VARCHAR2).

20. In SQL*Plus, why might DBMS_OUTPUT.PUT_LINE print nothing?

  1. PUT_LINE only works in triggers
  2. The block must be compiled first
  3. SET SERVEROUTPUT ON has not been run
  4. DBMS_OUTPUT needs a COMMIT
Show answer

Answer: C. DBMS_OUTPUT writes to a buffer; the client only displays it when server output is enabled. It is for debugging, not application logging.

Questions 21–30: collections, bulk processing and triggers

21. What is the purpose of BULK COLLECT?

  1. To compress table data
  2. To lock all rows of a table
  3. To fetch rows into a collection in one step, reducing context switches
  4. To collect optimizer statistics
Show answer

Answer: C. For large tables, use FETCH … BULK COLLECT INTO … LIMIT 1000 in a loop so memory use stays controlled.

22. FORALL is used to:

  1. Loop through any PL/SQL code faster
  2. Send a whole collection to one DML statement in a single context switch
  3. Create parallel sessions
  4. Fetch rows from a cursor
Show answer

Answer: B. FORALL is not a loop – it can contain exactly one INSERT, UPDATE, DELETE or MERGE statement.

23. What does SAVE EXCEPTIONS add to FORALL?

  1. It continues past failing rows and records them in SQL%BULK_EXCEPTIONS
  2. It commits after each row
  3. It ignores all errors permanently
  4. It writes errors to the alert log
Show answer

Answer: A. At the end, ORA-24381 is raised and you loop through SQL%BULK_EXCEPTIONS to see each failing index and error code.

24. Which collection type has a fixed maximum size declared up front?

  1. Associative array
  2. Nested table
  3. REF CURSOR
  4. VARRAY
Show answer

Answer: D – VARRAY. TYPE t_days IS VARRAY(7) OF VARCHAR2(10); Nested tables and associative arrays have no fixed upper limit.

25. An associative array (index-by table) can be indexed by:

  1. DATE only
  2. PLS_INTEGER or VARCHAR2
  3. Any object type
  4. ROWID only
Show answer

Answer: B. String keys make associative arrays useful as fast in-memory lookups, for example product code → price.

26. Which clause makes a trigger fire once for every affected row?

  1. FOR EACH STATEMENT
  2. WHEN ROW
  3. FOR EACH ROW
  4. PER ROW
Show answer

Answer: C. Without FOR EACH ROW, a trigger is statement-level and fires once, even if the statement affects thousands of rows.

27. In a row-level DELETE trigger, what are the :NEW values?

  1. NULL
  2. The same as :OLD
  3. The values being inserted
  4. An error is raised if you read them
Show answer

Answer: A – NULL. In DELETE triggers only :OLD has values; in INSERT triggers only :NEW has values; UPDATE has both.

28. What causes the “mutating table” error (ORA-04091)?

  1. A table without a primary key
  2. Too many triggers on one table
  3. Dropping a table inside a trigger
  4. A row-level trigger querying or changing the same table that fired it
Show answer

Answer: D. The table is in the middle of changing, so Oracle stops the trigger from reading an inconsistent state.

29. Which feature, available since Oracle 11g, is a standard way to avoid mutating table errors?

  1. INSTEAD OF trigger
  2. Compound trigger
  3. Autonomous trigger
  4. System trigger
Show answer

Answer: B. A compound trigger collects row data in its row-level section and processes it in the AFTER STATEMENT section, when the table is stable.

30. Can you COMMIT inside a normal trigger?

  1. Yes, always
  2. Only in BEFORE triggers
  3. No, unless the trigger is declared an autonomous transaction
  4. Only in statement-level triggers
Show answer

Answer: C. The trigger is part of the triggering statement’s transaction. Autonomous transactions are acceptable for audit logging, not for business data.

Questions 31–40: dynamic SQL, performance and advanced topics

31. What does PRAGMA AUTONOMOUS_TRANSACTION do?

  1. Makes the subprogram run faster
  2. Runs the subprogram in a separate session
  3. Prevents any commit
  4. Gives the subprogram its own transaction, which commits or rolls back independently
Show answer

Answer: D. The classic use is an error-logging procedure whose log row survives even when the main transaction rolls back.

32. Which statement runs a SQL string built at run time?

  1. EXECUTE IMMEDIATE
  2. RUN SQL
  3. DBMS_OUTPUT.EXECUTE
  4. CALL STRING
Show answer

Answer: A. Use it for DDL inside PL/SQL or for queries whose table or columns are only known at run time. DBMS_SQL handles more complex cases.

33. Why use bind variables with EXECUTE IMMEDIATE … USING?

  1. They allow DDL statements
  2. They prevent SQL injection and let Oracle reuse the parsed statement
  3. They are required for SELECT statements
  4. They disable the optimizer
Show answer

Answer: B. Never concatenate user input into SQL text. Note that table and column names cannot be bound; validate them with DBMS_ASSERT.

34. What is SYS_REFCURSOR commonly used for?

  1. Storing rows permanently
  2. Locking a table
  3. Returning a query result set from a procedure to a client such as Java, .NET or APEX
  4. Defining a VARRAY
Show answer

Answer: C. It is a predefined weak REF CURSOR type, so you do not need to declare your own.

35. What does the NOCOPY hint request?

  1. Passing an OUT or IN OUT parameter by reference instead of copying it
  2. Preventing users from copying the code
  3. Disabling result caching
  4. Skipping the parameter
Show answer

Answer: A. It helps with large collections. It is a hint, and if an unhandled error occurs, the caller may see partly changed values.

36. A function marked DETERMINISTIC promises that:

  1. It never raises exceptions
  2. It runs in parallel
  3. It commits its changes
  4. The same inputs always produce the same result, so Oracle may reuse results
Show answer

Answer: D. It is required for functions used in function-based indexes. Marking a function deterministic when it is not leads to wrong results.

37. A function performs an INSERT and is called from a SELECT statement. What happens?

  1. The insert works normally
  2. The insert is silently skipped
  3. Oracle raises ORA-14551 (cannot perform DML inside a query)
  4. The SELECT returns no rows
Show answer

Answer: C. Functions called from SQL must not change data, unless they are autonomous transactions – which is rarely the right design.

38. What is the purpose of WHERE CURRENT OF cursor_name?

  1. To fetch the next row
  2. To update or delete the row most recently fetched from a FOR UPDATE cursor
  3. To close the cursor
  4. To count fetched rows
Show answer

Answer: B. The cursor must be declared with FOR UPDATE, which locks the selected rows until you commit or roll back.

39. What does the CONTINUE statement (Oracle 11g and later) do inside a loop?

  1. Exits the loop
  2. Restarts the procedure
  3. Raises an exception
  4. Skips the rest of the current iteration and starts the next one
Show answer

Answer: D. CONTINUE WHEN r.status = 'CANCELLED'; skips cancelled rows without nested IF blocks.

40. Why is row-by-row processing (“slow-by-slow”) a performance problem?

  1. Each row causes a context switch between the PL/SQL and SQL engines
  2. PL/SQL loops are limited to 1,000 rows
  3. It disables indexes
  4. It requires a COMMIT per row
Show answer

Answer: A. The best fix is often a single SQL statement (INSERT … SELECT, MERGE). When procedural logic is needed, use BULK COLLECT and FORALL.

What to practise next

Scored 32 or more? You are well prepared for most PL/SQL developer interviews. If exceptions or triggers caught you out, write small test blocks for each one – running the code teaches more than rereading. Next, try the SQL MCQs, the 100 Oracle Forms MCQs or the 100 Oracle APEX MCQs. All topics are on the Programming MCQs hub.

Need PL/SQL or Oracle development?

AbairTech writes and tunes PL/SQL, builds Oracle APEX applications and migrates Oracle Forms to the web.

Oracle ServicesMore MCQs