Top PL/SQL Interview Questions and Answers (2023)

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.

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-
 

• DBMS Interview Questions
• DB2 interview questions
• Selenium Interview Questions
• Javascript interview questions
•  SAP FICO Interview Questions 
   

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