More Oracle Interview Questions : SQL and PL/SQL

I can say Oracle database is a box of chocolates. You never know what you're gonna get. The more I learn about it, the more there is to learn. It's getting so much better with each new release too. See what Oracle 10g has to give us.. recycle bin, flashback queries, regular expressions ... to name a few. It's intriguing and interesting. Many people found my previous hub on PL/SQL very useful. This hub additionally has questions in SQL along with PL/SQL. Some of these are job interview questions while some others are just interesting information ( which of course could be asked in interviews too ), if you want to be an Oracle developer. The answers that I provided are only for reference and are open to correction.

What does Opening a cursor do ?

- It executes the query and identifies the Result set

What does Fetching a cursor do ?

- It reads the Result Set row by row.

What does Closing a cursor do ?

- It clears the private SQL area and de-allocates the memory.

What are Cursor Variables ?

- Also called REF CURSORS.

- They are not tied to a single SQL. They point to any SQL area dynamically.

- Advantage is : You can declare variables at Client side and open them Server side. You can thus centralize data retrieval.

Why use Cursor Variables?

- You can pass cursor RESULT SETS between PL/SQL stored programs and clients.

What are SQLCODE and SQLERRM ?

- Oracle Error code and detailed error message

- They are actually functions with no arguments, that can be used only in procedural statements ( not SQL)

What are Pseudocolumns ?

- They are not actual columns. They are like Functions without arguments.

- They typically give a different value for each row.


Why use Truncate over Delete while deleting all rows ?

- Truncate is efficient. Triggers are not fired.

- It deallocates space (Unless REUSE STORAGE is given).

What is a ROWID composed of ?

- It's a hexadecimal string representing the address of a row. Prior to Oracle 8, it's a restricted rowid comprising block.row.file. Extended rowid ( the default on higher releases) comprises data object number as well ( comprising the segment number ).

What is the use of a ROWID ?

- Retrieve data faster with ROWID.

- Shows you the physical arrangement of rows in the table.

- Also unique identifier for each row.

Can rows from two different tables have the same ROWID?

- Possible, if they are in a Cluster


- ROWNUM is a pseudocolumn which is the number assigned to each row retrieved.

- ROW_NUMBER is an analytic function which does something similar, but has all the capabilities of PARTITION BY and ORDER BY clauses..

What is an inline view?

- It's not a schema object

- It's a subquery in the FROM clause with an alias that can be used as a view within the SQL statement.

What are Nested and Correlated subqueries ?

- The subquery used in WHERE clause is a nested subquery.

- If this subquery refers to any column in the parent statement, it becomes a correlated subquery.

How do you retrieve a dropped table in 10g?

- FLASHBACK table <tabname> to BEFORE DROP

What are PSPs?

- PL/SQL Server Pages. Web pages developed in PL/SQL

What is an index-organized table?

- The physical arrangement of rows of this table changes with the indexed column.

- It's. in-short, a table stored like an index itself.

What is an implicit cursor?

- Oracle opens an implicit cursor to process each SQL statement not associated with an explicit cursor.

Name a few implicit cursor attributes.


That's from me now.


More by this Author

Comments 83 comments

KK 9 years ago

I found the questions very useful. Thanks.

DS Gangadhar 9 years ago

Hi This is very helpful for intervivewes

shiv kumar 9 years ago

Many thanks for thiis tutorial.

MK 9 years ago

Great set of Q&A's. I found this very very helpful! Thanks so much for posting this stuff!

chinna 8 years ago

Thanks a lot for the questions. They are very helpful.

Sudhir 8 years ago

Very much helpful to the oracle developers

clickbankformula profile image

clickbankformula 8 years ago from Thailand

thanks for your tips, it is very short and clear tips. Brief and good for oracle newbie and pro one.

sumit 8 years ago

Thanks a Ton for this gr8 collection.

its really helpful.

santhosh tadavai 8 years ago

thnx...really gud..n also reffered to my frnds...

job interview questions 8 years ago

You have a knack for making a very hard subject easy to understand. It is so nice to write like this.

Best of luck!

raj 8 years ago

thanks a lot for the q&a's. It was really informative

DEEPAK 8 years ago


Govardhan 8 years ago


thi is govardhan please send mme oracle sql & pl/sql interview questions.

i am looking for job on pl/sql development:

please send questions to:

Fodie 7 years ago

Keep up the good work your q ans a in very useful thanks a lot

Anagha 7 years ago

Thanx a lot !! :)

Rahul 7 years ago

Thanks , these questions are very useful for interviews, it helps a lot.

JoeB 7 years ago

Good list... one comment about using truncate versus "delete all"... truncate is much faster because it does not put data in the UNDO area of the database. It also means that recovery for a mistakenly done command is a little more involved than a delete all.

hemaraj 7 years ago

it's very helpful

MIRA 7 years ago

It is really helpful and i got through the interview..

pora boagaski 7 years ago

thanks man !!!

creativeMind profile image

creativeMind 7 years ago from Cochin

good post..informative

SRINIVAS 7 years ago

Thanks a lot, really useful.

Ahson 7 years ago


Deb 7 years ago

Good one... Really good...Tnanks

srinu 7 years ago

Its really helpful

Sajid 7 years ago

Nice Selections of Questions! Really Useful

sasi 7 years ago

Really good one. thanks

ndrajeet 7 years ago

its all r gud for interview

srinu 6 years ago

nice faq's

Muhannad 6 years ago

Really without any exaggerating, you are excellent

Quadri 6 years ago

You did Wonderful job! keep it up

you can updated with some real scenarios it would be help



Syed  6 years ago

Thanks, it helped me in interview

Sai 6 years ago


Chidananda Naidu Gonugntla 6 years ago


Pals 6 years ago

Hey, this is very helful...Thanks for posting it!

Marin 6 years ago

Hey, very useful, thanks for posting

Hassan 6 years ago

It is really helpful and i got through the interview.

talari lokesh 6 years ago

Very helpful for oracle INTERVIEW

Pranesh I V 6 years ago

Fine . Really gr8 . Presentation is nice. Please update many useful Q/A like tis, which helps a lot for learners like me.

Amit 6 years ago

is this question for PL/SQL developer..... I think it is also useful for JAVA programmer..... in interview they expect deep knowledge of oracle..

sam 6 years ago

it is really good for learners. 6 years ago

i want to know more ques in oracle.......becoz i want to start up my career with it.

vinay 6 years ago

thnks a lot

Manis 6 years ago

Very useful oracle tips.

Please add more QA!

Manis 6 years ago

Hi Rakesh,

Just now only I come to know about "ROW_NUMBER"

Thanks for your useful post.

You can check this blog for more Interview related QA at

prathi 6 years ago

Very informative

bhanu 6 years ago

it is very useful

pvram 6 years ago


this is pvram reddy and looking for job on sql/plsql, so please send me some interview quations and answers to

thanking you

sudheer 6 years ago

Please also send me the interview questions to

Thanks Very Much Friends,

Sudheer C

subhendu 6 years ago

the question answers r very useful....expect more of those...

pratap singh 6 years ago

THANKS,very nice questions with very nice answers,

PLz. suggest some more questions with answers and

CASE STUDIES TYPE questions n answers TOO.

anup 6 years ago

questions and answers are very useful....

vineet 6 years ago

good concepts, very useful in I/N

vipin 6 years ago

gr8 stuff

thanks for sharing your knowledge

Merci 6 years ago

Ha ... ha ... ha ...

I was interviewed on the interview on these issues yesterday. But I found it only today. 100% coincidence with those issues on which I was asked.

Usha 5 years ago

Wow its really nice

SA 5 years ago

Short n perfect answers...

I found it very helpful.


sundhar.j 5 years ago

good collections

Akaas Developer 5 years ago

for more: Oracle Interview Questions and



Akaas Developer

ravi 5 years ago

Good work.. thanks for it..

Sadashiv 5 years ago

Short and Perfect Answers.


Mohammed Habeeb 5 years ago

Thanks ,, It's So Amazing

Suresh 5 years ago

good one. Very helpful

vinayagababu 4 years ago

really very good answer and very helpful

Kumar 4 years ago

Thank's for the Above information, Cool Job ... Keep it!!!!

divakar 4 years ago

nice it is usefully for me

mohan 4 years ago

tank you man It is very useful for us



Mahesh 4 years ago

really Good stuff in this material

Gopal 4 years ago

Can anyone help me with PL-SQL interview questions. I mean actual working environment questions, as to what tools are used in everyday PL/SQL work and what happens after every step of coding etc. Thanks


sandeep sharma 4 years ago

I am fresher in oracle d2k. Please help me . If u have a job for fresher then please inform me on my no. 09953435317.or mail to me on would be thankful to you for this.plzzzzzzz help me i need a job.

Farooque 4 years ago

its very usefull,,,,,,,,, Thanks for help

praveen 4 years ago

good..very useful..try to post more questions

Neha 4 years ago

Excellent !!

Riyas 4 years ago

Good One, Useful One

Ankush 4 years ago


kumara 4 years ago

nice explanation.


Ammu 4 years ago

Information is so useful for beginners

sunil Kumar 4 years ago

Very good questions and answers.

Please post FAQs questions on this page.

Thank you.

Ajay 3 years ago

Agree with Others comments, good to see the interview questions in short

rupesh 3 years ago

Best sql plsql interview preparation guide...

sarfaraz Shaikh 3 years ago

Write explaination with example that would be more benifecial for us.


tamil 2 years ago

really it's very useful for fresher but need experience

udhayakumar 2 years ago

good one keep it up

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.

    Click to Rate This Article