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

Indian Geography

What is the approximate distance between earth and the moon?

Indian History

In which year was the battle of ‘Koregaon Bhima’ fought?

The approach that is very useful in organizing the content in history is.

General Knowledge of India

NITI Aayog stands for _____.

General Knowledge of MP

India’s first Ramayan art museum was established at?

Errors Identification

Read the sentence carefully and choose the option that has an error in it:
The management’s trusted employee was suspected of stealing large sum of money.

Read the sentence carefully and choose the option that has an error in it:
The teacher asked the child to repeat again because he spoke feebly.

Fill in the blank

Fill in the blank with the most appropriate preposition in the given sentence.
Let’s sit _________ the shade of this beautiful tree.

Choose the most appropriate determiner for the given sentence.
________of what he said was very sensible.


Choose the option that substitutes the given phrase appropriately.
A work of art made by carving

Correct sentence

Choose the option that best transforms the sentence into its Indirect form:
‘What country do you come from?’ said the police officer.

Alternative Phrase

Choose the option that best explains the highlighted expression:
All human beings have feet of clay.

Fill in the blank

Choose an appropriate modal for the given sentence:
My doctor said that I_____ stop smoking as one of my lungs got infected.

Choose the appropriate prepositions for the given sentence:
My parents have been married ________ forty-nine years, but you can still see the love ________ their eyes.

Choose the appropriate conjunction for the given sentence:
______ it rains, the college will declare the holiday.


Choose the appropriate antonym for the highlighted word in the given sentence.
The family protracted their visit by several days.


Choose the appropriate tenses to fill in the blanks in the given sentence:
Please _________ a noise. Ritu __________ to sleep.


Choose the appropriate synonym for the highlighted word in the given sentence.
Does he really expect us to believe such a flimsy excuse?