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
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