PL/SQL Faq and Exception handling...

SET 1
1 message

Ragunath Gunasekaran <ragunath90@gmail.com> Tue, Jan 22, 2019 at 11:33 AM

To: Vish Viswanathan <viswarao90@gmail.com>

Infosys Limited India has opportunities for talent with experience in 

Oracle PL/SQL
SQL 

Please click the link to view detailed job description And to apply 

🔥https://infy.com/2szNz2H
Doubts you may reach me on.,

🔥🔥https://bit.ly/reachRaghu
Top crossing repeated 50+ QA and Exception handling scenarios

Here are 50+ frequently asked PL/SQL interview questions and answers which will definitely help you out to
crack one of the toughest interviews.
 

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.

PL/SQL Exception handling

An exception is a condition which executes when an error occurs during execution of a PL/SQL program. PL/SQL
provides a feature that lets the programmers to handle these exceptions in the exception block in a PL/SQL
program. Exception block contains the methods (defined by the programmer) which can provide a particular
action that needs to be taken to come out of this error or exception and prevent the program from terminating
abnormally. This mechanism is known as error handling or exception handling in PL/SQL.

Generally, there are two types of exceptions:

1. System-defined Exceptions

2. User-defined Exceptions

System-defined Exceptions

These are pre-defined exceptions situated in Oracle SQL. It is involuntarily raised or executed on encountering a
program that violates any Oracle SQL rules.

System-defined exceptions normally are of two types:

1. Named Exceptions
These are system defined exceptions which are pre-defined in the Oracle SQL and normally have a name for
reference intention. Few of the named exceptions are as follows:

a. ZERO_DIVIDE: This error occurs when a user tries to divide a number by zero.

b. NO_DATA_FOUND: This error occurs when a user fires a query that doesn’t return anything.
c. CURSOR_ALREADY_OPEN: This error occurs when you try to access a Cursor which is already open or which is
already being used by any other program.

d. TOO_MANY_ROWS: This error is raised when you try to fetch more than a single row into a variable or a record.

e. LOGIN_DENIED: This error is raised when a user tries to log into the Oracle database with a wrong username or
a password.

f. INVALID_CURSOR: This error occurs when you perform an invalid task on a cursor like fetching data from a
cursor that is closed.

g. STORAGE_ERROR: This error occurs when PL/SQL database runs out of memory or memory gets
malfunctioned.

2. Unnamed Exceptions
These are the system defined exceptions that the Oracle provides to its users. These exceptions don’t have a
naming system to its block structure. However, such exceptions do have an error code and an error message
associated to it. 

Such exceptions are handled either by associating the exception code to a name and using it as a named
exception or by using the WHEN other THEN exception handler mechanism.

However, Oracle provides us a feature to assign a name to an unnamed System exception which includes the
usage of a Pragma which is also known as an Exception_Init. This mechanism is used to link the Oracle system
error code to user defined exception name.

Syntax using Exception_Init

1 DECLARE
2 Exception_Name Exception;
3 Pragma
4 Exception_Init (Exception_Name, Error_Code);
5 Begin
6 Execution Section....
7 Exception
8 WHEN Exception_Name THEN
9 Exception Handler....
10 END;
User-defined Exceptions

The user-defined exceptions are the ones that are developed or programmed by the programmer. These are
explicitly declared in the declaration section and explicitly raised in the execution section.

A PL/SQL exception definition contains parts such as exception type, error code/ number and an error message
for the end-user. Every exception whether it is user-defined or pre-defined (System) has an error code associated
with it.

Syntax for User-defined Exceptions

1 Declare
2 Declaration Section
3 Begin
4 Execution Section
5 Exception
6 Exception Section…
7 When ExceptionA Then
8 ExceptionA-Handling Statements
9 When ExceptionB Then
10 ExceptionB-Handling Statements
11 When ExceptionZ Then
12 ExceptionZ-Handling Statements
13 End;

Example

1 declare
 2 admin_id integer;
3 admin_name varchar2(20);
4 begin
5 select ano,aname into admin_id,admin_name from Admin
6 where ano=admin_id;
7 dbms_output.put_line(admin_name);
8 exception
9 When NO_DATA_FOUND Then
10 dbms_output.put_line('Administrator Details Unmatched');
11 When OTHERS Then
12 <div style="margin:0px;padding:0px 5px;border:0px;vertical-
13 align:baseline;font-family:inherit;background:0px
14 center;height:inherit;font-size:inherit!important;line-height:inh