Top 100 PLSQL Interview Questions

 

Top 100 PL/SQL Interview Questions (Sections-wise)

1. Basics (1–15)

  1. What is PL/SQL and its advantages over SQL?

  2. What are the differences between SQL and PL/SQL?

  3. What are the components of a PL/SQL block?

  4. What are anonymous blocks? Give an example.

  5. Explain %TYPE and %ROWTYPE.

  6. What are the data types available in PL/SQL?

  7. Difference between CHAR and VARCHAR2 in PL/SQL.

  8. What are literals and identifiers?

  9. Explain DECLARE, BEGIN, EXCEPTION, END in PL/SQL block.

  10. Difference between PL/SQL variables and constants.

  11. What is the difference between BOOLEAN and NUMBER for flags?

  12. Explain nested blocks in PL/SQL.

  13. How are NULLs handled in PL/SQL?

  14. What are PL/SQL expressions and operators?

  15. Explain control statements in PL/SQL (IF, CASE, LOOP).

2. Cursors (16–30)

  1. What is a cursor? Difference between implicit and explicit cursors.

  2. How do you declare a cursor in PL/SQL?

  3. What is %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN attributes?

  4. How do you fetch records using a cursor?

  5. Difference between FOR loop cursor and WHILE loop cursor.

  6. How do you pass parameters to a cursor?

  7. What is a REF CURSOR? Difference between weak and strong REF CURSOR.

  8. Explain cursor FOR loop.

  9. How to use multiple cursors in PL/SQL.

  10. What are cursor variables?

  11. Difference between explicit cursor and cursor variable.

  12. How to close a cursor explicitly?

  13. How to handle exceptions in cursors?

  14. Can a cursor return more than one row?

  15. Difference between implicit cursor and cursor FOR loop.

3. Procedures and Functions (31–45)

  1. Difference between procedure and function.

  2. Syntax to create a procedure.

  3. How to create a function in PL/SQL.

  4. Difference between stored and anonymous procedures/functions.

  5. Explain IN, OUT, IN OUT parameters.

  6. Can a procedure return a value?

  7. How to call a procedure from SQL*Plus or another PL/SQL block?

  8. Difference between procedure overloading and function overloading.

  9. How to handle exceptions inside procedures/functions?

  10. Can a function perform DML operations?

  11. Difference between autonomous transaction and normal procedure.

  12. Explain PRAGMA AUTONOMOUS_TRANSACTION.

  13. How to pass a table/record as parameter?

  14. How to debug PL/SQL procedures and functions.

  15. How to handle NULL input parameters in functions/procedures.

4. Triggers (46–60)

  1. What is a trigger?

  2. Types of triggers in Oracle (BEFORE, AFTER, INSTEAD OF).

  3. Difference between row-level and statement-level triggers.

  4. How to create a trigger in PL/SQL?

  5. What are INSTEAD OF triggers?

  6. What are compound triggers?

  7. Difference between BEFORE INSERT and AFTER INSERT triggers.

  8. Can a trigger call a procedure?

  9. What are mutating table errors?

  10. How to avoid mutating table errors?

  11. Explain trigger timing and events.

  12. Can triggers perform COMMIT or ROLLBACK?

  13. Difference between system triggers and DML triggers.

  14. How to disable or drop a trigger?

  15. Use cases for triggers in real projects.

5. Packages (61–70)

  1. What is a package in PL/SQL?

  2. Advantages of using packages.

  3. Difference between package specification and package body.

  4. How to create a package.

  5. Can a package have private procedures/functions?

  6. How to initialize a package using package initialization block?

  7. Difference between public and private objects in a package.

  8. How does package help in performance optimization?

  9. How to call a package procedure/function?

  10. Difference between standalone procedure and packaged procedure.

6. Performance & Optimization (71–80)

  1. How to improve PL/SQL performance?

  2. Difference between BULK COLLECT and normal SELECT INTO loop.

  3. Explain FORALL statement.

  4. Difference between implicit and explicit cursors in terms of performance.

  5. What is SQL%ROWCOUNT usage?

  6. How to avoid context switching in PL/SQL.

  7. Difference between PL/SQL tables, VARRAYs, nested tables.

  8. Explain use of indexing in PL/SQL performance.

  9. Difference between PL/SQL collections and database tables.

  10. How to profile PL/SQL code using DBMS_PROFILER.

7. Advanced PL/SQL (81–90)

  1. What is dynamic SQL? Explain DBMS_SQL and EXECUTE IMMEDIATE.

  2. Difference between static and dynamic SQL.

  3. How to handle exceptions in dynamic SQL.

  4. What are PL/SQL collections? Explain types.

  5. Difference between associative array, nested table, and VARRAY.

  6. How to loop through collections efficiently.

  7. What is bulk binding?

  8. How to implement recursive PL/SQL procedures/functions.

  9. Difference between autonomous transaction and regular transaction.

  10. Explain the use of DBMS_OUTPUT.PUT_LINE and debugging.

8. Exception Handling (91–95)

  1. How exceptions are handled in PL/SQL.

  2. Difference between predefined and user-defined exceptions.

  3. How to raise an exception manually.

  4. What is RAISE_APPLICATION_ERROR?

  5. Difference between NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE exceptions.

9. Miscellaneous / Scenario-based (96–100)

  1. How to implement a hierarchy using recursive queries in PL/SQL.

  2. Difference between triggers and constraints.

  3. How to create log tables and auditing using triggers.

  4. How to handle large objects (CLOB, BLOB) in PL/SQL.

  5. Explain PL/SQL packages for real-time project use cases.

No comments:

Post a Comment

Subscribe to: Comments (Atom)

About This Blog

SQL and PL/SQL are essential for database management, enabling efficient data retrieval, manipulation, and transaction control. SQL (Structu...

  • About This Blog
    SQL and PL/SQL are essential for database management, enabling efficient data retrieval, manipulation, and transaction control. SQL (Structu...