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.

- Examples: ROWNUM, NEXTVAL, ROWID, VERSION_STARTTIME

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

What is ROWNUM and ROW_NUMBER ?

- 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.

- %FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS

That's from me now.

COMMENTS AND SUGGESTIONS ARE WELCOME !

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 8 years ago

Many thanks for thiis tutorial.


MK 8 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

THNAX .....! I THINK VERY GOOD AND VERY AFFECTIVE INFO


Govardhan 7 years ago

hi

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: govardhan.kurra@gmail.com


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 6 years ago

Thanks.


Deb 6 years ago

Good one... Really good...Tnanks


srinu 6 years ago

Its really helpful


Sajid 6 years ago

Nice Selections of Questions! Really Useful


sasi 6 years ago

Really good one. thanks


ndrajeet 6 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

ful.

Thanks


Syed  6 years ago

Thanks, it helped me in interview


Sai 6 years ago

Thanks


Chidananda Naidu Gonugntla 6 years ago

Thanks


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.


priyanka.driems@gmail.com 6 years ago

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


vinay 5 years ago

thnks a lot


Manis 5 years ago

Very useful oracle tips.

Please add more QA!


Manis 5 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

http://oracle-faq-qa.blogspot.com/


prathi 5 years ago

Very informative


bhanu 5 years ago

it is very useful


pvram 5 years ago

hi,

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

thanking you


sudheer 5 years ago

Please also send me the interview questions to sudhir.220650@gmail.com.

Thanks Very Much Friends,

Sudheer C


subhendu 5 years ago

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


pratap singh 5 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 5 years ago

questions and answers are very useful....


vineet 5 years ago

good concepts, very useful in I/N


vipin 5 years ago

gr8 stuff

thanks for sharing your knowledge

http://soft-engineering.blogspot.com


Merci 5 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.

Thanks!!!!!!!!!!!!!!!


sundhar.j 5 years ago

good collections


Akaas Developer 4 years ago

for more: Oracle Interview Questions and

Answers

Regards,

Akaas Developer


ravi 4 years ago

Good work.. thanks for it..


Sadashiv 4 years ago

Short and Perfect Answers.

regards@sadashiv


Mohammed Habeeb 4 years ago

Thanks ,, It's So Amazing


Suresh 4 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

regards

mohankaram


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

Gopal

gopalseri@gmail.com


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 sandeepseo2010@gmail.com.I 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

thanks!


kumara 4 years ago

nice explanation.

Thanks.


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 2 years ago

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


rupesh 2 years ago

Best sql plsql interview preparation guide...


sarfaraz Shaikh 2 years ago

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

Thanks


tamil 2 years ago

really it's very useful for fresher but need experience


udhayakumar 2 years ago

good one keep it up

www.oracletraininginchennai.in

    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
    working