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 ?
Yes

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:

DECLARE
due EXCEPTION; 
BEGIN
RAISE due; 
EXCEPTION
WHEN due THEN 
Null;
END; 

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

Average

The mean of 100 students marks was found to be 40. it was found later that a mark 53 was read as 83. the correct mean is?

Numbers

The geometric mean of three observations 40, 50 and X is 10, then the value of X is ?

Indian Politics

Who appoints the members of National human rights commission?

The President of India can resign by writing under his hand addressed to?

Under which article of the constitution of India can the President of India refer a matter to the Supreme Court for its opinion?

General Computer Knowledge

The term VPN stands for ?

The transmission control protocol(TCP) is a............ layer protocol ?

Oracle PL/SQL

What is the difference between Procedure and Function?

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

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

Fill in the blank

I ________ station before the train arrived.

If I ______ a king, I would help everyone.

Radha___________her daughter for an hour.

Errors Identification

Find out which part of the sentence below has an error and mark the option accourdingly -
Sohan was married in 1990 and have three children.

Find out which part of the sentence below has an error and mark the option accourdingly -
There is no money in the bank in Ram Singh's and Rama's joint account.

Direction

Swapnil start from his home and walked 35 km towards east, after that he turned to his right and walked 20 km. He again turned to his right and walks 35 km. Finally, he turned to his left and walked 20 km and reached his office. How far is his office from his home?

Numbers

How many 2 digit numbers are divisible by 6?

HCF and LCM

Find the smallest square number which is exactly divisible by 3, 5, 15, 18, 20 and 24?