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.


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.



    0 of 8192 characters used
    Post Comment

    • profile image

      udhayakumar 3 years ago

      good one keep it up

    • profile image

      tamil 4 years ago

      really it's very useful for fresher but need experience

    • profile image

      sarfaraz Shaikh 4 years ago

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


    • profile image

      rupesh 4 years ago

      Best sql plsql interview preparation guide...

    • profile image

      Ajay 4 years ago

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

    • profile image

      sunil Kumar 5 years ago

      Very good questions and answers.

      Please post FAQs questions on this page.

      Thank you.

    • profile image

      Ammu 6 years ago

      Information is so useful for beginners

    • profile image

      kumara 6 years ago

      nice explanation.


    • profile image

      Ankush 6 years ago


    • profile image

      Riyas 6 years ago

      Good One, Useful One

    • profile image

      Neha 6 years ago

      Excellent !!

    • profile image

      praveen 6 years ago

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

    • profile image

      Farooque 6 years ago

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

    • profile image

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

    • profile image

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


    • profile image

      Mahesh 6 years ago

      really Good stuff in this material

    • profile image

      mohan 6 years ago

      tank you man It is very useful for us



    • profile image

      divakar 6 years ago

      nice it is usefully for me

    • profile image

      Kumar 6 years ago

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

    • profile image

      vinayagababu 6 years ago

      really very good answer and very helpful

    • profile image

      Suresh 6 years ago

      good one. Very helpful

    • profile image

      Mohammed Habeeb 6 years ago

      Thanks ,, It's So Amazing

    • profile image

      Sadashiv 6 years ago

      Short and Perfect Answers.


    • profile image

      ravi 6 years ago

      Good work.. thanks for it..

    • profile image

      Akaas Developer 6 years ago

      for more: Oracle Interview Questions and



      Akaas Developer

    • profile image

      sundhar.j 6 years ago

      good collections

    • profile image

      SA 6 years ago

      Short n perfect answers...

      I found it very helpful.


    • profile image

      Usha 7 years ago

      Wow its really nice

    • profile image

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

      vipin 7 years ago

      gr8 stuff

      thanks for sharing your knowledge

    • profile image

      vineet 7 years ago

      good concepts, very useful in I/N

    • profile image

      anup 7 years ago

      questions and answers are very useful....

    • profile image

      pratap singh 7 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

      subhendu 7 years ago

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

    • profile image

      sudheer 7 years ago

      Please also send me the interview questions to

      Thanks Very Much Friends,

      Sudheer C

    • profile image

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

    • profile image

      bhanu 7 years ago

      it is very useful

    • profile image

      prathi 7 years ago

      Very informative

    • profile image

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

    • profile image

      Manis 7 years ago

      Very useful oracle tips.

      Please add more QA!

    • profile image

      vinay 7 years ago

      thnks a lot

    • profile image 7 years ago

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

    • profile image

      sam 7 years ago

      it is really good for learners.

    • profile image

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

      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

      talari lokesh 7 years ago

      Very helpful for oracle INTERVIEW

    • profile image

      Hassan 8 years ago

      It is really helpful and i got through the interview.

    • profile image

      Marin 8 years ago

      Hey, very useful, thanks for posting

    • profile image

      Pals 8 years ago

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

    • profile image

      Chidananda Naidu Gonugntla 8 years ago


    • profile image

      Sai 8 years ago


    • profile image

      Syed  8 years ago

      Thanks, it helped me in interview

    • profile image

      Quadri 8 years ago

      You did Wonderful job! keep it up

      you can updated with some real scenarios it would be help



    • profile image

      Muhannad 8 years ago

      Really without any exaggerating, you are excellent

    • profile image

      srinu 8 years ago

      nice faq's

    • profile image

      ndrajeet 8 years ago

      its all r gud for interview

    • profile image

      sasi 8 years ago

      Really good one. thanks

    • profile image

      Sajid 8 years ago

      Nice Selections of Questions! Really Useful

    • profile image

      srinu 8 years ago

      Its really helpfull

    • profile image

      Deb 8 years ago

      Good one... Really good...Tnanks

    • profile image

      Ahson 8 years ago


    • profile image

      SRINIVAS 8 years ago

      Thanks a lot, really useful.

    • creativeMind profile image

      creativeMind 8 years ago from Cochin

      good post..informative

    • profile image

      pora boagaski 8 years ago

      thanks man !!!

    • profile image

      MIRA 8 years ago

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

    • profile image

      hemaraj 8 years ago

      it's very helpful

    • profile image

      JoeB 9 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

      Rahul 9 years ago

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

    • profile image

      Anagha 9 years ago

      Thanx a lot !! :)

    • profile image

      Fodie 9 years ago

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

    • profile image

      Govardhan 9 years ago


      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:

    • profile image

      DEEPAK 9 years ago


    • profile image

      raj 9 years ago

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

    • 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

      santhosh tadavai 9 years ago

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

    • profile image

      sumit 10 years ago

      Thanks a Ton for this gr8 collection.

      its really helpful.

    • clickbankformula profile image

      clickbankformula 10 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

      Sudhir 10 years ago

      Very much helpful to the oracle developers

    • profile image

      chinna 10 years ago

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

    • profile image

      MK 10 years ago

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

    • profile image

      shiv kumar 10 years ago

      Many thanks for thiis tutorial.

    • profile image

      DS Gangadhar 10 years ago

      Hi This is very helpful for intervivewes

    • profile image

      KK 10 years ago

      I found the questions very useful. Thanks.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: ""

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)