Top 65 PL/SQL Interview Questions and Answers [2023]

CTA

PL/SQL is an extension of SQL and can be referred to as Procedural Language/SQL. It allows users to build complex database applications with the help of procedures, modules, functions, control structures, and more. This blog aims to discuss some of the most popular questions that recruiters of major companies generally ask during job interviews. Here is a list of the interview questions along with their answers:

Q1. Compare SQL and PL/SQL
Q2. What is PL/SQL?
Q3. What is the basic structure of PL/SQL?
Q4. What is a Trigger and what are its uses?
Q5. What Data Types are Present in PL/SQL?
Q6. What are the Basic Parts of a Trigger?
Q7. How is the Process of PL/SQL Compiled?
Q8. What is a Join?
Q9. What is a View?
Q10. What does a PL/SQL Package Consist Of?

This blog on PL/SQL interview questions is divided into three parts:

1. Basic Interview Questions

2. Intermediate Interview Questions

3. Advanced Interview Questions

Watch this video about PL/SQL vs. SQL on YouTube:

Basic PL/SQL Interview Questions for Freshers

1. Compare SQL and PL/SQL.

2. What is PL/SQL?

Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration and conditional branching.

Go through this PL/SQL Tutorial to learn what is PL/SQL?

3. What is the Basic Structure of PL/SQL?

PL/SQL uses a block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

CTA

4. What is a Trigger and what are its uses?

A trigger is a database object that automatically executes in response to some events on the tables or views. It is used to apply the integrity constraint to database objects.

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for:

• Audit data modifications
• Log events transparently
• Enforce complex business rules
• Maintain replica tables
• Derive column values
• Implement complex security authorizations

Any constant, variable, or parameter has a data type depending on which the storage constraints, format, and range of values and operations are determined.

5. What Data Types are Present in PL/SQL?

There are various kinds of data types present in PL/SQL. They are:

1. Scalar: The scalar data type is a one-dimensional data type with no internal components. CHAR, DATE, LONG, VARCHAR2, NUMBER, and BOOLEAN are some examples of the scalar data type.
2. Composite: The composite data type is made up of different data types that are easy to update and have internal components that can be utilized and modified together. For instance, RECORD, TABLE, VARRAY, and so on.
3. Reference: The reference data type stores pointers, which are values that relate to other programs or data elements. REF CURSOR is an example of the reference data type.
4. Large Object: The large object data type stores locators, which define the location of large items stored out of line such as video clips, graphic images, and so on. BLOB, BFILE, CLOB, and NCLOB are examples of the large object data type.

6. What are the Basic Parts of a Trigger?

There are three basic parts of a trigger. They are:

• A triggering statement or event
• A restriction
• An action

7. How is the Process of PL/SQL Compiled?

Syntax checking, binding, and P-code generation are all part of the compilation process. Syntax checking looks for compilation issues in PL/SQL code. After all mistakes have been fixed, the data holding variables are given a storage address. This process is referred to as binding. The PL/SQL engine’s P-code is a set of instructions. For named blocks, P-code is saved in the database and used the next time it is run.

Go through the Handling PL/SQL Errors tutorial page to know how error handling is done in PL/SQL!

8. What is a Join?

A join is a query that combines rows from two or more tables, views, or materialized views. A join is performed by the Oracle Database whenever there are multiple tables in the FROM clause of the query. Most of these queries contain at least one join condition, either in the FROM or WHERE clause.

9. What is a View?

A view is created by joining one or more tables. It is a virtual table that is based on the result set of an SQL statement; it contains rows and columns, just like a real table. A view can be created with the CREATE VIEW statement.

Intermediate PL SQL Interview Questions

10. What Does a PL/SQL Package Consist Of?

A PL/SQL package consists of:

• PL/SQL table and record type statements
• Procedures and functions
• Cursors
• Variables, such as tables, scalars, records, etc., and constants
• Exception names and pragmas for relating an error number with an exception
• Cursors

Check out this insightful PL/SQL tutorial to learn more about Pl/SQL Packages!

11. What do you know about the Commands COMMIT, ROLLBACK, and SAVEPOINT?

COMMIT: The COMMIT command saves changes to a database permanently during the current transaction.

ROLLBACK: The ROLLBACK command is used at the end of a transaction to undo any modifications made since the start of the transaction.

SAVEPOINT: The SAVEPOINT command saves the current point with a unique name during the processing of a transaction.

12. What are the Benefits of PL/SQL Packages?

PL/SQL packages provide several benefits. Some of them are as follows:

• Enforced Information Hiding: It offers the liberty to choose whether to keep the data private or public.
• Top-down Design: We can design the interface to the code hidden in the package before we actually implement the modules.
• Object Persistence: Objects declared in a package specification behave like global data for all PL/SQL objects in the application. We can modify the package in one module and then reference those changes in another
• Object-oriented Design: The package gives developers a stronghold over how the modules and data structures inside the package can be used.
• Guaranteeing Transaction Integrity: It provides a certain level of transaction integrity.
• Performance Improvement: The RDBMS automatically tracks the validity of all program objects stored in the database. It also enhances the performance of packages.

Master PL/SQL by enrolling in this top-rated PL/SQL Certification Training!

13. What is Exception Handling?

Exception handling is a mechanism that is implemented to deal with runtime errors. It can be adjusted in PL/SQL. PL/SQL provides the exception block that raises the exception, thus helping the programmer to find the fault and resolve it. When an error occurs, the program’s error handling code is included. There are two different types of exceptions defined in PL/SQL:

• User-defined exception
• System-defined exception

14. Mention a Few Predefined Exceptions

The following are some examples of predefined exceptions:

• NO DATA FOUND: A single-row SELECT statement that returns no data
• TOO MANY ROWS: A single row SELECT statement that returns many rows
• INVALID CURSOR: An incorrect cursor operation is performed
• ZERO DIVIDE: An attempt at zero division

15. What are the Various Types of SQL Statements?

The five types of SQL statements are as follows:

• DDL: Data definition language (DDL) helps in the creation of a database structure or schema. CREATE, DROP, ALTER, RENAME, and TRUNCATE are the five types of DDL commands in SQL.
• DML: Data manipulation language (DML) allows you to insert, change, and delete data from a database instance. DML is in charge of making all kinds of changes to a database’s data. The database application and the user can insert data and information using three basic commands—INSERT, UPDATE, and DELETE.
• DCL: GRANT and REVOKE are the commands in the data control language (DCL) that can be used to grant rights and permissions. The database system’s parameters are controlled by other permissions.
• TCL: Transaction control language (TCL) commands deal with database transactions. Some of the TCL commands are COMMIT, ROLLBACK, and SAVEPOINT.
• DQL: Data query language (DQL) is used to retrieve data from the database. It just has one command, which is SELECT.

16. What are the Different Methods to Trace the PL/SQL Code?

Tracing the code is a crucial technique to measure its performance during the runtime. The different methods of tracing the code include:

• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION and DBMS_MONITOR
• trcsess and tkprof utilities

17. What are the Various Types of Parameters in PL/SQL?

There are three types of parameters in PL/SQL. They are as follows:

• IN: The IN parameter allows you to send values to the procedure that is being called. The IN parameter can be set to default values. It behaves as a constant and cannot be changed.
• OUT: The OUT parameter returns a value to the caller. The OUT parameter is an uninitialized variable that cannot be used in expressions.
• IN OUT: The IN OUT parameter sends starting values to a procedure and returns the updated values to the caller. This parameter should be treated as an initialized variable and given a value.

CTA

18. What are PL/SQL Records?

PL/SQL records are a collection of values. To put it another way, PL/SQL records are a collection of many pieces of information, each of which is of a simpler type and can be associated with one another as fields.

Three types of records are supported in PL/SQL:

• Records based on tables
• Records created by programmers
• Records that are based on a cursor

19. Why do we use Index in a Table?

We use an index in a table to allow quick access to rows. For procedures that return a small percentage of a table’s rows, an index allows quicker access to data.

20. What is the Difference among Functions, Procedures, and Packages in PL/SQL?

• Functions: The main purpose of PL/SQL functions is to compute and return a single value. The functions have a return type in their specifications and must return a specified value in that type.
• Procedures: Procedures do not have a return type and should not return any value, but they can have a return statement that simply stops its execution and returns to the caller. Procedures are used to return multiple values; otherwise, they are generally similar to functions.
• Packages: Packages are schema objects that group logically related PL/SQL types, items, and subprograms. You can also say that packages are a group of functions, procedures, variables, and record TYPE statements. Packages provide modularity, which aids in application development. Packages are used to hide information from unauthorized users.

21. Why are Database Links Used?

Database links are used to establish communication across multiple databases or environments such as test, development, and production. Other information can also be accessed using database links, which are read-only.

22. What is a Stored Procedure?

A stored procedure is a sequence of statements or a named PL/SQL block that performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as a schema object and can be nested, invoked, and parameterized.

23. What is the Overloading of a Procedure?

When the name of the same procedure is repeated with the parameters of different data types and parameters in different places, then that is referred to as procedure overloading.

24. What is meant by Expressions?

Expressions are made up of a series of literals and variables that are separated by operators. Operators are used in PL/SQL to manipulate, compare, and calculate data. Expressions are made up of two parts, operators and operands.

25. Which Cursor Attributes are the Result of a Saved DML Statement, when it is Executed?

The statement’s result is saved in four cursor attributes. The four attributes are:

• SQL% FOUND
• SQL% NOTFOUND
• SQL% ROWCOUNT
• SQL% ISOPEN

26. What is a Cursor? Why is it Required?

A cursor is a temporary work area that is created in system memory when an SQL statement is executed. A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data, which is retrieved from the database, to manipulate it. A cursor can hold more than one row but can process only one row at a time. A cursor is required to process rows individually for queries.

27. What are the Types of Cursors?

There are two types of cursors:

• Implicit Cursor: When PL/SQL executes an SQL statement, it automatically constructs a cursor without specifying one; these cursors are known as implicit PL/SQL uses implicit cursors for the following statements:
  • INSERT
  • UPDATE
  • DELETE
  • SELECT

• Explicit Cursor: A programmer declares and names an explicit cursor for the queries that return more than one row. An explicit cursor is a SELECT statement that is declared explicitly in the current block’s declaration section or in a package definition. The following are the commands that are used for explicit cursors in PL/SQL:
  • OPEN
  • FETCH
  • CLOSE

28. What is the Open Cursor Command Function?

When the OPEN cursor command is used to open a cursor, it performs the following operations:

• Set aside a processing memory region
• Paese the statement SELECT
• Use the memory addresses to assign values to input variables
• Recognize the active set of rows that meet the selection criteria
• Place the pointer exactly before the active set’s first row

29. How to Delete a Trigger?

To delete a trigger, you need to use the command DROP TRIGGER.

30. What are the Advantages of Stored Procedures?

Stored procedures have various advantages to help you design sophisticated database systems. Some of the advantages of stored procedures as listed below:

• Better performance
• Higher productivity
• Ease of use
• Increased scalability
• Interoperability
• Advance security
• Replication

31. What are the Various Types of Schema Objects that can be Created by PL/SQL?

There are various types of schema objects that are created by PL/SQL. Some of them are mentioned below:

• Stored procedures, packages, and functions
• Object tables, object types, and object views
• Database triggers
• Database links
• Cursors
• Table
• View

32. What is the Advantage of Implicit Records?

Implicit records are handy since they do not require hard-coded descriptions. Because implicit records are based on database table records, any changes to the database table records will be reflected in the implicit records automatically.

33. What are the Ways of Commenting in PL/SQL?

In PL/SQL, comments help readability by describing the purpose and function of code portions. Two types of comments are available in PL/SQL. They are as follows:

• Single-line Comments: Single-line comments start with a double hyphen (- -) at the beginning of a line and go all the way to the conclusion.
• Multi-line Comments: Multi-line comments start with a slash-asterisk (/*) and terminate with an asterisk-slash (*/), and they can span across several lines.

34. What is %TYPE?

The %TYPE property is used to declare a column in a table that includes the value of that column. The variable’s data type is the same as the table’s column.

35. What is %ROWTYPE?

The %ROWTYPE property is used to declare a variable that contains the structure of the records in a table. The variable’s data type is the same as the table’s columns.

36. Differentiate between a Temporary Tablespace and a Permanent Tablespace

A temporary tablespace is used to store temporary items such as sort structures, while a permanent tablespace is used to store things that will be used as the database’s genuine objects.

37. How Many Triggers can be Applied to a Table?

A maximum of 12 triggers can be added to a table.

38. What is a Mutating Table Error?

A mutating table error occurs when a trigger tries to update a row that is currently in use. It can be fixed by using views or temporary tables so that the database selects one and updates the other.

39. What does the PLVtab Enable you to do when you Show the Contents of PL/SQL Tables?

PLVtab enables you to do following when you show the contents of PL/SQL tables:

• Display or suppress the row numbers for the table values
• Show a prefix before each row of the table
• Display or suppress a header for the table

40. How can you Save or Place your msg in a Table?

To save a msg in a table, you either load the individual messages with calls to the add_text procedure or load sets of messages from a database table using the load_from_dbms procedure.

Wish to learn more? Visit the PL/SQL Collections and Records tutorial page!

Advanced PL/SQL Interview Questions and Answers for Experienced Professionals

41. What are Pseudocolumns and how do they work? How can Pseudocolumns be used in Procedure Statements?

Pseudocolumns aren’t genuine table columns but they behave like them. Pseudocolumns are used to retrieve specific information in SQL statements. Although pseudocolumns are recognized by PL/SQL as part of SQL statements, they cannot be used directly in a procedural language. The following are the pseudocolumns that are used:

• CURRVAL and NEXTVAL
• LEVEL
• ROWID
• ROWNUM

42. What is the use of the || Operator?

The strings are concatenated using the || operator. The || operator is employed by both DBMS_OUTPUT.put line and select statements.

43. What is the difference between Sqlcode and Sqlerrm, and why are they important for Pl/SQL Developers?

The value of the error number for the most recent error detected is returned by SQLCODE. The SQLERRM function returns the actual error message for the most recent issue. They can be used in exception handling to report or save the error that happened in the code in the error log database. These are especially important for the exception WHEN OTHERS.

44. What is raise_application_error?

This procedure can be used to send user-defined error messages from stored subprograms. You can prevent returning unhandled exceptions by reporting failures to your application. It appears in two places, the executable section and the exceptional section.

45. In PL/SQL, how can you verify whether an Update Statement is Executed or not?

The SQL % NOTFOUND attribute can be used to determine whether or not the UPDATE statement successfully changed any records. If the last SQL statement run had no effect on any rows, this variable returns TRUE.

46. Explain the Day-to-day Activities in PL/SQL.

• Create database objects—tables, synonyms, sequences, etc.
• Implement business rules, create procedures, functions, etc.
• Impose business rules, create constraints, triggers, etc.
• Create cursors for data manipulation

47. How can you locate a PL/SQL Block when a Cursor is Open?

The %ISOPEN variable cursor status can be used to find the PL/SQL block.

48. What do you know about pragma_exception_init in PL/SQL?

The pragma_exception_init command in PL/SQL instructs the compiler to associate an exception name with an Oracle error number. This enables one to refer to any internal exception by name and create a custom handler for it.

49. In PL/SQL, what are the differences between Stored Procedure and Stored Function?

The key differences between stored procedure and stored function are:

• Returning the value in a stored procedure is optional, while returning the value in a stored function is required.
• A stored procedure can have both input and output parameters, while a stored function can only have either an input parameter or an output parameter.
• Exception handling is possible in a stored procedure, whereas it is not possible in a stored function.

50. How to Display Records having the Maximum Salary from an Employee Table?

Select * from emp where sal= (select max(sal) from emp)

If you have any doubts or queries related to PL/SQL, get them clarified from our PL/SQL experts on our SQL Community!

51. What is the Syntax to Disable a Trigger?

ALTER TRIGGER TRIGGER_NAME DISABLE;

52. How to Display the Highest Salary from an Employee Table?

Use the following code to display the highest salary from an employee table:

Select max(sal) from emp;

53. Which Command is used for Deleting a Package?

The command used for deleting a package is DROP PACKAGE

54. How to Display the Second Highest Salary from an Employee Table?

Select max(sal) from emp where sal not in ( select max(sal) from emp

55. How can you view the User-defined Functions and Procedures in PL/SQL?

The table USER SOURCE is used to store user-defined functions and procedures. To examine them, the function and procedure names should be specified in uppercase (in select command). The following command is used to inspect the source code of a user-defined function or method:

Select text from user_source where name=’PROCEDURE_NAME’;

56. What is a Join?

Join is a keyword that is used to query data from multiple tables based on the relationship between the fields of tables. Keys play a major role in Joins.

57. In PL/SQL, what is the Purpose of the DBMS_OUTPUT Package?

The PL/SQL output is shown on the screen using the DMS_OUTPUT package. get_line, put_Line, new_line, and many more are found in DBMS_OUTPUT. The put_line procedure, which is a part of the DBMS_OUPUT package, is used to display the information in the line.

58. What is a View?

• A view is a virtual table consisting of the data contained in a table.
• A view does not need any memory space.
• A view can be created on multiple tables.

59. How can you Execute a Stored Procedure?

There are two steps to execute a stored procedure: 

• Use the EXECUTE keyword. The EXEC keyword can also be used.
• Call the name of the procedure from a PL/SQL block.

Syntax
EXECUTE procedure_name;
Or
Exec  procedure_name;

60. What are the differences between ANY and ALL operators?

ALL Operator: Value is compared to every value returned by the subquery using the ALL operator.

• • > ALL denotes greater than the maximum
  • < ALL denotes less than the minimum
  • <> ALL is the same as NOT IN condition

ANY Operator: Value is compared to each value returned by the subquery using the ANY operator. SOME is a synonym for ANY operator.

• • > ANY denotes something more than the bare minimum
  • < ANY denotes a value lower than the maximum
  • = ANY is the same as the IN operator

61. How to Create a Function?

The syntax to create a CREATE function is below:

CREATE function_name
RETURN return_datatype
{IS | AS}
DECLARE
VARIABLE DATATYPE;
BEGIN
function_body
END function_name;

62. How can you Switch from an Init.ora File to Spfile?

One can switch from Init.ora file to Spfile by creating a spfile from the pfile command.

63. What is a Subquery? What are Its Types?

A subquery is a query within another query. The outer query is known as the main query and the inner query is called the subquery. A subquery is executed first, and the result of the subquery is passed to the main query.

There are two types of subqueries:

• Correlated
• Non-correlated

64. How can you Read/Write Files in PL/SQL?

One can read/write operating system text files by using the UTL_FILE package. It provides a restricted version of the operating system stream file I/O, and it is available for both client-side and server-side PL/SQL.

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp','myoutput','z');
UTL_FILE.PUTF(file, 'Value of func1 is %sn',func1(2));
UTL_FILE.FCLOSE(file;
END;

65. How do you Create Nested Tables in PL/SQL?

In PL/SQL, one of the collection types is nested tables. They can be made in a PL/SQL block or at the schema level. These are similar to a 1D array, except their size can be dynamically extended.

The syntax to create a nested table is as follows:

TYPE type_name IS TABLE OF element_type [NOT NULL];
name_of_table type_name;
An example for creating nested tables in PL/SQL is as follows:
DECLARE
TYPE deptname IS TABLE OF VARCHAR2(10);
TYPE budget IS TABLE OF INTEGER;
names deptname;
deptbudget budget;
BEGIN
names := deptname ('Finance', 'Sales', 'Marketing');
deptbudget := budget (89899, 67879, 98999);
FOR i IN 1 .. names.count LOOP
dbms_output.put_line('Department = '||names(i)||', Budget = ' || deptbudget(i));
end loop;
END;
/

Learn from our SQL Server Interview Questions to crack interviews at top companies.