Being prepared for your PL/SQL interview is the most important step in your job search process. It is important to know that first impressions matter and could very well be the decisive factor in getting that job you’ve been trying for.
List of PL/SQL Interview Questions and Answers
Q1. Can you describe 3 advisory statistics that you can gather?
The three advisory statistics that can be collected are:
• Buffer Cache Advice
• Segment Level Statistics
• Timed Statistics
Q2. Can you explain what a Cursor is along with its different attributes?
Cursor is a temporary buffer area which has been created in the system memory when an SQL statement is executed. It is used in order to process multiple records and also record by record tabs. There are two types of cursors available: Implicit Cursors and Explicit Cursors.
In addition, there are four different cursor attributes listed as under:
• %Found
• %Not Found
• %Isopen
• %Rowcount
Q3. Explain the process to rollback a particular part of a procedure or any PL/SQL Program.
During definition of a PL/SQL program, Savepoints allows you to rollback part of a transaction instead of the whole transaction.
Q4. Can you describe the range of the Error Codes in PL/SQL Exceptions?
The error codes in PL/SQL Exception falls between the range of -20000 to -20899.
Q5. Can you highlight the various methods used to trace the PL/SQL code?
Tracing code can be described as an important function which is used to measure the performance of the code during the runtime. Accordingly, there are four different methods which are used for tracing. These are:
• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION and DBMS_MONITOR
• trcsess and tkproof utilities
Q6. What are sequences in PL/SQL?
Sequences are objects that are used to generate a number sequence without an overhead of locking. This comes particularly handy when one needs to create a unique number in order to act as a primary key. However, the one disadvantage is that the sequence number is lost if a rollback is executed on a transaction.
Q7. Can you list out the various loops available in the PL/SQL Database?
The following loops are used in PL/SQL:
• Simple Loop
• For Loop
• Nested Loop
• While Loop
Q8. Describe the various data types that are available in PL/SQL.
There are two primary data types available in PL/SQL. These are further subdivided into several data types which are as under:
1. Composite Data Types: Record, Table, etc.
2. Scalar Data Types: Date, Number, Boolean, Varchar, Long, Varchar2, etc.
Here’s an additional list of 8 popular PL/SQL interview questions if you’re on the lookout for a few more examples that are likely to be asked during your interview.
1. Can you explain the difference between a Function, Procedure, and a Package?
2. Can you describe the difference between the SQL*loader and import utilities?
3. Can you point out the different types of Exceptions?
4. Describe the concept of context area in PL/SQL.
5. Point out the Default Cursor in Oracle PL/SQL.
6. Describe what SQLERRM and SQLCODE are and explain their importance.
7. List out the different Loop Control Structures used in PL/SQL.
8. Can you touch upon the concepts of Union, Union All, Intersect and Minus in PL/SQL?
If you’re not confident, make sure to practice your answers in front of friends/family so that you can feel more comfortable. In addition, ensure that you arrive at your interview at least 10-20 minutes before time so you have time to gather your thoughts.
Related Articles :
