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

11.  Explain function or procedure overloading

Ans:- Click here

12.  What is MERGE used for ?

Ans:- Click here

13.  What is GREATEST and LEAST function used for ? 

Ans:- Click here

14. Where we use SOUNDEX function ?

Ans:- Click here

15.  What is COALESCE function ?

Ans:- Click here

16. Difference between TRUNC and ROUND function

Ans:- Click here

17. How to convert Julian Date to date ?

Ans :- Using ‘JSP’ format string

SQL > select to_char(to_date(2456317,’JSP’),’dd-Mon-yyyy’) as day  from dual;

DAY
————
24-Jan-2013

18. How to convert date to Julian Date format ?

Ans :- Using ‘J’ format string

SQL > select to_char(to_date(’24-Jan-2013′,’dd-mon-yyyy’),’J’) as julian from dual;

JULIAN
——-
2456317

19. What is the difference between PRIMARY KEY and UNIQUE KEY constraints ?

1. UNIQUE KEY columns can have null values but PRIMARY KEY column cannot accept null values.

2. A table can have only one PRIMARY KEY column but many UNIQUE KEY columns allowed.

( I can spot only two. Any more difference ? )

20. What is PRAGMA ?

PRAGMA is Oracle keyword to telling the compiler to do some special work.

Examples :-

PRAGMA AUTONOMOUS TRANSACTION

PRAGMA RESTRICT_REFERENCE

21. What is Constraint ? How many constraints are available ?

Ans :- Explained in another post

22. What is Cartesian Product ?

If two or more tables are joining without join condition will result into Cartesian products.

If table A has 2 rows and table B has 4 rows then Cartesian product between A and B will return 8 rows ( 2 multiply by 4 )

You want to improve the answer ? Please add your comment or send email.

See Also

Install Oracle in Ubuntu 12.04
Latest version of Oracle database – Oracle 12c 
Oracle 12C New Features
Notepad++ – Tips and Tricks
Remote desktop in ubuntu 12.04
Install Skype in Ubuntu 12.04
Install Microsoft fonts in Ubuntu 12.04
Latest version of Oracle database – Oracle 12c

32 responses to “Oracle interview questions and answers”

  1. nice but i want more about primary and unique

    1. Updated. Thanks for comments

    2. Primary key wont allow null values
      primary key wont allow duplicate values
      unique key will allow null values
      unique key wont allow duplicate values

      1. john r u workin on wich technology

    3. One table can have one Primary key column where as a Table can have n number of Uniqe key columns.

  2. Really good anwers but i want more on PLSQL

  3. Really loved the explanations provided.
    Awesome work!!

  4. which is the good company for oracle pl/sql

  5. Actually the major difference of Primary key and Unique key is when we create primary key automatically index will be created but for unique index wont created.
    I faced that question in Interviews.

    1. That is good to know. Will add this point too. Thanks

      1. This is wrong. For Unique constraint UNIQUE index will be created automatically.

  6. How many NULL values can Accept by column of UNIQUE KEY (Interview Question)

    1. A table can have many null values in the unique key column.

    2. unlimited, because null is not equal to null…

  7. Thank you, I’ve recently been searching for info approximately this topic for a while
    and yours is the greatest I have came upon so far. But, what concerning the
    conclusion? Are you certain concerning the source?

  8. Visit below link for more. In every interview, these questions are asked.
    http://quick2solutions.com/index.php/plsql-interview-questions/

  9. thanks for ur complements all

  10. can you please list best companies having datawarehousing projects(plsql+informatica)

  11. can some one please give me practical Pl/SQL tuning questions asked in interviews for 8 years experienced?

  12. How is the market for PL/SQL and Informatica in USA?

  13. […] Also read Oracle Interview Questions and Answers […]

  14. Hello,
    Nice set of questions that are more in-depth than the standard ones on most interview lists! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…We also provide SQL Interview Questions .

  15. Hello,
    Great! very nice collection of questions and topics thank you for sharing this information with us.

  16. […] Find Nth highest salary Polymorphism in Oracle Install Notepad++ in Ubuntu Oracle 12c New Features Oracle Interview questions and answers 5 Different ways to delete records […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: