25+ Must Know PL SQL Interview Questions

20149 Views

SaveSavedRemoved 30

Though there are many technologies that have come up over the years, PL SQL is one core skill that is required in many job roles. PL/SQL stands for “Procedural Language extensions to the Structured Query Language” and is widely used as database programming.

If you are new and also looking for a tutorial do check this oracle doc on Pl SQL.

In this post, I am listing down some of the most asked PL SQL Interview Questions. Every developer working on PL SQL should be able to answer these, Else there are chances of rejection.

So let’s start.

Table of Contents

Most Asked PL SQL Interview Questions

What is the difference between SQL and PL SQL?

SQL is a structured query language used to query data from the database, While PL SQL is procedural SQL used to write programs.

What is an anonymous block?

When you write a pl/sql script without storing in DB its anonymous (no name) . To give a name the anonymous block can be converted to a database procedure or a function.

What is Pragma?

Pragma is use to handle exception and give a custom Name to the Exception.

What is a NO COPY Parameter?

It is the same concept as we have in other languages like C / Pascal. The Paas by Value and pass by reference concept. When NOCOPY is used PL/SQL compiler passes OUT and IN OUT parameters by reference, instead of by value. This saves memory.

What is a %TYPE attribute?

%TYPE attribute is used to copy the datatype of an object(inherits) to define a new object. e.g
name emp.name%TYPE will create a name variable of the same type as the emp table name column.

What is the difference between CHAR & VARCHAR?

CHAR is for exact fixed length variable, Varchar is for variable length. The memory management is better with varchar.

How can you use a database function or procedure in a SQL statement?

You can use a Function in SQL to return a value as a part of the select statement. The procedure will not return as a part of the select statement.

What is the difference between a function and a procedure? How can you return a value using the procedure?

The function must return a value, the procedure may or may not. Using the OUT parameter you can return a value using a procedure.

How to use Commit inside a procedure without committing the calling transaction?

by Using PRAGMA AUTONOMOUS_TRANSACTION

What is an implicit cursor and an explicit cursor?

Implicit cursors are cursors automatically created to fetch records like in For loops. The explicit cursor needs explicit defining of the cursor, opening and closing the cursor.

What is a mutating table?

Access a table that is in a state of change (via an INSERT, UPDATE, or DELETE statement) gives a mutating error.
This is generally during row-level trigger firing during the event. To avoid mutating don’t use trigger.

What are cursor attributes?

Cursor attribute %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT are the implicit attributes of a cursor that can be used to check record status and attributes.

What is a Ref Cursor?

Ref Cursor is a pointer to a cursor. It acts as a dynamic cursor and can be used to fetch different data sets at runtime.

When do you use WHERE CURRENT OF?

When using an explicit cursor you can issue updates where the cursor is at that time ( where current of). The updates happen to that record/field.

How many types of triggers can be written on a table?

There are 12 types of triggers in PL/SQL that contains the combination of
BEFORE, AFTER, ROW Level, TABLE Level, INSERT, UPDATE, DELETE, ALL, and Instead of keywords.

Structure:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
[ON table_name]
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
WHEN (condition)

What is the difference between Commit, Rollback, and Savepoint?

commits save the transaction permanently in the Database, Rollback reverts if it’s not yet committed and Savepoint is used to roll back to a certain point(identified by savepoints).

Name some Seeded Exceptions.

e.g
NO_DATA_FOUND
CURSOR_ALREADY_OPEN
TOO_MANY_ROWS
DUP_VAL_ON_INDEX
ZERO_DIVIDE

What are DML, DDL, and DCL?

DML- Data manipulation language ( insert update delete)
DDL – Data Definition Language (Create table, create view, etc)
DCL- Data control language ( eg. Grant Revoke)

How to Enable Disable a Trigger?

ALTER trigger (trigger name) ENABLE / DISABLE;

What is an invalid object?

Objects like Packages and procedures become invalid after there is some compilation error found. The Pl SQL compiler invalidates the objects. Any program already using it will throw an error.

Explain Sequential control statements.

Statements like GOTO, NULL are sequential statements used to transfer the control during the flow. GOTO transfers to a certain labeled section, Null transfers to the next executable section.

You are getting PL/SQL: numeric or value error: character string buffer too small. How to fix it?

ORA-06502: error Numeric or Value error is when we are trying to assign the wrong data type to a variable.

How do you debug PL SQL programs?

Though we have error messages from PL SQL Engine we can also create custom error tables and store those errors to track the flow of the programs.

What is Bulk Collect? what is the syntax to use?

Bulk Collect is used to fetch data in bulk and process . It collects many rows at once and places them in a collection.
Fetch c1 bulk collect into ArrayVariable

These were some of the most asked PL SQL Interview questions which every developer should know. Hope it helps.

If you work on BIP Reports check out BIP interview questions

For more information on PL SQL visit here