PL/SQL Interview Questions and Answers
1. What is PL/SQL?
PL/SQL stands for Procedural Language/SQL. It is Oracle’s extension of SQL that combines SQL commands with
procedural programming features like loops, conditions, variables, and exception handling.
2. What are the features of PL/SQL?
Main features include block structure, exception handling, loops, cursors, triggers, procedures, functions, packages,
and better performance through reduced network traffic.
3. How is PL/SQL different from SQL?
SQL is used only for database operations like SELECT, INSERT, UPDATE, and DELETE. PL/SQL supports
programming concepts such as loops, conditions, variables, and exception handling along with SQL.
4. Why was PL/SQL introduced when SQL already existed?
SQL cannot handle complex programming logic. PL/SQL was introduced to provide procedural capabilities such as
decision-making, looping, modular programming, and error handling.
5. Difference between Procedure and Function
A procedure performs an action and may or may not return a value. A function must return a value and is generally
used in SQL expressions.
6. What are the data types in PL/SQL?
PL/SQL supports scalar data types (NUMBER, VARCHAR2, DATE), composite types (RECORD, TABLE), reference
types, and large object types (BLOB, CLOB).
7. Write a basic PL/SQL block
DECLARE
num NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE(num);
END;
8. Explain loops and their types
PL/SQL supports LOOP, WHILE LOOP, and FOR LOOP. Loops are used to execute a block of code repeatedly until
a condition is satisfied.
9. Benefits of Packages
Packages help organize related procedures and functions together. They improve modularity, security, code
reusability, and performance.
10. Use of COMMIT and ROLLBACK
COMMIT permanently saves changes in the database. ROLLBACK undoes changes made during the current
transaction.
11. How do you debug PL/SQL code?
PL/SQL code can be debugged using DBMS_OUTPUT.PUT_LINE, SQL Developer debugger tools, exception
handling, and log messages.
12. What is a Trigger?
A trigger is a stored program that automatically executes when events like INSERT, UPDATE, or DELETE occur on a
table.
13. What is an Index?
An index improves the speed of data retrieval operations on a table. It works like a book index for faster searching.
14. Difference between %TYPE and %ROWTYPE
%TYPE is used to declare a variable with the same datatype as a table column. %ROWTYPE is used to store an
entire row of a table.
15. Difference between Implicit and Explicit Cursor
Implicit cursors are automatically created by Oracle for single-row queries. Explicit cursors are created by
programmers for multi-row queries.
16. Difference between BEFORE and AFTER Trigger
BEFORE triggers execute before the database operation occurs. AFTER triggers execute after the operation is
completed.
17. Write a PL/SQL Program
DECLARE
a NUMBER := 5;
b NUMBER := 10;
BEGIN
DBMS_OUTPUT.PUT_LINE('Sum = ' || (a+b));
END;
18. What are User-Defined Exceptions?
User-defined exceptions are custom exceptions created by programmers to handle specific runtime errors.
19. What are Predefined Exceptions?
Predefined exceptions are built-in exceptions automatically raised by Oracle, such as NO_DATA_FOUND and
ZERO_DIVIDE.
20. What are Cursor Attributes?
Cursor attributes provide information about cursor execution. Common attributes are %FOUND, %NOTFOUND,
%ROWCOUNT, and %ISOPEN.
21. How do you optimize PL/SQL code?
Optimization techniques include using indexes, reducing loops, bulk collection, proper exception handling, avoiding
unnecessary queries, and using packages.