Today’s world has witnessed the overwhelming production of data every day. Around 2.5 quintillions of data are generated each day, and a study has stated that more than 90% of the world’s data is produced in just the last two to three years.
The immense generation of data has resulted in the need for a suitable and well-structured set of query languages. A query language is a computer language that helps us process a large amount of data and retrieve specific and meaningful data. One of the globally used query languages is PL/SQL.
So, let us begin with what exactly PL/SQL is and what its uses are. We will also highlight career opportunities in PL/SQL that you need to know. Lastly, we will go through frequently asked PL/SQL interview questions along with their elaborated answers.
What is PL/SQL?
Procedural Language extensions to the Structured Query Language is the full form of PL/SQL. It is an exceptional query language as it combines the data manipulation feature of SQL and the processing power offered by procedural languages. Furthermore, the principal aim of PL/SQL is to process all SQL statements smoothly by enhancing a database’s security, sturdiness, and portability.
In the early 90s, Oracle developed PL/SQL to improve the Structured Query Language’s (SQL’s) capabilities. PL/SQL incorporates a wide array of features similar to other programming languages, like collections, iterative and conditional statements, procedures, triggers, arrays, functions, etc.
Career Opportunities in PL/SQL
Individuals wishing to get a job in PL/SQL should have a strong background in SQL, C programming language, databases, and text editors. However, it is easy to learn these skills quickly.
A PL/SQL developer is responsible for developing efficient SQL queries to enhance a database’s performance and analyze the available data to provide robust solutions to end-users.
Here is a list of the skills required to become a PL/SQL developer:
- In-depth understanding of the Oracle database and its tools.
- Thorough knowledge of procedures, C programming language, query writing and execution, and triggers.
- Firm understanding of software development life cycle (SDLC).
- Experience in designing and implementing ETL processes.
- Ability to tune SQL queries’ performance.
- Knowledge of creating, designing, and implementing data models.
While you work as a PL/SQL developer, you will get an opportunity to work with various domains of Oracle. In addition, while developing client-side queries as a PL/SQL developer, you will also get the chance to manage SQL server-based queries.
You can even transform your PL/SQL career into data-level or solution-level architects, as you possess a sound understanding of a PL/SQL code‘s functionalities and features. Furthermore, you can switch to an analyst role, where you need to work with analytical tools to produce meaningful business insights.
Therefore, pursuing a career in the PL/SQL domain offers a wide variety of job roles. Though a PL/SQL developer is the primary role, there are many other roles, such as data analyst, SQL developer, ETL developer, PL/SQL administrator, and PL/SQL performance optimization developer that you can switch to.
20 Top PL/SQL Interview Questions and Answers
Here are the most important Pl/SQL interview questions along with their detailed answers:
1. Define PL/SQL.
Answer: PL/SQL stands for Procedural Language extensions to the Structured Query Language. It is an extended version of SQL, enabling us to use functions, procedures, control structures, etc., to design and create database applications. Furthermore, PL/SQL is a powerful query language that combines SQL and procedural languages’ data manipulation and processing features.
Alternatively, we can say that PL/SQL uses SQL to instruct the compiler about ‘what to do.’ Further, it uses a procedural approach to guide the compiler about ‘how to do.’ PL/SQL also supports object-oriented concepts, loops, and conditional statements analogous to other database languages.
2. State some differences between PL/SQL and SQL.
Answer: Oracle developed PL/SQL to overcome some significant disadvantages of SQL. The following table shows dissimilarities between PL/SQL and SQL:
|It is a natural language used to communicate with databases and stands for Structured Query Language.||It is an extension of SQL and is a Procedural Language/Structured Query Language.|
|SQL does not support procedural features, like looping and condition testing.||As PL/SQL is a procedural language, it supports conditional and looping statements.|
|The database server executes a single SQL query at a time, consuming a lot of time and effort.||In PL/SQL, the database server accepts a complete block of statements and executes all of them simultaneously. Therefore, it requires less time and also eliminates network traffic.|
|SQL does not incorporate any error handling procedure.||You can use customized error handling procedures in PL/SQL.|
|SQL does not have variables.||PL/SQL consists of variables and data types.|
|It is an object-oriented language.||It is an application-oriented language.|
|The primary objective of using SQL is to write and execute queries, DDL and DML command.||PL/SQL supports functions, triggers, program blocks, and packages.|
3. List out significant features of PL/SQL.
Answer: Some of the most notable features of PL/SQL are:
- It supports processing multiple queries in one go through a single PL/SQL command.
- As PL/SQL is a procedural language, it supports looping, branching, and decision making.
- A code, once developed in PL/SQL, can be stored as different units, like functions, triggers, procedures, and packages. In addition, we can use these units for developing other applications.
- PL/SQL incorporates an exception handling block that supports exception handling.
- Furthermore, we can validate and check the data for errors and bugs in PL/SQL before performing manipulations.
- Finally, the code written using PL/SQL is portable. We can execute that code on any operating system or computer hardware having an Oracle engine.
4. Explain different kinds of data types in PL/SQL.
Answer: Data types in computer programming tell us about the type of a variable’s value and what operations can be performed on it. PL/SQL has four different types that are as follows:
- Scalar Data Type
This kind of data type incorporates a single or an atomic value. Therefore, it does not have internal components. Examples of Scalar Data Types are VARCHAR2, CHAR, DATE, LONG, NUMBER, and BOOLEAN.
- Composite Data Type
This kind of data type involves internal components. We can use these internal components and manipulate them easily. Examples of this kind of data type are RECORD, TABLE, and VARRAY.
- Reference Data Type
As its name suggests, a reference data type involves a pointer that refers to other data items or program items. An example of Reference Data Type is REF CURSOR.
- Large Object Data Type
Large Object (LOB) Data Type also involves pointers that refer to large objects. These objects are isolated from other program items, such as images, text, video clips, etc. Examples of LOB Data Types are Binary LOB, Character LOB, NCHAR LOB, and Binary File (BFILE).
5. What are %ROWTYPE and %TYPE data types?
Answer: The PL/SQL’s %TYPE attribute provides a table column’s or a variable’s data type. We use %TYPE when we want to declare variables that hold the values of a table column.
The PL/SQL’s %ROWTYPE attribute declares a variable as a record. However, that variable has the same structure as a row in a table. A record in PL/SQL is similar to the row in a table.
6. Describe PL/SQL packages.
Answer: A PL/SQL package is an object schema that categorizes logically connected subprograms, items, and types. It usually contains subprograms, exceptions, variables, cursors, constants, procedures, and functions. The Oracle database stores compiled PL/SQL packages in it.
A PL/SQL package has two major components – package specification and package body. The package specification component defines public objects. As these objects are public, we can access or fetch them from outside the package. In addition, if the package specification consists of cursors and subprograms, then the package body should contain queries and code for their implementation.
The next component of the PL/SQL package is the package body, containing subprograms’ and cursors’ implementation. It involves queries for the execution of cursors and code for subprograms. Furthermore, the package body allows us to declare private variables. In addition, it involves a dedicated section to handle exceptions.
7. Explain PL/SQL’s basic structure.
Answer: As PL/SQL is a block-structured language, its programs are written and divided into logical code blocks. The structure of the PL/SQL block is:
DECLARE <declaration section> BEGIN <executable commands> EXCEPTION <exceptional handling> END;
Each code block in PL/SQL program consists of three parts:
This part of the PL/SQL block is optional and is used to define all variables, subprograms, and other elements that are to be involved in a PL/SQL program. While defining any element, we must begin with the ‘DECLARE’ keyword.
- Executable Commands:
The next part of the PL/SQL block starts with the keyword ‘BEGIN’. In addition, this section of the PL/SQL block ends with the ‘END’ keyword. This part of the PL/SQL block is mandatory. All executable PL/SQL statements or commands are present between these two keywords.
However, there should be at least one executable statement or command. If we don’t want to execute anything, it should contain the NULL command.
- Exception Handling:
The last part of the PL/SQL block is exceptional handling. Like the declarations part, this part is also optional to use. It involves one or more exceptions that manage errors occurring in the PL/SQL program. Make sure to add this part before the ‘END’ keyword.
8. Define a PL/SQL cursor and list its types.
Answer: A pointer to the memory area or context area, which consists of all SQL statements and information about processing these statements, is called a PL/SQL cursor. A PL/SQL cursor helps us to fetch and process multiple rows at a time. Alternatively, a PL/SQL cursor selects multiple rows from a database, retrieves the data from the rows, and processes each row individually.
Implicit cursor and explicit cursor are two different cursors in PL/SQL.
- Implicit Cursor:
If we execute any SQL statement, like INSERT, DELETE, UPDATE, and SELECT, PL/SQL creates a cursor without defining it. Such a type of cursor is called the implicit cursor.
- Explicit Cursor:
We define an explicit cursor for queries that return two or more rows from a database. However, we need to follow four steps for declaring an explicit cursor:
- Declaring the cursor
- Opening the cursor
- Retrieving row using the cursor
- Closing the cursor
9. Explain triggers in PL/SQL.
Answer: In general, we know that trigger means activate. Triggers in PL/SQL are previously developed or stored programs. An Oracle engine activates or invokes triggers automatically when any database-related events occur. Below are certain events when an Oracle engine invokes or fires triggers:
- A database operation, like SHUTDOWN, SERVERERROR, LOGOFF, STARTUP, and LOGON.
- A data manipulation language (DML) statement, like UPDATE, INSERT, SELECT, and DELETE.
- A data definition language (DDL) statement, like CREATE, ALTER, and DROP.
10. List and explain types of PL/SQL blocks.
Answer: There are two kinds of PL/SQL blocks, as listed below:
- Anonymous Block
Anonymous blocks are those that do not have names assigned to them. We cannot store these blocks as database objects in the server. Also, we need to develop and use anonymous blocks in the same session.
- Named Block
PL/SQL blocks with unique names are known as named blocks. We can store named blocks in the server as database objects. Moreover, it is possible to use named blocks until they are present in the server as database objects.
11. Explain the terms SAVEPOINT, COMMIT, and ROLLBACK.
When we use any DML command in the PL/SQL program, it makes changes to the data present in the database buffer. However, the actual database does not get affected and remains unchanged. We use the COMMIT statement to save transaction changes or changes made to the rows after using any DML command to the database permanently.
If we wish to undo any change made in the current transaction, we can use the ROLLBACK statement. This statement stops the current transaction from running and undoes modifications made during the transaction.
For example, consider that we have deleted a wrong row from the database, and we need to restore that deleted row. In such a scenario, we must use the ROLLBACK statement.
The SAVEPOINT statement is ideal for longer transactions. This statement divides a longer transaction into smaller sections and marks points in a transaction. Unlike the ROLLBACK statement, the SAVEPOINT statement enables only a part of a transaction to roll back.
Syntax: SAVEPOINT <SAVEPOINT_NAME>;
12. State the difference between syntax errors and runtime errors.
Answer: Runtime errors in PL/SQL are the errors the exception-handling section of the PL/SQL block handles. One of the examples of the runtime error is the SELECT statement that does not return a single row from the database. All locks and changes made to the transactions before using SAVEPOINT are preserved.
Syntax errors in PL/SQL can be a spelling mistake or any syntax written in the wrong format. The PL/SQL compiler identifies syntax errors from the PL/SQL program.
13. Define constraining and mutating tables.
Answer: The general meaning of mutating is change. Therefore, a mutating table is a table that gets modified when we use insert, update, or delete statements, i.e., DML statements.
On the other hand, a table that we require to access and read for a referential integrity constraint is referred to as a constraining table.
14. Explain the way of writing comments in PL/SQL.
Answer: Comments in computer programming help improve the readability of the code. In addition, comments tell what actually the code is for and how it functions. Adding comments in a PL/SQL program does not affect its functionality. There are different methods for writing single-line and multi-line comments in PL/SQL.
- Single-line comments
We can write single-line comments using the ‘–’ symbol before the actual comment.
- Multi-line comments
To write multi-line comments in PL/SQL, we use the ‘/* */’ symbol. The syntax is:
/* comment lines */.
Below is an Instance of PL/SQL’s Single-line and Multi-line Comments:
DECLARE num NUMBER(5); --it is a local variable BEGIN num := &p_num; /*it is a host variable used only in the program body*/ END;
15. Define formal parameters and actual parameters.
Answer: Formal parameters are those that are declared in the procedure header and are called in the procedure body. To understand formal parameters, let us see one example.
CREATE OR REPLACE FUNCTION square_area (side NUMBER) RETURN NUMBER IS
Here, side NUMBER is a format parameter.
Parameters that are present in the procedure call or function call statement are actual parameters. Below is an example of an actual parameter:
BEGIN DBMS_OUTPUT.PUT_LINE(square_area (5)); END; /
16. Explain three different modes of a parameter.
Answer: Parameters in PL/SQL have three distinct modes, as explained below:
- IN Parameters:
The IN parameter enables us to pass values to the called procedure. We can initialize IN parameters to default values. Furthermore, we cannot assign any other values to IN parameters, as they are constant.
- OUT Parameters:
Unlike IN parameters, OUT parameters are not initialized. We cannot use OUT parameters in expressions. Parameters that return values to the caller are OUT parameters. It is indispensable to specify this type of parameter.
- IN OUT Parameters:
IN OUT parameters are the combination of IN parameters and OUT parameters. Parameters that pass values to the procedure and then return values to the caller are IN OUT parameters. However, we need to assign values to these parameters, and they function like initialized variables.
17. State and describe PL/SQL exceptions.
Answer: Any error taking place in the PL/SQL program causes it to terminate unexpectedly. Therefore, the PL/SQL program involves an error-handling code that handles errors occurring in the PL/SQL block. This error-handling code is present in the EXCEPTION section of the PL/SQL block.
Following are the three different kinds of PL/SQL exceptions:
- Undefined Exceptions: Very rare errors that occur with no names defined.
- Pre-defined Exceptions: Common errors taking place in a PL/SQL block with pre-defined names.
- User-defined Exceptions: Errors that violate business rules but do not result in runtime errors.
18. Explain predefined exceptions in PL/SQL.
Answer: Predefined exceptions involve rare errors that occur with no default names. Below are predefined exceptions in PL/SQL:
- NO_DATA_FOUND: The SELECT statement involves only a single row and returns no data.
- INVALID_CURSOR: This exception is raised when an illegal operation takes place.
- TOO_MANY_ROWS: The SELECT statement uses a single row and returns multiple rows as output.
- ZERO_DIVIDE: This exception is thrown when a PL/SQL code attempts to divide by a zero.
19. Define expressions. What are the different kinds of expressions in PL/SQL?
Answer: An expression in PL/SQL is a series of variables and literals. These literals and variables are separated using operators. Alternatively, an expression is a combination of operators and operands. In addition, we use operations in PL/SQL to compare, manipulate, and calculate data.
Operands in a PL/SQL expression can be a function call, constant, or variable, whereas operators are ‘+’ and ‘*.’
There are four kinds of expressions used in PL/SQL that are as follows:
- Boolean Expression: Ex. ‘code’ LIKE ‘co%e’.
- Date Expression: Ex. SYSDATE>TO_DATE(‘02-JUL-2022’, “dd-mm-yy”).
- Numeric Expression: Ex. 10*20+5.
- String Expression: Ex. LENGTH(‘CODE CHALLENGE’ || ‘CH’).
20. Explain PL/SQL records.
Answer: A record in PL/SQL is a kind of data structure used for holding data items. It is a group of several pieces of data or values. In addition, it involves various data fields analogous to a row in a database’s table. All values or pieces of data in a record are of the same types and related to each other.
For instance, consider that we need to keep a record of books available in a library, where each book has attributes, such as Author, ID, and Title. The best data structure in PL/SQL to store all these fields of books available in a library is known as a record.
In the PL/SQL block’s Declaration segment, we define records. Records in PL/SQL are of three types:
- Table-based Record: We use the table name with the %ROWTYPE attribute to define table-based records.
DECLARE record_name table_name%ROWTYPE;
DECLARE r_address address%ROWTYPE;
In the above example, we heated a record named r_address analogous to the address table.
- Cursor-based Record: We use an explicit cursor with the %ROWTYPE attribute to define a cursor-based record.
DECLARE Record_name cursor_name%ROWTYPE;
DECLARE CURSOR c_address IS SELECT house_no, street_name, city_name FROM address; r_address c_address%ROWTYPE;
In the above example, first, we have declared an explicit cursor c_address. This cursor retrieves data from the address table’s columns house_no, street_name, city_name.
After creating an explicit cursor, we declared a record named r_address, analogous to the c_address cursor.
- Programmer-defined Record: We use a programmer-defined record to create a record whose structure does not depend on the existing ones. First, we will define a record type containing the record’s structure. Later, we will declare that record using its record type.
Defining a record type:
TYPE record_type IS RECORD( field_name1 data_type1 [[NOT NULL] := | DEFAULT default_value]; field_name2 data_type2 [[NOT NULL] := | DEFAULT default_value]; ... );
Declaring a record depending upon the above record type:
DECLARE TYPE r_employee_contact_t IS RECORD ( employee_name employees.name%TYPE, first_name contacts.first_name%TYPE, last_name contacts.last_name%TYPE ); r_employee_contacts r_employee_contact_t; BEGIN NULL; END;
Procedural Language extensions to the Structured Query Language is one of the most potent and vigorous query languages used globally. It offers various benefits, such as high-level security, object-oriented programming support, access to predefined SQL packages, and support for building PL/SQL Server Pages and web applications.
Additionally, PL/SQL offers a broad spectrum of career opportunities. Though PL/SQL does not develop or enhance itself and is obsolete, there is no better option for Oracle than this. It is the core language used with the Oracle database. Therefore, it would be highly beneficial to pursue a career in PL/SQL development.
This post consists of PL/SQL interview questions and answers that are popularly asked in interviews. If you are a fresher and appearing for a PL/SQL interview, getting familiar with these questions and answers will help you ace your interview.
People are also reading:
- SQL Interview Questions and Answers
- What is NoSQL Database?
- Difference between SQL and MySQL
- SQL Commands
- SQL Cheat Sheet
- Top JQuery Interview Questions and Answers
- AWS Interview Questions and Answers
- Docker Interview Questions and Answers
- Top Linux Interview Questions and Answers
- SSIS Interview Questions & Answers