PL/SQL Interview Questions and Answers

Q1. How can you Rollback a particular part of a procedure or any PL/SQL Program?
It can be done using Savepoints during definition of a PL/SQL program.

Q2. Is there a PL/SQL Engine in SQL*Plus?
Unlike Oracle Forms, SQL*Plus does not have an SQL Engine.  Thus, all your PL/SQL are sent directly to the database engine for execution. This makes it comparatively more efficient as SQL statements are not uncovered and sent to the database individually. 

Q3. What are the different types of Exceptions?
User-Defined and System Exceptions.

Q4. What is the basic use of a Cursor?
A Cursor is basically used to access values from multiple records in the database or a table.

Q5. What is the basic difference between a Procedure and a Function?
Both Procedures and Functions have the capability of accessing parameters but Functions return a value to the PL/SQL Block whereas Procedures do not return any value.

Q6. What are the modes of parameters in a PL/SQL Procedure?
In, Out and In-Out are different modes of a PL/SQL Procedure.

Q7. What are the different components of a PL/SQL trigger?
Trigger Action, Trigger Restriction and Trigger Action are the different components of a PL/SQL Trigger.

Q8. Describe the disadvantage of Database Trigger over Stored Procedures? 
We cannot control the execution of a Trigger whereas a Stored Procedure Execution can be controlled by the programmer.

Q9. What are the properties of a Database?
The properties of a Database are also known as ACID Properties. These are Atomicity, Consistency, Isolation and Durability.

Q10. How can you take an Input from a User of the Database?
You can take it using the Input Operator. This is as follow:
val1:=&val1;

Q11. What is %rowtype used for?
This is used if you want to create new variable which needs to fetch in a variable value from the database and you don’t know the Datatype of that variable. %rowtype will automatically change its datatype to the one in the database.

Q12. What are the Parameters for raise_application_errror()?
The parameters are: Error Code and an Error Message.
The Syntax is: raise_application_error(Error Code, Error Message);

Q13. What is the difference between a Rollback Command and a Commit Command?
A Commit command is used to save the current transaction in the database in which modification is done to the database using Database Manipulation Language (DML) commands. A Rollback command is however used to undo the modifications done by the DML commands previously.

Q14. What is a Statement Level Trigger?
A Statement Level trigger is executed when a statement or a command affects the whole table which is independent of a row in any table.

Q15. What are the different parts of an Explicit Cursor?
The different parts in the process of making an Explicit Cursor are as follows:
1. Declaring the Cursor
2. Opening the Cursor
3. Fetching the Cursor
4. Closing the Cursor

Q16. Enlist various types of PL/SQL Exceptions?
The PL/SQL  Exceptions are as follows:
1. Zero_Divide
2. No_Data_Found
3. Cursor_Already_Open
4. Login_Denied

Q17. How can you enable or disable a trigger?
To Enable a Trigger, the Syntax is: Alter Trigger Trigger_Name Enable;
To Disable a Trigger, the Syntax is: Alter Trigger Trigger_Name Disable;

Q18. How can you assign a Name to an Un-Named PL/SQL Exception Block?
You can assign a name to an Un-Named Exception using Pragma, also known as Exception_init.

Q19. What is the range of the Error Codes in PL/SQL Exceptions?
The range of Error Code in PL/SQL Exception is between -20000 and -20899.

Q20. What is Context Area in PL/SQL?
Oracle processes the executed SQL Statement in a separate memory zone called as Context Area. It contains information regarding SQL Query, number of rows accessed by it, information retrieved from database tables/records and many more.

Q21. Which is the Default Cursor in Oracle PL/SQL?
Implicit Cursors are the Default Cursor in PL/SQL. These cursors are automatically activated when DML statements are encountered such as Insert, Delete or Update.

Q22. Why is closing the Cursor required during explicit cursor development?
It is important because it will consume system memory while it is in active state and if it is not closed or terminated then it won’t let the other things in the memory as memory will be occupied and later on it will be full. Hence, deletion is necessary.

Q23. Enlist various loops in PL/SQL Database.
The various loops used in PL/SQL are as follows:
1. Simple Loop
2. For Loop
3. Nested Loop
4. While Loop

Q24. Explain about SQLERRM and SQLCODE and their importance.
SQLERRM Returns the Error Message for the Last Error that came across. SQLERRM is useful for WHEN OTHERS Exception. They can be used for Reporting purpose in Error Handling. They can also store the Error in the Code and store it in the Error Log Table. SQLCODE Returns the Value of the Error Number for the previous error. 

Q25. What rules are to be taken care of when doing comparisons using NULL?
1. A NULL should never be TRUE or FALSE. 
2. If a value in an expression is NULL, then the expression itself evaluates to NULL except for Concatenation Operator.
3. A NULL is never equal or unequal to other values.
4.NULL is not a value, it represents absence of data. So NULL is something UNIQUE

Q26. What is the Difference between Runtime Errors and Syntax Errors?
A Runtime Error is handled with the help of Exception Handling Mechanism in a PL/SQL Block whereas a Syntax Error such as a spelling mistake is efficiently detected by the SQL Compiler.

Q27. Explain about Pragma Exception_Init.
It allows us to handle Oracle Pre Defined Messages wherein we can replace our own Message. We can therefore instruct the compiler to link the user specified message to Oracle Pre Defined Message during Compilation Time.
Syntax:  Pragma Exception_Init (Exception_Name, Error_Code)

Q28. What is Mutating Table Error?
It occurs when a Trigger tries to update a row that is currently in execution stage. So it is solved out by using temporary tables and views. 

Q29. What is the maximum limit of applying Triggers to a Table?
The maximum number of Triggers that can be applied to one table is 12.

Q30. What is the method to find out whether a Cursor is open or not?
The Cursor Status Variable can be used to find out whether the Cursor is open or not. It is %ISOPEN.

Q31. What is a Row Level Trigger?
A Statement Level Trigger is executed whenever a statement or a command affects a row in a table by Database Manipulation Command (DML) statements like Delete, Insert or Update.

Q32. What is an Active Set?
The set of rows that a Cursor holds at a single point of time is called as an Active Set.

Q33. What are the different Loop Control Structures used in PL/SQL?
The different Loop Control Structures in PL/SQL are as follows:
1. Exit
2. Exit-When
3. Continue
4. Goto

Q34. What is set serveroutput function used for in PL/SQL?
In PL/SQL, we frequently require to produce the Output on the console. We generally do it using dbms_output.put_line() function. For this to work properly and display the output on the console screen, we need to first set the server output to ON state. For this, the command is: set serveroutput on;

Q35. Which Datatypes are available in PL/SQL?
There are mainly two main Datatypes available in PL/SQL which are further sub-divided into many datatypes and these are:
1. Composite Datatypes: Record, Table, etc.
2. Scalar Datatypes: Date,Number, Boolean, Varchar, Long, Varchar2, etc.

Q36. Explain the difference between Truncate and Delete?
Truncate is much faster than Delete Command. It basically resets the Memory Blocks after Execution. Delete is a Database Manipulation Language (DML) Command whereas Truncate is a Data Definition Language (DDL) Command and it is comparatively slower.

Q37. Explain about Package in short.
A Package is a Schema Object which assembles logically relate PL/SQL Datatypes and Sub-Programs.  It is actually a combination of Procedures, Functions, Record Type and Variables. It enhances Application Development and this provides Modular Programs. It also provides Encapsulation which hides data from Unauthorized Users.

Q38. What are the disadvantages of Cursors and is there any alternative to it?
The processing of Cursors is very slow as compared to Joins. Hence, Joins can be an alternative to Cursors.

Q39. What is the method to display messages in Output Files and Log Files?
The Output Files and Log Files can be used to display messages by using the following: Fnd_file.put_line.

Q40. What is the difference between Grant command and Revoke command?
A Grant command permits the End-User to perform certain activities onto the database whereas a Revoke command prevents the End-User from making any changes to the Database.

Q41. Enlist the Attributes of a Cursor in PL/SQL.
%Rowcount: This attribute checks the number of rows that are updated, deleted or fetched.
%Isopen: This attribute checks whether the Cursor you want to access is currently open or closed.
%Found: This attribute checks if the Cursor fetched a row. It returns a TRUE  if any row is fetched.                                                                             %NotFound: This attribute checks if the Cursor fetched any row or not. It returns a TRUE value if any row is not fetched.

Q42. What is raise_application_error?
It is a Procedure which is included in the Dbms_Standard Package that allows User-Defined Messages to be issued from the Database Stored Sub-program or a Trigger.

Q43. Explain the difference between Varchar and Char?
Varchar doesn’t sets aside memory location during declaration of a variable. It stores the value only after a variable is defined or assigned a value. Its storage capacity is 32767 Bytes.

Char however preserves the memory location mentioned in the variable declaration even if it is not used. The maximum storage capacity for a Character variable is 255 Bytes.

Q44. Explain Union, Union All, Intersect and Minus in PL/SQL.
Union: It returns all the distinct rows selected by either of the Queries.
Union All: It returns all the rows selected by one of the queries which includes all the duplicates.
Intersect: It returns all the distinct rows selected by both the queries.
Minus: It returns all the distinct rows selected by the first query and not by the second one.

Q45. What is the difference between Varchar2 and Varchar?
Varchar2 Datatype is memory efficient as it variable memory storage datatype whereas a Varchar Datatype variable is not memory efficient as it has fixed memory storage. Varchar occupies space for NULL values whereas Varchar2 variable does not. Varchar can store upto 2KB whereas a Varchar2 Datatype can store upto 4KB.

Q46. What is a Mutating Table Error and how can you solve it?
It occurs if the Trigger tries to update a row that is currently being used. This is solved either by using the Temporary Tables or by the Views.

Q47. Enlist the packages provided by the Oracle for use by the Developers?
Oracle provides the packages such as Dbms_Transaction, Dbms_Alert, Dbms_Job, Dbms_Ddl, Dbms_Output, Dbms_Utility, Dbms_Lock, Dbms_Sql, Dbms_Pipe and Utl_File.

Q48. Explain the difference between SQL and SQL *PLUS.
SQL represents Structured Query Language and is used to manage the database. It is a Non Procedural Programming Language and a Standard Language for Relational Database Management System (RDBMS). However, SQL *PLUS is an Oracle specific program that executes SQL commands using PL/SQL blocks.

Q49. Explain Bulk Collect.
It is a way of fetching a very big collection of data. With the help of Oracle Bulk Collect, the PL/SQL Engine indicates the SQL Engine to collect more than one row at a single point of time and stores them into a collection. Then it switches back to the PL/SQL Engine. During the Bulk Collect, Context Switch at one point. The performance improvement would be better with the more number of rows fetched into the collection.

Q50. Enlist the Types of Triggers and its combinations.
There can be various types of combinations used in Triggers which are After, Before, Insert, Update, Delete, Row, Table and other such combinations.

Q51. Enlist the methods to recover from Deadlock
Selection of a Victim, Rollback and Starvation are the methods to recover from a Deadlock.

Q52. Enlist Concurrency Control Schemes.
The various Concurrency Control Schemes are Lock Based Protocol, Validation Base Protocol, Time Stamp Based Protocol and Multi-version Schemes.

So this was the list of all the important PL/SQL interview questions and answers that are very frequently asked in the interviews. If you found any information incorrect or missing in above list then please mention it by commenting below.