PL/SQL Basic Interview Questions
1. What are the features of PL/SQL?
- PL/SQL is a procedural language, which provides the functionality of decision making, iteration, and numerous further features of procedural programming languages.
- Using a single command, PL/SQL executes several queries in one block.
- PL/SQL can handle the exception generated in the PL/SQL block. That block is called an exception handling block.
- One can create a PL/SQL unit such as procedures, packages, triggers, functions, and types, which are stored in the database for reuse by applications.
- Applications that are written in PL/SQL are portable to computer hardware or operating systems where Oracle is functional.
2. What do you understand by PL/SQL Table?
An ordered collection of the same type of elements is referred to as a PL/SQL table. position of each element in the ordered collection is determined by its index number. The position of each element in the ordered collection is determined by its own index number. A user-defined type must be declared first for the PL/SQL table, and then it must be declared as a variable.
DECLARE
TYPE Vehicle_SSN_tabtype IS TABLE OF
integer (9)
INDEX BY binary _integer;
Vehicle_SSN_table Vehicle_SSN_tabtype;
3. Explain the basic structure followed in PL/SQL.
By including elements from procedural languages, PL/SQL expands SQL and creates a structural language that is more potent than SQL. A block is PL/SQL’s fundamental building block. Every PL/SQL program is composed of blocks that can be nested inside of one another.
4. What is a PL/SQL cursor?
PL/SQL cursor controls the context area. A cursor holds one or more than one row returned by an SQL statement. set of rows which is held by the cursor is known as an active set.
Two types of cursors exist in PL/SQL.
- Implicit Cursor
- Explicit cursor
5. What is the use of WHERE CURRENT OF in cursors?
LAST FETCHED ROW identify by using WHERE CURRENT OF in cursor. We can use the WHERE CURRENT OF statement for updating or deleting records without using the SELECT FOR UPDATE statement. The record that was last retrieved by the cursor can be updated or deleted using the WHERE CURRENT OF statement.
Syntax:
UPDATE table_name
SET set_clause
WHERE CURRENT OF cursor_name;
OR
DELETE FROM table_name
WHERE CURRENT OF cursor_name;
6. How can a name be assigned to an unnamed PL/SQL Exception Block?
Syntax:
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name, error_code);
BEGIN
// Code
EXCEPTION
WHEN exception _name THEN //exception handling steps
END;
7. What is a PL/SQL Trigger? Give some examples of when “Triggers” might be useful.
When a specific event takes place, the Oracle engine immediately starts. The trigger is already stored in the database If a certain condition is met, the trigger is frequently called from a database. Trigger mode can be activated or deactivated, but running explicitly is not possible.
Syntax:
TRIGGER trigger_name
trigger_event
[ triggers restrictions ]
BEGIN
trigger_action;
END;
In the above syntax, If the restrictions are TRUE or the trigger is unavailable, The trigger_event causes the database to fire the actions of the trigger if the trigger_name is enabled.
Below are some examples of where triggers might be useful.
Complex integrity constraints must be maintained.
To enforce intricate business regulations.
To audit any table information, if necessary.
Triggers are used whenever changes are made to a table and we need to signal other actions after the change has been made.
Additionally, it can be applied to stop fraudulent transactions.
8. When does a DECLARE block have to be present?
In PL/SQL anonymous blocks, such as stand-alone and non-stored procedures, a DECLARE statement is used. The statement in the stand-alone file should come first when they are used.
9. How should comments be written in PL/SQL code?
Two types of comments we can write in PL/SQL code.
- Single Line Comments
- Multiple Line Comments
Single Line Comments: We can use the — symbol to comment on a single line in PL/SQL code.
Multiple Line Comments: We can use the/* lines*/ syntax to comment on multiple lines in PL/SQL code.
DECLARE
— Hi Geeks, This is a single line comment.
BEGIN
/* Hi Geeks,
This is Multiple line comments.*/
END;
10. What is the purpose of the WHEN condition in the trigger?
WHEN condition is used for row level triggers. Trigger fire when a certain condition is met.
11. What are the Differences between SQL and PL/SQL?
SQL |
PL/SQL |
---|---|
SQL manages a relational database management system. | PL/SQL is a programming language for databases. |
SQL can perform a single operation at a time. | PL/SQL can execute multiple operations at the same time. |
SQL is an interpretive language. | it is a procedure language. |
No variable is used in SQL. | PL/SQL consists of variables, datatype, etc. |
SQL directly connects with the database server. | PL/SQL does not directly connect with the database server. |
it is a data data-oriented language. | It is application application-oriented language. |
It can not contain PL/SQL Language. | It can contain SQL inside it. |
It is used to perform DDL and DML operations. | PL/SQL performs blocks, functions, procedures and triggers. |
12. Why are SYSDATE and USER keywords used?
SYSDATE:
The local database server’s current time and date are returned by the SYSDATE keyword.
Example:
SELECT SYSDATE FROM dual;
USER :
The user id of the current session will be returned by using the USER keyword.
Example:
SELECT USER FROM dual;
13. What is the Difference between implicit cursor and explicit cursor?
Implicit Cursor | Explicit Cursor |
---|---|
Implicit cursor is automatically created cursor. | An explicit cursor is defined by the user. |
Implicit cursor can fetch a single row at a time. | The explicit cursor can fetch multiple rows at the same time. |
It gives less programmatic control to programmers. | The explicit cursor is totally controlled by programmers. |
It is less efficient to explicit cursor. | An explicit cursor is more efficient. |
Implicit cursor attributes always use the prefix “SQL” keyword. structure for implicit cursor defined as SQL%attri_name. some mor implicit cursors are SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT. |
Explicit cursors structure: curs_name%attri_name Some more explicit cursors are curs_name%FOUND, curs_name%NOTFOUND, curs_name%ROWCOUNT. |
14. Tell the importance of %TYPE and %ROWTYPE data types in PL/SQL.
%Type:
This datatype is used for specified tables to define the variable as its column name datatype.
Syntax:
vAttributName Attribute.Attribute_Name%TYPE;
In the above syntax, the datatype of Attribute_Name is assigned to the variable named vAttributeName.
%ROWTYPE:
Use %ROWTYPE if the programmer doesn’t know the datatype of the specified column but still needs to assign it to the variable. It is nothing more than an assignment in an array in which we can specify a variable and define the entire row datatype.
Syntax:
Rt_var_Student Student%ROWTYPE;
%ROWTYPE assigns the data type of the Student table to the Rt_var_Student variable.
15. What are the differences between ROLLBACK and ROLLBACK TO statements in PL/SQL?
- The ROLLBACK command is used to undo any modification made since the transaction’s start.
- The transaction may only be rolling back using the ROLLBACK TO command up to a SAVEPOINT. The transaction stays active even before the command is provided since the transactions cannot be rolled back before the SAVEPOINT.
16. What are the uses of SYS.ALL_DEPENDENCIES?
The dependencies between all the procedures, packages, triggers, and functions that the current user can access are described by SYS.ALL_DEPENDENCIES.
17. What the virtual tables exist during the execution of the database trigger?
- OLD and NEW two are virtual tables that exist during the execution of the database trigger.
- OLD and NEW both are accessible by UPDATE statement.
- INSERT statement can access only NEW value.
18. What is the Difference between the cursors declared in procedures and in the package specifications?
The cursor declared in a package specification is global and can be accessed by other procedures or procedures in the package. A cursor declared in a procedure is local that can not be accessed by other procedures.
19. What is purposes of COMMIT, ROLLBACK and SAVEPOINT statements in PL/SQL?
COMMIT statement: The changes made during a transaction are saved permanently by the COMMIT command.
Syntax:
DECLARE
BEGIN
// command;
COMMIT;
END;
ROLLBACK statement: It is used to undo any modification made since the transaction’s start.
Syntax:
DECLARE
BEGIN
// command;
ROLLBACK;
END;
SAVEPOINT statement: A transaction point that can be utilised to roll back to a certain point in the transaction is created using the SAVEPOINT statement.
Syntax:
DECLARE
BEGIN
SAVEPOINT sp;
// command;
ROLLBACK TO sp;
END;
20. How can we debug our PL/SQL code?
To debug our PL/SQL code, we can use the DBMS_OUTPUT and DBMS_DEBUG statements. The output is printed to the standard console via DBMS_OUTPUT. The output is printed to the log file by DBMS_DEBUG.
PL/SQL Interview Questions
PL/SQL (Procedural Language/Structured Query Language) is a powerful extension of SQL used in Oracle databases. PL/SQL is a block-structured language that enables developers to combine the power of SQL with procedural statements. It was developed by Oracle in 1992 in order to overcome some limitations of SQL which given below:
- SQL does not include decision-making, looping, or branching capabilities.
- There is no error-checking feature available when manipulating data.
- When SQL statements are sent to the Oracle engine simultaneously, the increased traffic can cause a decrease in execution speed.
PL/SQL and SQL are strongly integrated. PL/SQL supports various features such as cursors, exceptions, packages, triggers, and bind variables that enhance the functionality and performance of SQL statements. PL/SQL can update, query, and transform data in a database and also gives high productivity to developers.
In this article, we provide you with the top 50+ PL/SQL interview questions with answers that cover everything from the basics of PY/SQL function to advanced PL/SQL concepts such as PL/SQL Blocks, Bulk Processing, Security and Privileges. Whether you are a fresher or an experienced IT professional, this article gives you all the confidence you need to ace your next PL/SQL Interview.
Table of Content
- PL/SQL Basic Interview Questions
- PL/SQL Intermediate Interview Questions
- PL/SQL Advanced Interview Questions