Oracle interview questions /Answer Set-5

Explain user defined exceptions in oracle.

A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as exception. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to raise internal exceptions.

Exception:

DECLARE
userdefined EXCEPTION;


BEGIN

RAISE userdefined;


EXCEPTION
WHEN userdefined THEN

END;


Explain the concepts of Exception in Oracle. Explain its type.

Exception is the raised when an error occurs while program execution. As soon as the error occurs, the program execution stops and the control are then transferred to exception-handling part.
There are two types of exceptions:
1. Predefined : These types of exceptions are raised whenever something occurs beyond oracle rules. E.g. Zero_Divide
2. User defined: The ones that occur based on the condition specified by the user. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly.


How exceptions are raised in oracle?

There are four ways that you or the PL/SQL runtime engine can raise an exception:
• Exceptions are raised automatically by the program.
• The programmer raises a user defined exceptions.
• The programmer raises pre defined exceptions explicitly.


What is tkprof and how is it used?

tkprof is used for diagnosing performance issues. It formats a trace file into a more readable format for performance analysis. It is needed because trace file is a very complicated file to be read as it contains minute details of program execution.


What is Oracle Server Autotrace?

It is a utility that provides instant feedback on successful execution of any statement (select, update, insert, delete). It is the most basic utility to test the performance issues.