Oracle interview questions and answers

In this post we will discuss some basic Oracle Interview questions and answers. Answers given are very precise. Please help me to improve the answer if you think so. I will be regularly updating this post.

1. What is the difference between Procedure and Function ?

Ans:-

a) Function must return a value and procedure need not.

b) Function can be used in SQL with some restrictions. Procedure cannot be called directly from SQL.

2. What is the difference between Anonymous blocks and sub programs ?

Ans :-

a) Anonymous blocks are unnamed blocks which are not stored anywhere while sub programs are compiled and stored in database.

b) Anonymous blocks compile at run time.

3. What is the difference between DELETE and TRUNCATE ?

Ans:-

a) DELETE is a DML command and TRUNCATE is a DDL command.

b) TRUNCATE re-set the memory blocks after execution and much faster than DELETE in most of the circumstances.

4. What is Implicit Cursor and Explicit Cursor ?

a) Implicit Cursor is defined and controlled by Oracle Internally.

Example :-

declare
v_ename varchar2(50);
begin
select ename into v_ename from emp where empno = 10;
end;

select query used in above PL/SQL block is an implicit cursor

b) Explicit Cursor is defined and controlled programatically.

Example :-

declare
v_ename varchar2(50);
Cursor Cur_ename is select ename into v_ename from emp where empno = 10;
begin
Open Cur_ename;
Fetch Cur_ename into v_ename;
Close Cur_ename;
end;

5. Difference between DECODE and CASE

Ans :- Click here

6. How to find Nth highest salary ?

Ans :- Click here

7. Difference between UNION and UNION ALL clause

Ans :- Click here

8. What is Autonomous Transaction ?

Ans :- Click here

9. Difference between REPLACE and TRANSLATE functions

Ans:- Click here

10. What is LEAD and LAG function used for ?

Ans:- Click here