The blog provides the commonly asked PL/SQL Interview questions and answers for the interview preparation. PL/SQL is mainly used by the Oracle Database Developer for writing Database specific SQL programs including Stored Procedures, functions, etc.
The blog contains the basic as well as advanced questions on PL/SQL which could be helpful for technical interviews for both freshers as well as experienced professionals.
What is PL/SQL ?
PL Stands for Procedural language .
SQL stands for Structured Query language .
PL/SQL is a Procedural language which acts as an extension to SQL.
PL /SQL allows SQL functions, conditional branching, statements and iteration also.
PL /SQL supports to execute multiple statements using Stored Procedure
PL /SQL also support writing customer error handling
PL/SQL also support the usage of functions, cursors and triggers
What is the latest PL/SQL Version?
PL/SQL latest version is 11.0 which comes as part of Oracle 11g Database. It supports all existing features like functions, stored procedures, user-defined record types, tables, packages and extensions as well as advanced features like native compilation, PL/SQL function result cache, and minute dependency tracking
What are the features of PL/SQL ?
PL/SQL is block structured language
PL /SQL programs can be deployed with ease to any environment where Oracle
Database is supported.
PL/SQL is integrated with Oracle Data Dictionary
PL /SQL can be embedded with Cursors, triggers, functions and stored procedures to develop complex database programming logic
What is the difference between SQL and PL/SQL ?
SQL is a Query Language used to interact with Database whereas PL/SQL is a Procedural language which acts as an extension to SQL
SQL is primarily used to perform table operation like create, insert, update and delete where as PL /SQL being a procedural language supports conditional branching , loops and statements, functions , cursors and usage of stored procedure for writing complex programming logic
SQL is single command statements whereas PL /SQL is a block of code executing multiple statements at once
SQL does not support error handling whereas PL /SQL code blocks can be supported with custom error handling
SQL statements can be used in PL /SQL whereas PL /SQL cannot be embedded in SQL
What is the difference between % ROWTYPE and TYPE RECORD
%ROWTYPE returns an entire row of a table or view when the query is executed.
TYPE RECORD returns column of different tables or views when the query is executed.
What data types are supported in PL/SQL ?
PL /SQL supports the below given data types:
Scalar Data Types: includes NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN
Composite Data Types: includes RECORD, TABLE
What are PL/SQL Exceptions ?
PL/SQL Exception is the error condition or warning raised when the condition fails are run time. Exception blocks are written to capture these error condition and handle using custom defined exceptions
What are the types of PL/SQL Exceptions ?
PL/SQL Exceptions can be classified into below 2 categories
System-defined Exceptions
User-defined Exceptions
Name 5 system defined PL/SQL Exceptions ?
The below given are the few PL/SQL Exceptions
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
TOO_MANY_ROWS
ZERO_DIVIDE
Explain User defined PL/SQL Exceptions ?
The custom exceptions are known as user defined exceptions
The user defined exceptions must be declared and then raise explicitly ( using RAISE Statement or Stored Procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR
Syntax for user defined exceptions
<exception_name> EXCEPTION;
What all components are part of PL/SQL ?
The PL/SQL may consist of below given components
Variables and constants
Table and record TYPE statements
Stored Procedures
Functions
Cursors
PL/SQL Exceptions
What are the possible options to trace the PL/SQL code ?
The below given options / techniques can be used for PL/SQL code tracing
DBMS_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION and DBMS_MONITOR
TRCSSES and TKPROOF utilities
Explain PL/SQL hierarchical profiler and its benefits ?
The PL/SQL hierarchical profiler helps in capturing and analyzing hierarchical profiler
data for PL/SQL programs.
The PL/SQL hierarchical profiler consists of the DBMS_HPROF package(similar to
DBMS_PROFILER and DBMS_TRACE packages)
The PL/SQL hierarchical profiler also contains the plshprof command line utility to
converts the profile information into HTML format
Benefits :
Provides SQL and PL/SQL reporting for time consumption
Provides sub-programs calls count in the PL/SQL
Provides the time spent with each subprogram call
Provides Multiple interactive analytics reports in HTML format
Explain the main difference between function, stored procedure and a package ?
Function is quite useful when we need to compute the values at run-time . A function returns the value and its return type is defined in the function specification
Stored Procedure is useful when multiple statements are to be executed, conditional branching , loop statements are required to be used. It does not return any value.
Package is a scheme object which helps in grouping the PL/SQL components like Stored procedure, functions, triggers, cursors ,etc to get it executed as a single program.
What all parts PL/SQL consists of ?
PL/SQL packages have the following two parts:
Specification part: specifies the interface for the PL/SQL program
Body part: specifies the implementation of the PL/SQL program
How can a package be deleted ?
The DROP Package command is used to delete the PL/SQL package
Explain Trigger in PL/SQL ?
Trigger is a PL/SQL program which is invoked by Oracle Engine automatically when the specified event occurs.
Triggers can be executed in response to below given events:
DML statements like DELETE, INSERT, or UPDATE
DDL statements like CREATE, ALTER, or DROP
Database operations like SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN
Explain the advantages of using Trigger in PL/SQL ?
Trigger advantages are:
Trigger also prevents invalid transactions
Trigger helps in deriving column values based on table attribute data
Trigger helps in logging table access related information
Trigger helps in auditing
Trigger helps in synchronous table replication
Trigger proivdes security authorization
Trigger helps in enforcing referential integrity
Describe the Trigger Syntax ?
Syntax for creating Trigger:
CREATE [OR REPLACE ] TRIGGER <trigger_name>
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
How many triggers can be applied on table ?
Oracle allows maximum of 12 triggers to be applied on a table.
What are the Trigger Types in PL/SQL ?
The triggers can be classified into 3 types:
Level Triggers
Event Triggers
Timing Triggers
Level Triggers:
The Level Triggers can be further classified into 2 types:
Row Level Trigger: Triggers fires when the row data is updated using the DML statements like DELETE, INSERT, or UPDATE
Statement Level Trigger: Triggers which used FOR EACH Row Clause for triggering the statement
Event Triggers:
DML Event Trigger: Triggers fires when DML statements like DELETE, INSERT, or UPDATE is executed
DDL Event Trigger: Triggers fires when DDL statements like CREATE, ALTER, or DROP is executed
Database Event Triger: Trigger fires when Database operations like SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN is executed
Timing Trigger:
BEFORE Trigger: Trigger fires BEFORE the DML statement is executed
AFTER Trigger: Trigger fires AFTER the DML statement is executed
How the trigger can be disabled ?
ALTER TRIGGER <trigger_name> DISABLE;
How to create a BEFORE DELTE Triggger using Oracle PL/SQL ?
A BEFORE DELETE Trigger are those trigger which gets fired to execute the defined statements before the DELETE operation is executed
Syntax for BEFORE DELETE Trigger:
CREATE [ OR REPLACE ] TRIGGER <trigger_name>
BEFORE DELETE
ON table_name
[ FOR EACH ROW ]
DECLARE
— variable declarations
BEGIN
— trigger code
EXCEPTION
WHEN …
— exception handling
END;
How to DISABLE all the triggers on the table ?
ALTER TABLE <table_name> DISABLE ALL TRIGGERS;
How to ENABLE the Trigger ?
ALTER TRIGGER <trigger_name> ENABLE;
Explain PL/SQL Cursors ?
Cursor is the name given to the temporary work area( also called Context Area) used by Oracle to execute the SQL commands which is also known as Private SQL Area.
A Cursor is is used to retrieve the table data and process the rows (one at a time) returned by the SQL statement
The PL/SQL Cursors can be broadly classified into below 2 types:
Implicit Cursors
Explicit Cursors
Explain the PL/SQL Implicit Cursor Type ?
The PL/SQL implicit Cursor are executed internally by Oracle.
When the user executes a SELECT statement or DML statement (INSERT, UPDATE, DELETE), Oracle reserves a private SQL area in memory called cursor and executes the SQL commands. The implicit cursor returns one row at a time.
Explain the PL/SQL Explicit Cursor Type ?
The PL/SQL explicit cursors are the custom cursors created by the developers to execute a specific set of SQL commands . The below steps need to be performed for using the explicit cursor
1. Declare the custom cursor
2. Open the cursor to reserve the memory area
3. Populate the memory area
4. Fetch the records from the active data set one row at a time
5. Validate and perform program logic
6. Close the cursor
Explain the PL/SQL Implicit Cursor attributes ?
The below given are the Implicit Cursor Attributes and their description
Cursor Attribute: %ISOPEN
Cursor Variable: SQL%ISOPEN
If cursor opens return TRUE else return FALSE
Cursor Attribute: %FOUND
Cursor Variable: SQL%FOUND
If SELECT statement return one or more rows , then returns TRUE else retrun FALSE
Cursor Attribute: %NOTFOUND
Cursor Variable: SQL%NOTFOUND
If SELECT statement does not return one or more rows , then returns TRUE else retrun FALSE
Cursor Attribute: %ROWCOUNT
Cursor Variable: SQL%ROWCOUNT
If returns the number of rows affected by a SELECT statement or DML statement then returns TRUE else return FALSE
Explain the COMMIT Statement in PL/SQL ?
COMMIT in PL/SQL is used to commit the Database SQL Statement data. The below steps are performed when the COMMIT executes:
1. The data in the PL/SQL program gets INSERTED/ UPDATED in the oracle table data as permanent record
2. The locks acquired by the transaction to commit the changes will be Released
3. The table data changes can be queried now
Describe FETCH Cursor example in PL/SQL ?
Create a cursor
CURSOR c1
IS
SELECT course_name
FROM Courses
WHERE course_id = course_id_in;
Now Fetch data for cursor c1
OPEN c1;
FETCH c1 into c1_courseName;
Explain the ROLLBACK Statement in PL/SQL ?
ROLLBACK in PL/SQL is used to UNDO the changes executed in the Database SQL Statement data before the COMMIT is performed. The below steps are performed when the ROLLBACK executes:
1. The executed changes are REVERSED / UNDO to ensure no update on table data
2. The locks acquired by the transaction to commit the changes will be Released
Explain the SAVEPOINT Statement in PL/SQL ?
SAVEPOINT in PL/SQL depicts the current point in the processing of a transaction. In case of transaction ROLLBACK , SAVEPOINT undo parts of a transaction only
What are user -defined functions and its types ?
User defined functions are the custom functions to implement a specific custom logic.
The User defined functions could be of 3 types:
– Scalar Functions
– Inline Table valued functions
– Multi statement valued functions
Explain LOCK TABLE statement in PL/SQL ?
The LOCK TABLE statement is used to lock tables, table partitions, or table sub partitions
Syntax for LOCK TABLE:
LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];
What are the different LOCK Modes provided in SQL ?
The below given are the different lock modes in SQL
– SHARE : allows to execute concurrent queries but does not allow users to update the locked table
– ROW SHARE: allows concurrent access to the table, but does not allow users to lock the entire table for exclusive access.
– SHARE UPDATE : allows concurrent access to the table, but does not allow users to lock the entire table for exclusive access.
– ROW EXCLUSIVE: allows concurrent access to the table, but does not allow users to lock the entire table for exclusive access. and locking the table in share mode
– SHARE ROW EXCLUSIVE: Users can view records in table but does not allow users to update the locked table or from locking the table in SHARE mode.
– EXCLUSIVE: allows queries on the locked table, but no other activities
Q
User
Q
User
Q
User