PL/SQL Interview Questions and Answers
Introduction : PL/SQL is an abbreviation for Procedural Language extensions to SQL (Structured Query Language). Oracle developed it to address the shortcomings of SQL in order to facilitate the development and management of important applications.
SQL has the following drawbacks:
- SQL does not provide decision-making, looping, or branching.
- Because the SQL statements are being sent to the Oracle engine at the same time, the pace of execution suffers as a result of the increased traffic.
- There is no error checking feature when manipulating data.
PL/SQL was created to address the aforementioned shortcomings by preserving the capability of SQL and combining it with procedural commands. It is designed as a block-structured language, and the statements of each block are passed to the Oracle engine, which helps to boost processing performance owing to less traffic.
SQL is a more advanced form of PL/SQL. There is a top list of PL/SQL interview questions and answers provided.
- What is PL/SQL?
The procedural language PL/SQL was created for the Oracle Database management system. It has a syntax similar to Ada and Pascal and is used to construct, maintain, and manipulate database objects such as procedures, functions, triggers, and packages. PL/SQL has a variety of characteristics that are unique to dealing with relational databases and SQL, such as the ability to embed SQL statements directly in code. Object-oriented programming and exception handling are also supported.
- What is the purpose of using PL/SQL?
PL/SQL (Procedural Language/Structured Query Language) is a programming language used in an Oracle Database to manage and manipulate data. It creates and manages database objects like procedures, functions, triggers, and packages. PL/SQL enables users to develop complicated database applications, conduct complex calculations, and change database data. It also supports error management, allowing developers to handle exceptions and errors in an organised manner. Overall, the goal of PL/SQL is to provide a strong and fast mechanism to interface with and manage data in an Oracle Database.
- What are the most important characteristics of PL/SQL?
The procedural language PL/SQL was created expressly for the Oracle Database management system. Its most important aspects are as follows:
PL/SQL code is arranged into blocks, which can be nested within other blocks. This allows for the organisation of code to be transparent and modular.
Strong typing: PL/SQL variables have defined data types, and compile-time type verification is enforced.
PL/SQL has a robust exception management framework that allows developers to handle and recover from problems.
SQL support is built-in: PL/SQL interacts perfectly with the SQL language, allowing developers to easily manipulate data contained in the database.
PL/SQL allows developers to construct stored procedures and functions that may be invoked from other areas of the application to execute specific tasks.
Object Oriented Programming is supported.
Triggers are supported.
Cursor has my support.
- What is PL/SQL table? Why it is used?
A PL/SQL table, also known as an associative array, is a data structure in the PL/SQL programming language that is used to store and manage data collections. It is comparable to a standard table in a relational database, except it exists in the PL/SQL program’s memory and can be changed with PL/SQL expressions rather than SQL instructions. PL/SQL tables are used within the PL/SQL programme to store and manipulate huge amounts of data, as well as to pass data between PL/SQL procedures and functions. They can also be used to create temporary data structures for usage within a PL/SQL block or programme.
- What are the datatypes available in PL/SQL?
There are various datatypes accessible in PL/SQL, including:
NUMBER: used to store numerical values.
VARCHAR2: a variable-length character string storage type.
DATE: used to store date and time values.
CHAR: used to store fixed-length character strings.
LONG: used to store long character strings.
BLOB: binary big object storage
CLOB: character large object storage
BOOLEAN: used to store Boolean values.
BINARY INTEGER and PLS INTEGER are used to store integers.
REF CURSOR: for storing query result sets
PL/SQL RECORD: used to store a row of data with a variable number of columns.
PL/SQL TABLE: A collection of rows with various numbers of columns stored in a table.
VARRAY: a variable-size array of data storage.
Nested Table: A collection of rows with various numbers of columns stored in a table.
- What is the basic structure of PL/SQL?
The procedural language PL/SQL was created expressly for the Oracle Database Management System. A PL/SQL block’s fundamental form includes a DECLARE section for declaring variables, an optional BEGIN section for executing procedural instructions, and an EXCEPTION section for error management. The following is the general syntax for a PL/SQL block:
DECLARE
— Declarations of variables, cursors, and other constructs
BEGIN
— Executable statements
EXCEPTION
— Exception handling statements
END;
It is also feasible to nest PL/SQL blocks within other blocks and incorporate transaction-control commands like COMMIT and ROLLBACK.
- What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?
A function in PL/SQL is a subprogram that returns a single value. It can be used in expressions and accessed by a SELECT statement or another PL/SQL block. A procedure is a subprogram that accomplishes something but does not return a value. It can be accessed by a PL/SQL block or through another operation. A package is a collection of linked procedures, functions, and variables organised into a single PL/SQL unit. It enables modularity and structure of related code, as well as the sharing of variables and operations across various PL/SQL blocks.
- What is exception? What are the types of exceptions?
An exception in PL/SQL is a runtime error that happens during the execution of a programme. In PL/SQL, exceptions are classified into two types: predefined and user-defined.
PL/SQL defines predefined exceptions, which have names like NO DATA FOUND or TOO MANY ROWS. When a specified situation happens, such as a SELECT INTO command returning no rows, PL/SQL raises these errors automatically.
User-defined exceptions are exceptions that the programmer creates and raises. They can be explicitly raised using the RAISE statement and managed with exception management methods such as the EXCEPTION block.
- How to write a single statement that concatenates the words ?Hello? and ?World? and assign it in a variable named Greeting?
Using the following command in PL/SQL, you can concatenate the words “Hello” and “World” and assign the result to a variable named “Greeting”:
Greeting: = ‘Hello’ ||’|| ‘World’
In PL/SQL, the || operator is used to concatenate strings.
- Does PL/SQL support CREATE command?
Yes, the CREATE command is supported by PL/SQL for creating various database objects such as tables, procedures, and functions.
- Write a unique difference between a function and a stored procedure.
In PL/SQL, a function returns a single value, but a stored procedure does not return a result but can deliver numerous outputs via the OUT or IN OUT parameters.
- How exception is different from error?
An exception in PL/SQL is a circumstance or occurrence that happens during programme execution that breaks the normal flow of instructions. The application can handle exceptions, allowing it to continue running after the exception has been resolved. An error, on the other hand, is a more serious problem that the software cannot manage and must be resolved by the creator or administrator.
Errors are often caused by issues such as a coding syntax error, a missing file, or a shortage of memory resources. The PL/SQL runtime system can raise them and the programme can catch them. The programme, on the other hand, can raise an exception using the RAISE command and catch it using exception handling constructs such as the EXCEPTION block.
To summarise, an exception is a runtime occurrence that the programme can handle, whereas an error is a more serious problem that requires the intervention of a developer or administrator to rectify.
- What is the main reason behind using an index?
The primary purpose of utilising an index in PL/SQL is to increase query performance by enabling the database management system to easily discover and get specified rows of data from a table. An index serves as a roadmap for the database, directing it down a specified path to discover the desired data rather than having to scan the entire table. This can significantly speed up data retrieval, especially for huge tables with millions of records. Indexes can also be used to enforce uniqueness requirements on the data in a table, which can help to assure data integrity.
- What are PL/SQL exceptions? Tell me any three.
PL/SQL exceptions are errors that occur when a PL/SQL programme is being executed. They are handled in a PL/SQL block with the “EXCEPTION” block.
The following are three common PL/SQL exceptions:
NO DATA FOUND – When a SELECT INTO command returns no rows, this exception is thrown.
TOO MANY ROWS – thrown when a SELECT INTO command returns more than one row.
VALUE ERROR – When an arithmetic, conversion, truncation, or size constraint error occurs, this exception is thrown.
- How do you declare a user-defined exception?
A user-defined exception can be declared in PL/SQL using the following syntax:
DECLARE
exception_name EXCEPTION;
BEGIN
— code that may raise the exception
RAISE exception_name;
EXCEPTION
WHEN exception_name THEN
— code to handle the exception
END;
You can also throw a user-defined exception with a string value that the user can see.
RAISE exception_name ‘Error message’;
When an exception occurs, the string ‘Error message’ is displayed.
- What are some predefined exceptions in PL/SQL?
PL/SQL includes various preset exceptions, such as:
NO _DATA _FOUND: This error is thrown when a SELECT INTO command returns no rows.
TOO _MANY _ROWS: This exception is thrown when a SELECT INTO command returns more than one row.
INVALID _CURSOR: This exception is thrown when an operation is performed on an invalid cursor.
DUP_VAL _ON _INDEX : When a unique constraint is breached, DUP VAL ON INDEX is raised.
INVALID_NUMBER: This exception is thrown when an invalid number is found.
VALUE _ERROR : When an arithmetic, conversion, truncation, or size constraint error occurs, VALUE ERROR is raised.
ZERO_DIVIDE: When attempting to divide by zero, ZERO DIVIDE is raised.
INVALID _ARGUMENT _TYPE: thrown when an invalid argument is supplied to a built-in function.
CURSOR_ALREADY_OPEN: When an attempt is made to open a cursor that is already open, this error is raised.
COLLECTION_IS_NULL: This error is thrown when a programme tries to use collection methods other than EXISTS on an uninitialized nested table or varray.
ROWTYPE_MISMATCH: This error is thrown when a programme attempts to assign values to a record variable of the wrong type.
PROGRAM_ERROR: thrown when a programme internal error occurs.
STORAGE_ERROR: thrown when there is a storage error.
- What is a trigger in PL/SQL?
A trigger in PL/SQL is a programme unit that is executed automatically in response to a specified event, such as the insertion or modification of data in a table. Triggers are commonly used to enforce business rules or to ensure data integrity by conducting actions like cascade updates or data validation. They can be written to operate on individual rows or groups of rows, and can be defined to execute before or after a specific event.
- What is the maximum number of triggers, you can apply on a single table?
The maximum number of triggers that can be applied to a single table in PL/SQL is 12. This comprises event triggers for the following:
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE
BEFORE INSERT OR UPDATE
AFTER INSERT OR UPDATE
BEFORE UPDATE OR DELETE
AFTER UPDATE OR DELETE
BEFORE INSERT OR UPDATE OR DELETE
AFTER INSERT OR UPDATE OR DELETE
Please keep in mind that this only applies to Oracle Database; other databases may have different restrictions.
- How many types of triggers exist in PL/SQL?
Triggers in PL/SQL are classified into three types:
DDL Triggers
DML Triggers
Database Event Triggers.
- What is the difference between execution of triggers and stored procedures?
A trigger in PL/SQL is a series of instructions that are executed automatically, or “triggered,” in response to a certain event or change in a database table. In contrast, a stored procedure is a pre-compiled collection of SQL statements that can be called by a programme or trigger.
The primary distinction between triggers and stored procedures is that triggers are executed automatically in response to a specified event, whereas stored procedures are executed manually by invoking them. Triggers are commonly used to enforce business rules or preserve data integrity, whereas stored procedures are used to conduct particular tasks like data retrieval or manipulation.
Triggers can be set to run before or after a data manipulation language (DML) statement like INSERT, UPDATE, or DELETE, whereas stored procedures can be called by a programme or another stored procedure.
In summary, triggers are used to enforce business rules and ensure data integrity, whereas stored procedures are used to perform specified actions.
- What happens when a trigger is associated to a view?
A trigger in PL/SQL is a block of code that is executed automatically in response to particular events on a specified table or view. When a trigger is linked to a view, the trigger code is executed anytime an INSERT, UPDATE, or DELETE operation is performed on the data in the view. The information in the view can be used by the trigger to conduct additional actions, such as enforcing business rules or updating linked tables. Triggers can thus be used to maintain data integrity and consistency.
- What is the usage of WHEN clause in trigger?
The WHEN clause of a trigger in PL/SQL specifies the conditions under which the trigger should be activated. In the CREATE TRIGGER statement, the WHEN clause is followed by a Boolean expression. Only if the Boolean statement evaluates to true will the trigger fire. A trigger, for example, could be constructed to only update a record when a specified field in the record is modified to a certain value. The criterion for that field to have that precise value would be specified in the WHEN clause.
- How to disable a trigger name update_salary?
In PL/SQL, run the following command to disable the “update salary” trigger:
ALTER TRIGGER update_salary DISABLE;
This command will set the trigger’s state to “DISABLED,” and it will not execute when the triggering event occurs.
By querying the user_triggers view, you can confirm that the trigger is disabled:
SELECT status FROM user_triggers WHERE trigger_name = ‘update_salary’;
To reactivate the trigger, use the command:
ALTER TRIGGER update_salary ENABLE;
- Which command is used to delete a trigger?
In PL/SQL, the command to delete a trigger is “DROP TRIGGER.”
- what are the two virtual tables available at the time of database trigger execution?
The two virtual tables available in PL/SQL during database trigger execution are:
OLD – This virtual table stores the previous values of the rows affected by the triggering event (e.g. before update)
NEW – The new values of the rows affected by the triggering event are stored in this virtual table (e.g. after update)
- What is stored Procedure?
A stored procedure is a pre-compiled programme in PL/SQL that can be executed. It is made up of a collection of SQL statements that are saved in the database and can be called and executed by a user or an application. Stored procedures can receive input parameters and return output parameters, making them ideal for encapsulating complex logic and business rules while also lowering the amount of SQL code required in a programme. They can also boost performance by decreasing the number of round trips between the application and the database.
- What are the different schemas objects that can be created using PL/SQL?
The Oracle procedural language extension to SQL, PL/SQL, allows you to create a variety of schema objects, including:
Procedures : Theses are PL/SQL blocks that accept parameters and return one or more values.
Functions : Theses are PL/SQL blocks that take input arguments and return a single value.
Packages : Theses are PL/SQL collections of procedures, functions, and variables that are given names.
Triggers : These are PL/SQL blocks that are named and performed automatically in response to particular database events.
Types : It is a named PL/SQL object that enables the definition of user-defined data types like records and collections.
Views : These are virtual tables created depending on SELECT query results.
Sequence : A database object that creates distinct integers is called a sequence.
synonyms: A database object that gives another database object a different name.
There are more schema objects that may be constructed using PL/SQL, but these are the ones that are most frequently used.
- What do you know by PL/SQL Cursors?
Rows returned by a SELECT statement are processed by PL/SQL cursors. Instead of collecting all the rows at once and processing them, they offer a mechanism to retrieve and manipulate data row by row. The information about the SELECT statement is saved in a private SQL region with a handle or name called a cursor. The use of a cursor allows you to retrieve one row from the result set at a time. Rows produced by a SELECT command are processed using cursors. One at a time, they can be used to obtain, update, and remove records. PL/SQL offers both explicit and implicit cursors, among other different forms of cursors.
- What is the difference between the implicit and explicit cursors?
For several kinds of SQL statements, like SELECT INTO, INSERT, UPDATE, and DELETE, the PL/SQL engine automatically creates and manages an implicit cursor. Since the cursor has no name, the programmer cannot control it directly.
The DECLARE, OPEN, FETCH, and CLOSE statements are used by the programmer to establish and manage an explicit cursor, on the other hand. It is possible to have additional control over the result set by naming and directly manipulating an explicit cursor, which is accessible to the programmer. Additionally, cursor properties like %FOUND, %NOTFOUND, and %ROWCOUNT can be used with explicit cursors.
- What will you get by the cursor attribute SQL%ROWCOUNT?
The cursor attribute SQL%ROWCOUNT in PL/SQL delivers the total number of rows impacted by the last DML statement (such as an INSERT, UPDATE, or DELETE) used by the cursor. It can be used to count the number of rows impacted by a DML statement in a PL/SQL block, which can be helpful for debugging or assessing whether a DML transaction was successful.
- What will you get by the cursor attribute SQL%FOUND?
A Boolean value representing the success of the most recent cursor operation (OPEN, FETCH, or CLOSE) is returned by the cursor attribute SQL%FOUND in PL/SQL. If the operation was successful and a row was located, it returns TRUE; otherwise, it returns FALSE. If the operation was unsuccessful or no rows were located, it returns TRUE.
- What will you get by the cursor attribute SQL%NOTFOUND?
If the most recent cursor operation (like FETCH) was successful or not, a Boolean value will be returned by the cursor attribute SQL%NOTFOUND in PL/SQL. If the most recent cursor operation failed to return any rows, it will return FALSE; otherwise, it will return TRUE. This characteristic can be used to check the progress of a cursor operation and, based on the outcome, to perform the required action.
- What do you understand by PL/SQL packages?
PL/SQL packages are a mechanism to collect related PL/SQL objects, like functions and procedures, in one place. A package body and a package specification make up a package. The names, parameters, and return types of the procedures and functions that make up the package are all defined in the package specification, along with the package’s public interface. Along with any private variables and data structures utilised by the package, the implementation of those procedures and functions is contained in the package body. Additionally, packages can be used to encapsulate data, restrict access to it, and arrange associated functionality in a logical and user-friendly manner.
- What are the two different parts of the PL/SQL packages?
The package specification and the package body are the two components that make up PL/SQL packages.
The public interface of the package is specified in the package specification, commonly referred to as the package header. It lists the types, variables, constants, and subprograms that other PL/SQL blocks can see and access.
The implementation information for the variables and subprograms defined in the package specification is contained in the package body. Additionally, it defines additional variables and subprograms that are exclusively used within the package.
- Which command is used to delete a package?
The “DROP PACKAGE” command is used in PL/SQL to delete a package. It is used to delete an existing package and all of its components from the database. The command has the following syntax:
DROP PACKAGE package_name;
To delete a package named “employee_package,” for example, use the command:
DROP PACKAGE employee_package;
Please keep in mind that this command will fully destroy the package, so use with caution.
- How to execute a stored procedure?
You can run a stored procedure in PL/SQL by using the following syntax:
EXECUTE procedure_name([parameter1, parameter2, …]);
For instance, if you had a stored procedure called “calculate_ bonus” that takes two parameters, an employee ID and a bonus amount, you would run it as follows:
EXECUTE calculate_bonus(123, 500);
To execute a process, use the “BEGIN” keyword before its name.
BEGIN
procedure_name([parameter1, parameter2, …]);
END;
To execute the stored procedure, you can also use the “CALL” keyword.
CALL procedure_name([parameter1, parameter2, …]);
It is also possible to assign the saved procedure’s output to a variable using the following syntax:
variable_name := procedure_name([parameter1, parameter2, …]);
Before attempting to execute the process, ensure that it has been created in the database.
- What are the advantages of stored procedure?
PL/SQL stored procedures offer various advantages, including:
Increased performance: Because stored procedures are pre-compiled, they execute faster than dynamic SQL statements.
Improved security: Stored procedures can be assigned specific privileges, allowing for greater fine-grained control over who can access and execute them.
Reusability: Stored procedures can be called and reused repeatedly, minimising the requirement to rewrite the same code.
Modularity: Stored procedures can be grouped and organised into logical units of work, making them easier to maintain and alter.
Reduced network traffic: By running a stored procedure on the database server, you can limit the amount of data that needs to be transferred over the network, which increases speed.
Improved Concurrency: Stored Procedures are executed in the database server and do not block the database connection, allowing other processes to occur concurrently.
- What are the cursor attributes used in PL/SQL?
Cursor attributes are used in PL/SQL to retrieve information about a cursor’s current state. The following are some common cursor attributes:
%ISOPEN: returns TRUE if the cursor is open, FALSE if it is closed. %ROWCOUNT: returns the number of rows fetched or affected by the most recent SQL command.
%NOTFOUND: returns TRUE if the most recent SQL command returned no rows, and FALSE otherwise.
%BULK _ROWCOUNT: returns the number of rows that have been affected by the most recent bulk operation (when using FORALL)
These attributes can be used after the cursor name in a SELECT statement.
Eg.
IF cursor_name%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE(‘Cursor is open’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Cursor is closed’);
END IF;
- What is the difference between syntax error and runtime error?
A syntax error in PL/SQL happens when the code written does not follow the language’s rules and structure. This type of mistake will prohibit the code from compiling and running. A runtime error happens when the code is syntactically accurate but there is a problem with the logic or data that causes the programme to fail during execution. A PL/SQL statement that refers a table that does not exist, for example, would result in a runtime error since the statement is syntactically correct but cannot be executed because the table does not exist.
- Explain the Commit statement.
The COMMIT command in PL/SQL is used to terminate a transaction and make all changes made during the transaction permanent. It commits all pending database modifications and releases any database locks gained during the transaction. Any modifications made after a COMMIT statement will be recorded in a new, distinct transaction. The ROLLBACK statement, which is used to undo all changes made during a transaction, is the inverse of the COMMIT command.
- Explain the Rollback statement?
The ROLLBACK statement in PL/SQL is used to undo the changes made by the current transaction. It undoes all changes made by the current transaction, returning the database to its original state before the transaction began. If an issue occurs, the rollback statement is often used in error handling methods to undo any changes done during the transaction. It can also be used to roll back changes made during testing before committing them to the database.
- Explain the SAVEPOINT statement.
The SAVEPOINT command in PL/SQL creates a designated point in a transaction from which you can later roll back. It enables you to undo portions of a transaction without having to roll back the full transaction. For example, suppose you have a complex PL/SQL block that executes many updates and inserts to different tables, but you want to be able to undo only one of the modifications without effecting the others. Set a SAVEPOINT before the update and then roll back to that SAVEPOINT if the update fails or you need to undo it for another reason.
- What is mutating table error?
A “mutating table error” occurs in PL/SQL when a trigger attempts to modify a table that it is currently reading. This can result in unforeseen outcomes and is typically regarded bad practise. To avoid this mistake, the trigger should be built to read a duplicate of the data or to utilise a temporary table to store the necessary modifications.
- What is consistency?
In PL/SQL, consistency refers to the state of a database in which all data complies to the database’s integrity constraints. This ensures that the data in the database is consistent and accurate across all tables and rows. It is one of the ACID (Atomicity, Consistency, Isolation, Durability) model attributes used to secure a database’s reliability and integrity. Constraints like as primary keys, foreign keys, and check constraints are used to maintain consistency.
- What is cursor and why it is required?
A cursor is a handle or a name for a specific area in memory that is used to run a SQL statement and retrieve the results in PL/SQL. Cursors are essential in PL/SQL because they enable for the retrieval and manipulation of specific rows from a result set. They also allow several rows to be processed as a single block, which can increase the performance of a PL/SQL programme. Cursors also allow the use of PL/SQL structures like loops and conditional statements to process the data returned by a SQL statement.
- How many types of cursors are available in PL/SQL?
In PL/SQL, there are three sorts of cursors: implicit, explicit, and dynamic.
In conclusion, this comprehensive guide on PL/SQL interview questions and answers provides a valuable resource for individuals preparing for PL/SQL interviews. By exploring the various topics covered, including PL/SQL blocks, control structures, cursors, exceptions, and more, job seekers and interviewers can gain a deeper understanding of PL/SQL and its capabilities. This guide equips individuals with the knowledge and confidence needed to succeed in PL/SQL interviews, showcasing their expertise in Oracle database programming. Stay up-to-date with the latest PL/SQL practices, enhance your database programming skills, and confidently tackle PL/SQL-related questions with the help of this comprehensive resource.