PL SQL Interview Questions and Answers
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.
- 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
| 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.
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.
| 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. |
| 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. |
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.
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 (*/).
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.
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.
| 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. |
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.
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