Oracle PL/SQL Interview Questions and Answers

What is Savepoint?
Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.

A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

What are the various types of Exceptions ?

User defined and Predefined/System defined Exceptions.

What are the various types of database triggers ?
There are 12 types of triggers, they are combination of : Insert, Delete and Update Triggers. Before and After Triggers. Row and Statement Triggers.
What are the advantages/disadvantages of clusters ?
advantages : Access time reduced for joins. disadvantages : The time for Insert increases.
What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it.Therefore, you must declare a subprogram before calling it.This declaration at the start of a subprogram is called forward declaration.A forward declaration consists of a subprogram specification terminated by a semicolon.
What are the types of Notation ?
Position, Named, Mixed and Restrictions.
If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?

What is the advantage of using packages for storing PL/SQL objects?

Packages offer several advantages when you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O.
Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment.
You can specify public (visible and accessible) or private (hidden and inaccessible) in Package.

Define exception and How will you raise an exception in PL/SQL procedure?

In PL/SQL, a error condition is called an exception. Exceptions can be system defined or user defined.

Examples of system defined exceptions include division by zero and out of memory. Some common system exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR.
You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. Unlike system defined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements.

The following example illustrates the scope rules:

RAISE due; 

What is the difference between Procedure and Function?

Differences between Functions and Procedures

-Functions are normally used for computations where as procedures are normally used for executing business logic.

-Functions must return a value (using the RETURN keyword), but for stored procedures this is not compulsory. Procedures can use RETURN keyword but without any value being passed.

-Functions could be used in SELECT statements, but they should don't do any data manipulation. However, procedures cannot be included in SELECT statements.

-Functions could be used in creating "Function Based Indexes" to improve the performance ,but procedure not.

Latest Updates