Oracle PL/SQL Interview Questions and Answers

What is Commit, Rollback and Save point?

Commit – Makes changes to the current transaction permanent. It erases the savepoints and releases the transaction locks.

Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.

Rollback – This statement is used to undo work.

How do you make a Function and Procedure as a Private?
Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.
What is the difference between Package, Procedure and Functions?

A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.

Procedure is a sub program written to perform a set of actions and can return multiple values.

Function is a subprogram written to perform certain computations and always return a value.

What are the modes for passing parameters to Oracle?

There are three modes for passing parameters to subprograms
IN – An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

What is Raise_application_error?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue a user_defined error messages from stored sub-program or database trigger.
What is Pragma EXECPTION_INIT? Explain the usage?

he PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. 
E.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. 

SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code.

What is a package spec and package body? Why the separation?

Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs.
Separation helps make development easier. Dependency is simplified. You can modify body without invalidating dependent objects.

What is the difference between anonymous blocks and stored procedures?

Anonymous block is PL/SQL code and compiled only when called.
Stored procedure is compiled and stored in database with the dependency information as well.

Can you use COMMIT in a trigger?
Yes but by defining an autonomous transaction.
What is an autonomous transaction?
An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.
What is the difference between implicit and explicit cursors?
An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements.

Latest Updates

Trigonometry

What is the value of cot 4π/3 ?

Indian Politics

Article-24 of the Indian Constitution ‘Prohibition of employment of children in factories, etc’ deals with?

Article-134A of the Indian Constitution ‘Certificate for appeal to the Supreme Court’ deals with?

Chemistry

Who invented Aspirin?

Physics

A larger force on a rotating body results in larger?

What is the unit of the physical quantity, magnetic flux density?

Algebra

The system of equations 2x + 4y = 6 and 4x + 8y = 8 is ?

Time and Distance

A thief is spotted by a policeman from a distacne of 100 m. When the policeman starts the chase, the thief also starts running. If the speed of the theif is 8 km / hour and that of the policeman is 10 km/hour, then how far will the theif have to run before he is overtaken?

Algebra

Aman and Alok attempted to solve a quadratic equation. Aman made a mistake in writing down the constant term and ended up in roots (4, 3). Alok made a mistake in writing down the coefficient of x to get roots (3, 2). The correct roots of the equation are?

Compound Interest

The effective annual rate of interest corresponding to a nominal rate of 22% per annum payable half-yearly is?

Volume and Surface Area

If the volume of a cylinder is 2156 cubic cm and height is 14 cm, find its radius?

Algebra

What is the equation of the line which passes through the points (2, 3) and (- 4, 1)?

Triangles

In △ABC, the angle bisector of ∠A cuts BC at E. Find length of AC, if lengths of AB, BE and EC are 9 cm, 3.6 cm and 2.4 cm?

In a triangle, the length of the opposite side of the angle which measures 45° is 8√2 cm, what is the length of the side opposite to the angle which measures 90°?

General Knowledge of India

The boundary line between India and Pakistan is called?

Which city is located on the banks of the river Mahanadi?

Indian History

When was the battle of Haldighati fought?

In 1498, which Portuguese explorer discovered a new sea route from Europe to India?

General Knowledge of India

Chapchar Kut is a festival of which State?

Chemistry

In chemistry, soap is a salt of a ?

Indian Economy

Which of the following is a Direct tax?

Biology

Sauria lacertidae is the scientific name of?

Which of the following is a primary function of haemoglobin?

Average

A factory buys 8 machines.3 Machine A, 2 Machine B and rest Machine C. Prices of the machines are 100000, 80000 and 45000 respectively.Calculate the average cost of these machines?

Discount

Ticket for an adult is 500 and a child is 200. 1 child goes free with two adults. If a group has 20 adults and 8 children what will be the discount the group get?