ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Business and Employment»
  • Employment & Jobs»
  • Interviewing for a Job

More Oracle Interview Questions : SQL and PL/SQL

Updated on August 29, 2007

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 !

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      KK 9 years ago

      I found the questions very useful. Thanks.

    • profile image

      DS Gangadhar 9 years ago

      Hi This is very helpful for intervivewes

    • profile image

      shiv kumar 9 years ago

      Many thanks for thiis tutorial.

    • profile image

      MK 9 years ago

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

    • profile image

      chinna 9 years ago

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

    • profile image

      Sudhir 9 years ago

      Very much helpful to the oracle developers

    • clickbankformula profile image

      clickbankformula 9 years ago from Thailand

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

    • profile image

      sumit 9 years ago

      Thanks a Ton for this gr8 collection.

      its really helpful.

    • profile image

      santhosh tadavai 9 years ago

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

    • profile image

      job interview questions 9 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!

    • profile image

      raj 9 years ago

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

    • profile image

      DEEPAK 8 years ago

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

    • profile image

      Govardhan 8 years ago

      hi

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

      i am looking for job on pl/sql development:

      please send questions to: govardhan.kurra@gmail.com

    • profile image

      Fodie 8 years ago

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

    • profile image

      Anagha 8 years ago

      Thanx a lot !! :)

    • profile image

      Rahul 8 years ago

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

    • profile image

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

    • profile image

      hemaraj 8 years ago

      it's very helpful

    • profile image

      MIRA 8 years ago

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

    • profile image

      pora boagaski 7 years ago

      thanks man !!!

    • creativeMind profile image

      creativeMind 7 years ago from Cochin

      good post..informative

    • profile image

      SRINIVAS 7 years ago

      Thanks a lot, really useful.

    • profile image

      Ahson 7 years ago

      Thanks.

    • profile image

      Deb 7 years ago

      Good one... Really good...Tnanks

    • profile image

      srinu 7 years ago

      Its really helpfull

    • profile image

      Sajid 7 years ago

      Nice Selections of Questions! Really Useful

    • profile image

      sasi 7 years ago

      Really good one. thanks

    • profile image

      ndrajeet 7 years ago

      its all r gud for interview

    • profile image

      srinu 7 years ago

      nice faq's

    • profile image

      Muhannad 7 years ago

      Really without any exaggerating, you are excellent

    • profile image

      Quadri 7 years ago

      You did Wonderful job! keep it up

      you can updated with some real scenarios it would be help

      ful.

      Thanks

    • profile image

      Syed  7 years ago

      Thanks, it helped me in interview

    • profile image

      Sai 7 years ago

      Thanks

    • profile image

      Chidananda Naidu Gonugntla 7 years ago

      Thanks

    • profile image

      Pals 7 years ago

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

    • profile image

      Marin 7 years ago

      Hey, very useful, thanks for posting

    • profile image

      Hassan 7 years ago

      It is really helpful and i got through the interview.

    • profile image

      talari lokesh 7 years ago

      Very helpful for oracle INTERVIEW

    • profile image

      Pranesh I V 7 years ago

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

    • profile image

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

    • profile image

      sam 6 years ago

      it is really good for learners.

    • profile image

      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.

    • profile image

      vinay 6 years ago

      thnks a lot

    • profile image

      Manis 6 years ago

      Very useful oracle tips.

      Please add more QA!

    • profile image

      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

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

    • profile image

      prathi 6 years ago

      Very informative

    • profile image

      bhanu 6 years ago

      it is very useful

    • profile image

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

    • profile image

      sudheer 6 years ago

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

      Thanks Very Much Friends,

      Sudheer C

    • profile image

      subhendu 6 years ago

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

    • profile image

      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.

    • profile image

      anup 6 years ago

      questions and answers are very useful....

    • profile image

      vineet 6 years ago

      good concepts, very useful in I/N

    • profile image

      vipin 6 years ago

      gr8 stuff

      thanks for sharing your knowledge

      http://soft-engineering.blogspot.com

    • profile image

      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.

    • profile image

      Usha 6 years ago

      Wow its really nice

    • profile image

      SA 5 years ago

      Short n perfect answers...

      I found it very helpful.

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

    • profile image

      sundhar.j 5 years ago

      good collections

    • profile image

      Akaas Developer 5 years ago

      for more: Oracle Interview Questions and

      Answers

      Regards,

      Akaas Developer

    • profile image

      ravi 5 years ago

      Good work.. thanks for it..

    • profile image

      Sadashiv 5 years ago

      Short and Perfect Answers.

      regards@sadashiv

    • profile image

      Mohammed Habeeb 5 years ago

      Thanks ,, It's So Amazing

    • profile image

      Suresh 5 years ago

      good one. Very helpful

    • profile image

      vinayagababu 5 years ago

      really very good answer and very helpful

    • profile image

      Kumar 5 years ago

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

    • profile image

      divakar 5 years ago

      nice it is usefully for me

    • profile image

      mohan 5 years ago

      tank you man It is very useful for us

      regards

      mohankaram

    • profile image

      Mahesh 5 years ago

      really Good stuff in this material

    • profile image

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

    • profile image

      sandeep sharma 5 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.

    • profile image

      Farooque 5 years ago

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

    • profile image

      praveen 5 years ago

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

    • profile image

      Neha 5 years ago

      Excellent !!

    • profile image

      Riyas 5 years ago

      Good One, Useful One

    • profile image

      Ankush 5 years ago

      thanks!

    • profile image

      kumara 5 years ago

      nice explanation.

      Thanks.

    • profile image

      Ammu 5 years ago

      Information is so useful for beginners

    • profile image

      sunil Kumar 5 years ago

      Very good questions and answers.

      Please post FAQs questions on this page.

      Thank you.

    • profile image

      Ajay 3 years ago

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

    • profile image

      rupesh 3 years ago

      Best sql plsql interview preparation guide...

    • profile image

      sarfaraz Shaikh 3 years ago

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

      Thanks

    • profile image

      tamil 3 years ago

      really it's very useful for fresher but need experience

    • profile image

      udhayakumar 3 years ago

      good one keep it up

      www.oracletraininginchennai.in

    Click to Rate This Article