PL/SQL Interview Questions

What is PL SQL?

PL SQL is an extension of the SQL language and the Oracle database. It stands for Procedural Language Extension for SQL and allows the user to write procedural code. It can be considered a combination of the processing ability of procedural languages and the data manipulation abilities of SQL.

What are some important characteristics of PL/SQL?

Some important characteristics of PL/SQL are:

It has a built-in programming environment that is independent of any operating system.
It is a transactional processing language, which is highly portable.
It has architecture consisting of PL/SQL block, Database server and PL/SQL Engine.
It supports object-oriented programming concepts.
Its basic syntax is based on Pascal and ADA.

What is cursor in PL SQL?

A cursor in PL/SQL is used for controlling a context area. When an SQL statement is executed, a memory area is created called context area. A cursor is a pointer to this area. This cursor holds the different rows which are returned by an SQL statement execution. There are two types of cursors namely, implicit and explicit.

What is a trigger in PL SQL?

Triggers can be considered as programs that are executed whenever a particular event occurs.

They are invoked when any one of the following events occur:

A DML (Data Manipulation Language) statement like Insert, Update or Delete is encountered.
A DD (Data Definition) statement like Alter, Create, or Drop is executed.
Data operation statements like STARTUP, SHUTDOWN OR LOGON is encountered.

How to compare dates in PL SQL?

Dates in PL SQL can be compared using the to_date function. The following code can be used:

Select student_id
 From Students
 Where student_date_enrolled > to_date('19-NOV-94','DD-MON-YY')

What is sys_refcursor in Oracle PL SQL?

The sys_refcursor in Oracle PL SQL is a weak ref cursor that is built in the language. This can be used for mentioning the cursor variable’s data type that is built inside the data type of the SYS_REFCURSOR.

What is an autonomous transaction in Oracle PL SQL?

All the alterations done on the data are called transactions. The autonomous transaction provides the functionality of performing a separate transaction and to save or delete it. This transaction will not affect the main transaction session. It has no relation or link to the original transaction. They are specified at the subprogram level.

How to create a procedure in PL SQL?

A procedure in Pl SQL is used to perform multiple tasks. A Create or Replace Procedure statement is used for developing a procedure.

The following code can be used:

CREATE PROCEDURE procedure
[(parameter[IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  < body_of_procedure >
END procedure;

What is the difference between SQL and PL SQL?

The differences between SQL and Pl SQL are as follows:

SQL	PL/SQL
A single query can be executed at a time through DDL and DML operations.	A block of code can be executed at a time that can be procedures, functions and statements.
It interacts with a Database server.	It does not interact with a Database server.
It is a declarative query language that defines instructions on actions that have to be performed on the data.	It is a procedural language that defines logic about how the actions need to be performed.
There is no support for control structures like for and while loops.	The control structures are supported.
It is data oriented.	It is application oriented.

What is an autonomous transaction in PL SQL?

The different changes made on the data are called transactions. The autonomous transaction provides the facility of performing a transaction that is separate from the original session, and delete/save this. This transaction will not affect the main transaction session. It has no relation or link to the original transaction. They are specified at the subprogram level.

How to use utl_file in PL SQL?

The UTL_FILE package can be used by the PL/SQL language to perform read and write operations on the text files of the corresponding operating system. The package is available for both the server side and the client side.

How do you remove duplicates in PL/SQL?

Duplicate records can be removed from PL/SQL by the following steps:

By the self join command.
By row_number() method.
Through the Group By clause.
By the dense_rank().

What is the difference between a function and procedure in a PL/SQL?

The differences between function and a stored procedure are:

Function	Stored procedure
It must return a value.	It may not return a value.
It only has input parameters.	It can have input and output parameter
It can be called from a stored procedure.	It cannot be called from a function.
It does not support the try/catch block.	Supports exception handling through try and catch methods.

What is a cursor in PL/SQL?

A cursor in PL/SQL is used for controlling a context area. When an SQL statement is executed, a memory area is created called context area. A cursor is a pointer to this area. This cursor holds the different rows which are returned by an SQL statement execution.

What are the different types of cursors in Oracle PL/SQL?

The different types of cursors in Oracle PL/SQL are implicit and explicit cursors.

How to use delete command in PL/SQL?

The Delete statement is used in PL SQL to delete unwanted records from the table.

The following syntax can be used:

DELETE FROM table_name

WHERE conditions

Here, table_name is the table from where you want to delete the records. The where condition specifies the condition based upon which the records will be deleted. If this condition is not specified, the entire table will be deleted.

What is Rowtype in Pl/SQL?

The %ROWTYPE attribute is used for specifying a record that has certain fields that correspond to all the columns that are obtained from a cursor variable. It is prefixed by a variable or a cursor name. It also lets the user declare a record representing a view or a table.

What is the difference between anonymous PL SQL block and stored procedure?

The differences between anonymous PL SQL block and stored procedure are as follows:

Anonymous PL/SQL block	Stored procedure
It is an unnamed PL/SQL block.	It is a named block.
They cannot be called.	They can be invoked as per requirements.
These blocks cannot be saved in a database.	They cannot be saved in a database.
They start with the keywords BEGIN or DECLARE.	They start with the Header block.
As they lack a reference name, they cannot be stored for future use.	They have a reference name and are usually stored to be used in the future.

PL/SQL Interview Questions and Answers