Complete PL/SQL Interview Questions with
Answers
1. What is PL/SQL?
PL/SQL stands for Procedural Language/SQL. It is Oracle’s extension of SQL that supports programming features like
loops, conditions, and exception handling.
2. Difference between SQL and PL/SQL
SQL is used only for database queries. PL/SQL supports programming logic along with SQL.
3. What are the features of PL/SQL?
Features include loops, cursors, exception handling, triggers, procedures, functions, and packages.
4. Explain the structure of a PL/SQL block
A PL/SQL block contains DECLARE, BEGIN, EXCEPTION, and END sections.
5. What are variables in PL/SQL?
Variables are used to store values temporarily during program execution.
6. What are the data types in PL/SQL?
Common data types are NUMBER, VARCHAR2, DATE, BOOLEAN, BLOB, and CLOB.
7. Difference between %TYPE and %ROWTYPE
%TYPE stores datatype of a single column. %ROWTYPE stores an entire row.
8. What is a procedure?
A procedure is a stored program used to perform a specific task.
9. What is a function?
A function is a stored program that always returns a value.
10. Difference between procedure and function
Procedure may or may not return value. Function must return a value.
11. Can a function return multiple values?
Directly no, but it can return multiple values using OUT parameters or records.
12. What are IN, OUT, and IN OUT parameters?
IN sends value to procedure, OUT returns value, IN OUT does both.
13. What are conditional statements in PL/SQL?
IF, IF-ELSE, and CASE statements are conditional statements.
14. Explain IF-ELSE statement
It executes different blocks based on conditions.
15. What are loops in PL/SQL?
Loops repeat a block of code multiple times.
16. Difference between FOR LOOP and WHILE LOOP
FOR LOOP runs fixed times. WHILE LOOP runs until condition becomes false.
17. What is a cursor?
Cursor is a pointer used to fetch multiple rows from a query.
18. Difference between implicit and explicit cursor
Implicit cursor is created automatically by Oracle. Explicit cursor is created manually by programmer.
19. What are cursor attributes?
Cursor attributes are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
20. What is parameterized cursor?
A cursor that accepts parameters during execution.
21. What is exception handling?
Exception handling is used to handle runtime errors.
22. Difference between predefined and user-defined exceptions
Predefined exceptions are built-in. User-defined exceptions are created by programmer.
23. What is RAISE_APPLICATION_ERROR?
It is used to create custom error messages.
24. How do you handle exceptions in PL/SQL?
Exceptions are handled using EXCEPTION block.
25. What is a trigger?
Trigger is a stored program executed automatically on INSERT, UPDATE, or DELETE events.
26. Types of triggers in PL/SQL
BEFORE, AFTER, INSTEAD OF, row-level, and statement-level triggers.
27. Difference between BEFORE and AFTER trigger
BEFORE trigger runs before event. AFTER trigger runs after event.
28. What is a row-level trigger?
It executes once for each affected row.
29. What is a statement-level trigger?
It executes once for the entire SQL statement.
30. What is a package?
Package is a collection of related procedures, functions, variables, and cursors.
31. Advantages of packages
Packages improve modularity, security, and performance.
32. Difference between package specification and package body
Specification declares objects. Body contains implementation.
33. What is COMMIT?
COMMIT permanently saves changes in database.
34. What is ROLLBACK?
ROLLBACK undoes changes before commit.
35. What is SAVEPOINT?
SAVEPOINT creates a point to rollback partially.
36. What is transaction control in PL/SQL?
It manages database transactions using COMMIT, ROLLBACK, and SAVEPOINT.
37. What is an index?
Index improves data retrieval speed.
38. Types of indexes in Oracle
Primary index, unique index, bitmap index, composite index.
39. How do you optimize PL/SQL code?
Using indexes, bulk collect, proper joins, and reducing loops.
40. What is bulk collect?
Bulk collect fetches multiple rows at once for better performance.
41. Write a PL/SQL block for addition of two numbers
DECLARE a NUMBER:=10; b NUMBER:=20; BEGIN DBMS_OUTPUT.PUT_LINE(a+b); END;
42. Write a program to find even or odd number
Use MOD(number,2)=0 condition to check even or odd.
43. Write a cursor program
Cursor programs fetch multiple rows one by one.
44. Write a trigger example
CREATE TRIGGER trg BEFORE INSERT ON emp FOR EACH ROW BEGIN NULL; END;
45. Write a procedure example
CREATE PROCEDURE demo IS BEGIN NULL; END;
46. Write a function example
CREATE FUNCTION demo RETURN NUMBER IS BEGIN RETURN 1; END;