Loading…

PL/SQL Interview Questions

Last update: 01 Feb 2020, 20 QuestionsAsk Question
A Quick Overview of PL/SQL

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.

Best PL SQL Interview Questions And Answers

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

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

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.

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.

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.

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

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.

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.

CASE DECODE
Is a statement Is a function
Can be used in the WHERE clause Cannot be used in the WHERE clause

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.

Add Review