Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Beginner Level PL SQL Interview Questions
2.1.
1. What is PL/SQL?
2.2.
2. What are the characteristics of PL/SQL?
2.3.
3. How is PL/SQL different from SQL?
2.4.
4. How the process of PL/SQL is compiled?
2.5.
5. What is a PL/SQL table?
2.6.
6. Explain the basic structure of PL/SQL?
2.7.
7. What is a cursor in PL/SQL and explain its type?
2.8.
8. When does a Declare block become mandatory?
2.9.
9. What are database triggers?
2.10.
10. What are the uses of a database trigger? Give its syntax?
3.
Intermediate Level PL SQL Interview Questions
3.1.
11. How to write comments in PL/SQL?
3.2.
12. What is a Raise_application_error in PL/SQL?
3.3.
13. How to assign a name to an unnamed PL/SQL exception block?
3.4.
14. Differentiate between compile-time errors and run-time errors?
3.5.
15. What are COMMIT, ROLLBACK and SAVEPOINT statements in PL/SQL?
3.6.
16. What is a mutating table error?
3.7.
17. What is the use of SYSDATE and USER?
3.8.
18. How can we manipulate the character data using the functions?
3.9.
19. Why do we use SYS.ALL_DEPENDENCIES?
3.10.
20. What is the difference between the cursor in the procedure and the cursor in the package specification?
4.
Advanced Level PL SQL Interview Questions
4.1.
21. Explain the concept of bulk processing in PL/SQL.
4.2.
22. How %TYPE and %ROWTYPE are different?
4.3.
23. What does a typical PL/SQL package contain?
4.4.
24. What are records in Pl/SQL?
4.5.
25. What is the procedure in PL/SQL?
4.6.
26. What is the function in Pl/SQL?
4.7.
27. How are exceptions handled in PL SQL?
4.8.
28. What are triggers in PL/ SQL, and how can we use them to perform complex tasks?
4.9.
29. How you can debug a PL SQL code?
4.10.
30. What are autonomous transactions in PL SQL?
5.
Frequently Asked Questions
5.1.
How to prepare for a PL SQL interview?
5.2.
What is PL SQL basic concept? 
5.3.
What are commands in PL SQ?
5.4.
What are the basic elements of PL SQL? 
6.
Conclusion
Last Updated: Mar 27, 2024
Medium

PL SQL interview questions

Create a resume that lands you SDE interviews at MAANG
Speaker
Anubhav Sinha
SDE-2 @
12 Jun, 2024 @ 01:30 PM

Introduction

This blog will discuss the important PL SQL interview questions. PL/SQL stands for Procedural Language Extensions to SQL (Structured Query Language). Oracle developed it to address the shortcomings of SQL and make it easier to construct and manage essential applications comprehensively.

pl sql interview questions

In the next section, we will discuss important PL SQL Interview Questions. Over analyzing so many pl sql interview questions these are some of the questions that will surely help you to crack the pl sql interview to land your dream job. 

Beginner Level PL SQL Interview Questions

1. What is PL/SQL?

Oracle Corporation developed PL/SQL, which stands for Procedural Language Extension to Structured Query Language. It allows developers to build complicated interactions and queries using procedures, control structures such as branching, and functions and modules. It has a strong SQL integration mechanism that can handle static and dynamic Sql.

2. What are the characteristics of PL/SQL?

The following are some of the essential features of PL/SQL:

  • It's made up of blocks.
  • It can be used in Oracle-based environments.
  • Integration with Oracle's data dictionary
  • Thanks to stored procedures, application sharing is more efficient.

3. How is PL/SQL different from SQL?

PL/SQL

SQL

It is a SQL extension that includes procedures, functions, and many more capabilities. This is a query language that helps us to interact with the database.
Can accomplish difficult tasks in a high-level programming language, such as while loops and if-else expressions. Supports operations like insert, update, delete, etc. 
The complete block of statements is transmitted to the database server simultaneously to be executed, saving time and improving efficiency. It consumes time since one statement executes at a time.
Error handling can be customized. No error handling.

4. How the process of PL/SQL is compiled?

Syntax checking, binding, and p-code generation are part of the compilation process.

Syntax checking looks for compilation issues in PL SQL scripts. After all, mistakes have been fixed, the variables that hold data are given a storage address. Binding is the term for it. The PL SQL engine's P-code is a set of instructions. For named blocks, P-code is saved in the database and used the next time it is run.

5. What is a PL/SQL table?

  • PL/SQL tables are objects of the table type that are modeled after database tables. They're a means to create arrays that are nothing more than temporary tables in memory to speed up processing.
  • These tables can move large amounts of data quickly and easily.

6. Explain the basic structure of PL/SQL?

[DECLARE]
--declaration statements (optional)
BEGIN
--execution statements
[EXCEPTION]
--exception handling statements (optional)
END;

7. What is a cursor in PL/SQL and explain its type?

A PL/SQL cursor is a pointer to a memory location containing SQL statements and statement processing metadata. A context area is a name given to this memory space. This unique region uses a specific feature called the cursor for obtaining and processing more than one row. The cursor selects numerous entries from the database, which are then processed individually by software.

There are two types of PL/SQL cursors:

  1. Implicit Cursor: While invoking any of the commands SELECT INTO, INSERT, DELETE, or UPDATE, Oracle implicitly creates a cursor. Oracle handles the cursor execution cycle internally and returns the cursor's information and status using the cursor attributes ROWCOUNT, ISOPEN, FOUND, and NOTFOUND.
  2. Explicit Cursor: This cursor is a SELECT statement declared in the declaration block. The programmer must control the cursors' execution cycle, which begins with OPEN and ends with FETCH and CLOSE. Oracle defines the SQL statement execution cycle as well as the cursor that is associated with it.

8. When does a Declare block become mandatory?

Anonymous PL/SQL blocks, such as non-stored and stand-alone procedures employ this statement. When using them in a stand-alone file, the statement should appear first.

9. What are database triggers?

A named database object that encapsulates and defines a series of actions to be done in response to an insert, update, or delete operation against a table is known as a PL/SQL trigger. The Construct TRIGGER statement in PL/SQL is used to create triggers.

10. What are the uses of a database trigger? Give its syntax?

It is employed for the following purposes:

  • Check for data changes.
  • Keep track of occurrences in a transparent manner.
  • Ensure that complex business rules are followed.
  • Keep duplicate tables in good condition.
  • Calculate column values
  • Make complex security authorizations a reality.

Syntax: 

create trigger [trigger name]
[before | after]
on [table name]
[for each row]
[trigger_body]
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Intermediate Level PL SQL Interview Questions

11. How to write comments in PL/SQL?

Comments help us to understand the logic of the code. There are two ways to write comments in Pl/SQL:

  1. Using (--): This is used to write single-line comments.
  2. Using (/*....*/): This is used to write multi-line comments.

12. What is a Raise_application_error in PL/SQL?

It's a DBMS STANDARD procedure that lets you send user-defined error messages from a database trigger or stored sub-program.

13. How to assign a name to an unnamed PL/SQL exception block?

DECLARE 
  your_exception_name MINE_EXCEPTION; 
  PRAGMA EXCEPTION_INIT (your_exception_name, error_code); 
BEGIN 
-- Write your PL/SQL Logic
EXCEPTION
 WHEN your_exception_name THEN
    -- Steps to handle exception
END;

14. Differentiate between compile-time errors and run-time errors?

A PL/SQL compiler can readily discover a compile-time error. Take, for example, poor spelling.

An exception-handling section in a PL/SQL block is used to handle a run-time error. Consider the SELECT INTO query, which returns no rows.

15. What are COMMIT, ROLLBACK and SAVEPOINT statements in PL/SQL?

Commit: Any changes made to the database during the current transaction are made permanent with the COMMIT command. The changes are also available to other users after a commit. See "Overview of Transaction Processing in PL/SQL" for additional information on PL/SQL transaction processing.

Rollback: The flipside of the COMMIT statement is the ROLLBACK statement. It reverses part or all of the modifications made to the database during the current transaction. See "Overview of Transaction Processing in PL/SQL" for further information. In PL/SQL, the SQL ROLLBACK statement can be incorporated as static SQL.

Savepoints: The SAVEPOINT statement identifies and marks the current point in a transaction's processing. Savepoints undo portions of a transaction rather than the entire transaction when using the ROLLBACK TO command. See "Overview of Transaction Processing in PL/SQL" for further information.

16. What is a mutating table error?

This error occurs when a trigger tries to update a row that it is currently utilizing. Views or temporary tables are used to fix it, and the database selects one and changes the other.

17. What is the use of SYSDATE and USER?

SYSDATE: The current time and date on the local database server are returned by this keyword. SYSDATE is the syntax. We utilize the TO_CHAR function on SYSDATE and specify the format we require to extract the portion of the date.

USER: This keyword is used to return the user id of the current session?

18. How can we manipulate the character data using the functions?

The functions that we use to manipulate the character data.

Function Description
LEFT It returns the specified number of characters from the left of the string.
RIGHT It returns the specified number of characters from the right of the string.
SUBSTRING This function would choose data from any section of the string starting at a specified start point and continuing for the specified amount of characters.
LTRIM It removes all the whitespace from the left part of the string.
RTRIM It removes all the whitespace from the right part of the string.
UPPER It is used to convert all characters to the upper case of the string.
LOWER It is used to convert all characters to the lower case of the string.

19. Why do we use SYS.ALL_DEPENDENCIES?

All dependencies between procedures, packages, triggers, and functions that are accessible to the current user are described in SYS.ALL DEPENDENCIES. Name, dependency type, type, referenced owner, and other columns are returned.

20. What is the difference between the cursor in the procedure and the cursor in the package specification?

Cursors defined in procedures will have a local scope, which means other procedures can't use them. Cursors stated in package specifications, however, are given the global scope and can thus be utilized and accessed by other methods.

Also see, Power Electronics Interview Questions

Advanced Level PL SQL Interview Questions

21. Explain the concept of bulk processing in PL/SQL.

Bulk processing in PL/SQL involves handling multiple rows of data in a single operation, unlike traditional row-by-row processing. It leverages collections such as PL/SQL tables or arrays to process data efficiently.

22. How %TYPE and %ROWTYPE are different?

%TYPE: The attribute that declares a variable with the same data type as a column in a table.

Example:

DECLARE
departmentId
departments.department_id%TYPE;

%ROWTYPE: This attribute declares a RECORD variable with the same structure as a table row. The RECORD is the row that contains fields with the same data types and names as the table or view's columns.

Example:

DECLARE
department_rec
departments.%ROWTYPE;

23. What does a typical PL/SQL package contain?

Packages are schema objects that condense functions, processes, variables, and other items into a single location. Data that should be included in all packages.

  • Specifications for the package
  • Body of the package

24. What are records in Pl/SQL?

Records are collections of data of various types that can be linked together as fields. Table-based records, programmer-based records, and cursor-based records are the three types of records provided by PL/SQL.

25. What is the procedure in PL/SQL?

A procedure is a collection of PL/SQL statements that their names can call. A call specification (also known as a call spec) specifies how to call a Java method or a third-generation language (3GL) procedure from SQL and PL/SQL. When a call is made, the call spec instructs Oracle Database, which Java method to call.

26. What is the function in Pl/SQL?

A PL/SQL function is a reusable software unit saved as a schema object in the Oracle Database, similar to a procedure. The function name and a RETURN clause specify the datatype of the returned value in the function header. Each function argument can be in three modes: IN, OUT, or INOUT. The function body is similar to the procedure body, divided into three sections: declarative, executable, and exception-handling.

27. How are exceptions handled in PL SQL?

For handling exceptions in PL SQL there are EXCEPTION blocks. It consists of code that executes when there is an exception. We can define our own exceptions or use the pre-defined ones. The custom exceptions can be defined using the RAISE statement.

If you want to associate an exception with a named error code, then EXCEPTION INIT pragma is used.

28. What are triggers in PL/ SQL, and how can we use them to perform complex tasks?

The blocks of PL SQL that are executed automatically when a particular event occurs in the database are known as triggers. For example, when the user performs the insert, update, or delete operation, that triggers an event. They maintain data integrity. 

Therefore triggers are event-driven. You can use them for automating the database actions. With triggers, we can invoke stored procedures/ functions, this enables us to perform more complex tasks.

29. How you can debug a PL SQL code?

We can use DBMS_OUTPUT.PUT_LINE procedure for debugging. It shows us the debug information in runtime. Some IDEs provide the facility of in-built debugging tools for PL SQL code. We can also use logging frameworks or write down our own logging statements to identify the issues. The DBMS_DEBUG package can also be used if advanced debugging is needed.

30. What are autonomous transactions in PL SQL?

The independent transactions in a vast transaction are known as autonomous transactions. They are useful when there is a requirement for logging or changes in the track that needs to be made without the result of the main transition being affected. This helps to main data consistency.

You can use it for performing operations that are separated from the main transactions. They must be rolled back independently or committed. 

Frequently Asked Questions

How to prepare for a PL SQL interview?

If one wants to prepare for a PL SQL interview, then one must clear all the basic concepts.  Further, you must refer to the questions which are mostly asked in the interview and prepare accordingly. Reading interview experiences also helps a lot.

What is PL SQL basic concept? 

Features of PL SQL and its working, basic syntax, commands, identifiers,  data types, variables, constraints, literals, arrays, functions, cursors, records, and triggers, are some of the basic topics of PL SQL that one must complete thoroughly before moving onto advanced topics.

What are commands in PL SQ?

Using commands in PL SQL, we can execute certain actions in a program. Some of the common commands are DECLARE,  LOOP, BEGIN, END, IF_THEN_ELSE, CASE, COMMIT, EXIT, etc. One must have a complete understanding of all the basic commands in PL SQL.

What are the basic elements of PL SQL? 

The basic elements of PL/SQL include variables, loops and conditional statements, exception handling, cursors for database interaction, and procedures/functions forming its fundamental components for organized and reusable code.

Conclusion

In this article, we have extensively discussed the PL SQL Interview Questions. We discussed the different types of questions along with their concepts.

We hope this blog has helped you enhance your knowledge of the PL SQL Interview Questions. You can also check out our other content on the Coding Ninjas Website-
 

Do upvote our blog to help other ninjas grow. Happy Coding!

Previous article
SQL Query Interview Questions
Next article
Top PL/SQL Interview Questions and Answers (2023)
Live masterclass