Loading…

PL SQL Interview Questions and Answers

By Umesh Singh
Last update: 08 Jun 2020, 20 Questions

PL/SQL stands for Procedural Language/ Structured Query Language. It is basically an extension of SQL with operational features of the programming language. It runs within the Oracle executable and inherits all the security features, portability, and swiftness of Oracle Database. Below is a list of the most common PL SQL interview questions for you to have a read.

 

PL/SQL is used to integrate data-manipulation of SQL with the processing abilities of procedural languages. PL/SQL has many benefits like having better performance and productivity, access to predefined packages, support for Object-Oriented Programming and Developing Web Pages and Applications easily.

Features of PL/SQL:
  • PL/SQL is tightly integrated with SQL.
  • It offers extensive error checking.
  • It contains multiple data types.
  • It offers and supports a wide variety of programming structures.
  • Enabled support for structured programming through functions and procedures.
  • Supports and based on object-oriented programming.
  • It supports the development of web applications and server pages.

Most Frequently Asked PL SQL Interview Questions

1. What is the difference between SQL and PL SQL?
SQL PL/SQL
Stands for Structured Query Language. Stands for Procedural Language SQL, an extension for SQL.
Only one single query can be executed at a time. Multiple codes can be executed at a single time.
It is used to store data to be displayed It's the language through which an application is created to display data
PL SQL statements cannot be embedded in the code SQL statements can be embedded in the code
Used to write queries and commands using DDL and DML methods Used to write a block of code for having procedures, variables, functions and packages
2. What is delimiter in PL SQL?

Delimiter in PL/SQL is a simple/compound symbol. In PL/SQL, delimiters are used to represent arithmetic operations like addition and subtraction.

3. Why do we use Rowtype in PL SQL?

In PL/SQL, Rowtype is used to provide a record type/data type in the form of rows in a database. Variables declared using the %ROWTYPE attribute are considered to be declared using a data type name.

4. How many types of triggers are there in PL SQL?

In PL/SQL, there are two types of triggers:

  • Row level triggers: Used to trigger an event once any changes are made in rows.
  • Statement level trigger: It is used to trigger an event for each executed SQL statement.
5. Why packages are used in PL SQL?

In PL/SQL modules, packages are used to encapsulate logically related data types, constants, variables, subprograms, cursors, and any other exceptions. Each package can be used to simplify the level of understanding inside an interface using simple, clear and well-defined functions within.

6. What is difference between commit and rollback?
COMMIT ROLLBACK
Syntax: COMMIT; Syntax: ROLLBACK;
Used to validate modifications made by current transactions Used to erase modifications made by current transactions
After executing this, a transaction can’t be used for ROLLBACK After executing this, a transaction can be modified and sent for COMMIT
Occurs after successful completion of a transaction Occurs if transaction is abrupted unexpectedly
7. What is Savepoint in PL SQL?

In, PL/SQL, a Savepoint is a TCL(Transition Control Language), statement is used to create a breakpoint during a specific/current transaction at a particular location. A Savepoint is basically a pit stop for transactions and ROLLBACK can be used to erase any part of a transaction.

8. What is attribute indicator in PL SQL?

Attributes in PL/SQL are properties which let the user reference the data type and structure of a present item without having to repeat its definition. Attributes are mainly used for easy maintenance of database tables.

9. What is the difference between Case and decode?
CASE DECODE
Is a statement Is a function
Can be used in the WHERE clause Cannot be used in the WHERE clause
10. What is PGA and UGA?

Server processes assigned in a client program to be used as a memory for private data are known as Process Global Area. UGA or User Global Area is used to retain data during a session database such as Package Variables and Private SQL areas.

11. What is function overloading in PL SQL?

The Function overloading in PL/SQL is based on the concept of polymorphism. It actually refers to the capability of any programming method to perform multiple operations based on the input data type and the context in which the process, i.e., function, is used.

12. What are the difference between syntax and runtime errors in pl/sql?
S.no Run-Time Error Syntax Error
1. This is a type of error that is encountered while the program is running. This is an error encountered in the syntax of a sequence of characters or the tokens intended to be written in a particular language.
2. It happens due to the occurrence of an illegal operation. It happens due to the occurrence of grammar rules with respect to the programming language.
3. This type of error is detected while the program is still running. This type of error is detected while compiling the program.
4. E.g., Array out of bound, Diving by zero, etc. E.g., Missing curly braces, semicolons, etc.
13. What is the difference between constraints and triggers?
S.no Constraints Triggers
1. This is defined by the relationship between data elements. These are the actions that are executed when a specific reaction occurs.
2. It is used for a column. It is used for a table.
3. These are useful for database consistency. These are used for logging and auditing.
4. It precedes triggers when getting fired. First constraints get fired. Only then do triggers get fired.
14. What are parameter modes in PL SQL functions and procedures?

In PL/SQL, procedures support all the parameter modes listed below, whereas, functions only support the IN parameter mode. Here are all the parameter modes in PL/SQL for procedures and functions:

  • IN: This is the default mode, which allows the calling code to pass values in the procedures or functions.
  • OUT: The OUT parameters allow the procedures or functions to pass the values back towards the calling code.
  • IN OUT: The IN OUT parameters allow the calling code to pass the values in and also to receive values from procedures or functions.
15. How are multi line comments defined in PL SQL?

To define a multi-line comment style in PLSQL, there are two steps.

  • A double hyphen (- -) is placed anywhere on a line (except on a character literal), which shall turn it into a comment.
  • To make a comment as a multi-line, it must begin with a slash-asterisk (/*) and then end with an asterisk-slash (*/).
16. What is scalar data type in PL SQL?

A scalar data type in PL/SQL is one in which there are single values with no internal components such as DATE, BOOLEAN, or NUMBER.

17. Why do we need cursor in PL SQL?

In PL/SQL a cursor is needed for the following reasons:

  • To retrieve data, a single row at a time, from a data set, unlike in the SQL commands where all the rows are operated on the result set at one time.
  • Cursors are also used for updating records in a singleton fashion or a row by row manner into a database table.
18. What are the differences between stored procedure and functions?
S.no Stored Procedure Function
1. It supports all the parameters of PL/SQL, namely, IN, OUT, and IN-OUT. It supports only input parameters, not the output parameters.
2. They can call functions as and when required. A function can not call a stored procedure.
3. There is no possibility of calling stored procedures from select, having, and where statements. A function can be called using the select statement.
4. Here, transactions can be used. No transactions can be used here.
5. It can undergo exception handling by the insertion of try/catch blocks. There are no provisions for a function to explicitly undergo exception handling.
6. It may or may not return with a value. It has to return with either a result or a value to the caller.
7. All the database operations such as Insert, Delete, Update, etc. can be performed with stored procedures. Only the Select Database operation can be carried out with functions.
8. Stored procedures can fire triggers. Functions can not fire triggers.
9. It can use print commands. A function can not use print commands.
10. Stored Procedures can execute dynamic SQL commands. Functions can not execute dynamic SQL commands.
19. Why indexing is used in PL SQL?

Nowadays, Databases are getting colossal, and there is a large amount of data to go through while finding anything in particular when required. Indexing in PL/SQL helps to speed up the process of finding a specific piece of data by returning a small portion of table rows in order to make the process of accessing data accurate and faster.

20. What are exceptions in PL SQL?

In PL/SQL, an exception is technically an error condition during the execution of a program. There are two types of limitations in PL/SQL, namely:

  • System defined exceptions
  • User-defined exceptions