MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

PL/SQL & T-SQL (Oracle and SQL Server) Module 9

PL/SQL foundations: blocks, types, SQL and exceptions interview questions

Interview questions on “PL/SQL foundations: blocks, types, SQL and exceptions”: warm-ups, then what each harder question tests and how a good answer starts.

16 questions: 5 warm-ups and 11 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

  1. Fresher

    Which sections can a PL/SQL block have, and which one must it have?

    Answered in the lesson PL/SQL blocks, variables and data types

  2. Fresher to mid

    When is PLS_INTEGER a better choice than NUMBER?

    Answered in the lesson PL/SQL blocks, variables and data types

  3. Fresher

    How do EXIT and CONTINUE differ inside a PL/SQL loop?

    EXIT ends the whole loop and control moves to the statement after END LOOP; CONTINUE ends only the current pass and starts the next one. Both have a WHEN form that acts only when its condition is TRUE, and both can name a label to act on an enclosing loop from inside a nested one.

    From the lesson PL/SQL control structures and iterators

  4. Fresher

    What does a SELECT INTO do when no row matches, and how can a block respond?

    Answered in the lesson SQL inside PL/SQL: SELECT INTO and DML

  5. Fresher

    Which exceptions come with a name in PL/SQL, and which do you declare yourself?

    Predefined exceptions come with a name: PL/SQL declares them in package STANDARD for common Oracle errors, such as NO_DATA_FOUND for ORA-01403, and the runtime raises them by itself. A user-defined exception is a name that your own block, subprogram or package declares with EXCEPTION for a rule of yours, and only a RAISE in your code raises it. A WHEN that names either kind catches it.

    From the lesson PL/SQL exceptions and how they propagate

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

  1. Fresher to senior

    A cooperative bank's branch in Nashik loads customer names into a table whose name column is VARCHAR2(30). English names of up to 30 letters load, but Marathi names of 12 to 15 letters fail with ORA-12899. Explain why, and what you would change in the table and in the PL/SQL that loads it.

    What it tests
    Knowing byte and character length semantics, how UTF-8 sizes Devanagari, what ORA-12899 reports, and how to change a column and the PL/SQL variables that feed it without losing sight of the byte ceilings.
    First step
    Compare LENGTH and LENGTHB of one failing name. A Marathi name of 12 code points needs 36 bytes in an AL32UTF8 database, and VARCHAR2(30) without a qualifier usually means 30 bytes, so the column and every variable that holds the name need a limit counted in characters.
    Follow-ups

    From the lesson PL/SQL blocks, variables and data types

    Opening the model answers with your pass…

    Model answers open with any MySmartCoPilot pass.

    • Pro pass · 1 week₹79$2.99
    • 30 days₹179$5.99
    • 1 year₹1,699$45

    One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

    Choose a passI have a licence key

    It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  2. Mid to senior

    The stock table's item_name column was widened from VARCHAR2(60) to VARCHAR2(120). That night a stored procedure that prints shelf labels failed with ORA-06502 on two new items, although the table had accepted them. What happened, how would you fix the procedure, and what would %TYPE still not protect?

    What it tests
    Seeing a hand-typed copy of a column's type as the fault, anchoring with %TYPE, and knowing what anchoring leaves to the code: derived values, other sizes and programs outside the database.
    First step
    Find the variable that receives item_name. If it was declared VARCHAR2(60) by hand, an 85-character name raises ORA-06502 when it is assigned, while the table, now 120 wide, takes it. Anchor that variable to the column, then look for every other place that assumed 60.
    Follow-ups

    From the lesson PL/SQL blocks, variables and data types

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  3. Fresher to senior

    A nightly settlement job maps each order's status to an action with a CASE statement on new, paid, shipped and cancelled. After the shop adds a returned status, the job stops with ORA-06592 on the first such order. Explain the failure and how you would change the code and its tests.

    What it tests
    Knowing that a CASE statement without ELSE raises CASE_NOT_FOUND, choosing between a real branch, a clear error and a constraint, and testing every value the column allows.
    First step
    Read the CASE statement: it lists four statuses and has no ELSE, so the fifth raises CASE_NOT_FOUND, ORA-06592. Decide what a returned order must do, add that WHEN, and make any status nobody planned for fail with a message that names it.
    Follow-ups

    From the lesson PL/SQL control structures and iterators

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  4. Mid to senior

    A procedure loads pending orders into an associative array indexed by order id, deletes those that fail a check, then sends the rest with FOR i IN 1 .. v_orders.COUNT. It passed its tests and now fails with ORA-01403. What is wrong, and how would you write the loop on 19c and on 26ai?

    What it tests
    Understanding sparse collections, what COUNT means after DELETE, why 1 .. COUNT reads missing indexes, and the FIRST and NEXT, INDICES OF and PAIRS OF ways to visit only the elements that exist.
    First step
    Indexed by order id, the array holds elements at ids such as 4107 and 4112, not at 1, 2 and 3, and the deletes add more gaps. COUNT is only the number of elements left, so the loop asks for indexes that hold nothing. Walk the indexes that exist instead.
    Follow-ups

    From the lesson PL/SQL control structures and iterators

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  5. Mid to staff

    Your team wrote a billing package on Oracle AI Database 26ai with FOR loops that use BY and WHEN, and simple CASE statements with dangling predicates. A client must run it on Oracle Database 19c. How do you plan the port, and how do you show that the 19c version behaves the same?

    What it tests
    Knowing which constructs came in 21c and 26ai, rewriting them into classic loops and searched CASE without changing meaning, and proving it with the same boundary tests on both releases.
    First step
    List every construct newer than 19c: iteration controls from 21c, extended CASE from 26ai, and SQL BOOLEAN columns if any. Rewrite each in its classic form, keeping the order of the WHEN clauses, then run one set of boundary tests on both databases and compare the results.
    Follow-ups

    From the lesson PL/SQL control structures and iterators

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  6. Fresher to senior

    A procedure finds a customer with SELECT customer_id INTO v_id FROM customers WHERE LOWER(email) = LOWER(p_email). After a partner's data load, some calls fail with ORA-01422, although the email column has a unique constraint. Explain how that can happen and how you would fix the code and the data.

    What it tests
    Knowing that SELECT INTO raises TOO_MANY_ROWS when two rows match, that a unique constraint compares exact strings, and choosing between an index on LOWER(email), a data clean-up and an explicit handler.
    First step
    Run the query without INTO for one failing email. It returns two rows whose emails differ only in letter case. The unique constraint compares the strings exactly, so it accepted both, while the lookup lowers both sides and finds two. Then decide with the data's owner which row is the real customer.
    Follow-ups

    From the lesson SQL inside PL/SQL: SELECT INTO and DML

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  7. Mid to staff

    A nightly job loops over 500 payments and calls a shared procedure, post_payment, for each one; post_payment ends with COMMIT. Payment 312 raises an error that the job does not handle. What state is the data in, why is a rerun risky, and how would you redesign the job?

    What it tests
    Knowing that a COMMIT inside a procedure ends the caller's transaction, what the failure undoes and keeps, and designing a caller-owned unit of work or a restartable batch that records its progress.
    First step
    Count what is stored. Each of the 311 payments before the failure was committed by post_payment itself, and only the unfinished work of payment 312 was undone. A rerun from the start would post those 311 a second time. Next, move the COMMIT out of post_payment and decide what one unit of work is.
    Follow-ups

    From the lesson SQL inside PL/SQL: SELECT INTO and DML

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  8. Fresher to senior

    Checkout code inserts an order and then runs SELECT MAX(order_id) INTO v_id FROM orders to attach the order's lines to it. Under load, some lines end up on other customers' orders. Explain why, and what you would write instead.

    What it tests
    Seeing the race between the INSERT and the second query, knowing that the database assigns identity values, and using RETURNING INTO with its rules for zero and several rows.
    First step
    Between the INSERT and the SELECT MAX, another session can insert its own order, and MAX then returns that session's id. Ask the INSERT for the id it generated, with RETURNING order_id INTO v_id, so that no second query and no gap remain.
    Follow-ups

    From the lesson SQL inside PL/SQL: SELECT INTO and DML

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  9. Mid to senior

    A nightly job updates stock prices from a supplier file, row by row, and has reported success for weeks, yet 300 of 12,000 items still carry old prices. Each row's UPDATE sits in a block ending in WHEN OTHERS THEN NULL. Explain what happened, how you would fix the job, and how you would find similar handlers.

    What it tests
    Seeing that an OTHERS handler that does nothing turns every error into success, logging and re-raising instead, handling the expected row errors by name, and finding such handlers with compile-time warnings.
    First step
    Run the UPDATE by hand for one of the 300 items, without the handler, and read the error it raises, for example a price that breaks a check constraint. Then list what kinds of row error the file can cause, decide which ones the job may skip with a record of the item, and switch compile-time warnings on before recompiling the job.
    Follow-ups

    From the lesson PL/SQL exceptions and how they propagate

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 4 minutes

  10. Mid to senior

    A procedure declares v_limit NUMBER(5,2) := limit_for(p_card); and handles VALUE_ERROR by logging the card and using a limit of 500.00. Since limits of 1500.00 were added, calls for those cards fail with ORA-06502 and the log stays empty. Why does the handler never run, and how would you restructure the procedure?

    What it tests
    Knowing which handlers can see an error raised while a declaration gets its value, working out what NUMBER(5,2) can hold, and restructuring the procedure and judging its fallback of 500.00.
    First step
    Work out the largest value NUMBER(5,2) can hold and compare it with 1500.00. Then mark where the failing assignment runs: before or after the procedure's BEGIN. Ask which handlers can see an error raised at that point, and whether falling back to 500.00 is right for a card whose limit is 1500.00.
    Follow-ups

    From the lesson PL/SQL exceptions and how they propagate

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  11. Mid to staff

    Six teams call your payments package. It raises errors with RAISE_APPLICATION_ERROR, but its numbers grew ad hoc: -20001 means three different things, and one procedure uses -19050. Design the error contract callers can rely on: the numbers, how callers catch them by name, and how the original Oracle error stays visible.

    What it tests
    Knowing the range of application error numbers and what happens outside it, giving each number one meaning, letting every caller catch the errors by name, and keeping the original error visible when translating.
    First step
    List every RAISE_APPLICATION_ERROR call with its number and its meaning, and test what a caller actually receives from the call that uses -19050. Then give each meaning a number of its own from the range, and publish the numbers as named exceptions that every caller can see.
    Follow-ups

    From the lesson PL/SQL exceptions and how they propagate

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 4 minutes

All PL/SQL & T-SQL (Oracle and SQL Server) interview questions · The PL/SQL & T-SQL (Oracle and SQL Server) track