PL SQL Interview Questions and Answers

Last updated on Jan 04, 2023
  • Share
PL/SQL Interview 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

Here in this article, we will be listing frequently asked PL SQL Interview Questions and Answers with the belief that they will be helpful for you to gain higher marks. Also, to let you know that this article has been written under the guidance of industry professionals and covered all the current competencies.

Q1. What is the difference between SQL and PL SQL?
Answer
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
Q2. What is delimiter in PL SQL?
Answer

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

Q3. Why do we use Rowtype in PL SQL?
Answer

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.

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

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.
Q5. Why packages are used in PL SQL?
Answer

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.

Q6. What is difference between commit and rollback?
Answer
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
Q7. What is Savepoint in PL SQL?
Answer

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.

Q8. What is attribute indicator in PL SQL?
Answer

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.

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

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.

Reviewed and verified by Baliram Prasad
Baliram Prasad

Baliram Prasad is a Technical Lead in Coldfusion. I am working closely with B2C and B2B Clients. I am having 13+ years of experience. I love programming and most of my time goes in learning the best w...