ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Oracle PLSQL Interview Questions. Additional Concepts Every Developer Should Know.

Updated on April 9, 2007

PL/SQL Demystified

The best way of learning something is by asking questions. That's why I've organized this hub in a question-answer format.

The questions range from Basic to Advanced. I gave a concise answer too, only for quick reference and revision. For detailed answer to each question, refer to any Oracle PL/SQL online documentation.

Some of these questions were actually asked in my Job Interviews in the US.

I have added some others which I found interesting. Very useful to know.

What is a cursor ? ( Basic)

- Name or handle to a private SQL area where Oracle parses and fetches query results.

How to control how many cursors are open ?(Intermediate)

- Set OPEN_CURSORS parameter in initialization parameters.

What is shared SQL ? (Intermediate)

-Oracle recognizes similar statements. The SQL area is used many times for similar statements.

What is Parsing ? (Intermediate)

- Syntax checking, privileges checking, allocating Private SQL Area.

What is the difference between anonymous blocks and stored procedures ? ( Basic)

- Anonymous block is compiled only when called.

- Stored procedure is compiled and stored in database with the dependency information as well.

- Former is PL/SQL code directly called from an application. Latter is stored in database.

- Former has declare statement.Latter doesn't.

What are the advantages of procedures ? ( Basic)

- Loaded once and used many times

- Performance better coz all SQL stmts are sent in one go from the application to the database

- Security ( no object privileges are given directly )

- Invoker's rights possible

- Data integrity, productivity

What are standalone procedures ? (Basic)

- Those that are not part of package

How is a PL/SQL program stored in database ? (Advanced)

- Parsed code is stored. It's called P-code

How is a PL/SQL program executed ?(Advanced)

- Prior to Oracle 9i, we have only bytecode and a virtual machine in the database runs it. Later versions have faster native code execution.

- PL/SQL engine is the main component that executes procedural stmt and passes the SQL to the SQL statement executor.

What are the advantages and disadvantages of DBMS_SQL ? (Intermediate)

- It has all the advantages of dynamic sql .. like runtime construction of sql, DDL statements can be executed.

- Its advantage over EXECUTE IMMEDIATE is it can Describe objects

- It's kind of bulky and difficult compared to EXECUTE IMMEDIATE.

What is a package spec and package body ? Why the separation ? ( Basic)

- Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs

- Separation helps make development easier

- Dependency is simplified. You can modify body without invalidating dependent objects.

What are the advantages of Packages ? ( Basic)

- Encapsulation of code logic

- Privileges to objects can be controlled

- Loaded once into memory , used subsequently.

- Dependency simplified

- Public/private procs, functions, variables

How do you handle exceptions for bulk operations ? (Intermediate)

- Use the SAVE EXCEPTIONS clause ( FORALL index IN bound_clause SAVE EXCEPTIONS LOOP ... END LOOP )

- Use 'Exceptions When Others' to handle the exceptions

- SQL%BULK_EXCEPTIONS(i).ERROR_CODE,

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX

SQL%BULK_EXCEPTIONS.COUNT

Tell some tips to avoid performance problems in PL/SQL. (Intermediate to Advanced)

- Use FORALL instead of FOR, and use BULK COLLECT to avoid looping many times

- Tune SQL statements to avoid CPU overhead

- Use NOCOPY for OUT and IN OUT if the original value need not be retained. Overhead of keeping a copy of OUT is avoided.

- Reorder conditional tests to put least expensive ones first

- Minimize datatype conversions => Assign data to exact same type variables

- Use PLS_INTEGER for computation intensive code. NUMBER, INTEGER maintain precision and scale but not optimized for performance as additional checks are made to maintain precision and scale.

- Do not use subtypes like POSITIVE, NATURAL, INTEGER as they have additional checks

- Use BINARY_FLOAT, BINARY_DOUBLE

- EXECUTE IMMEDIATE is faster than DBMS_SQL

How to know PL/SQL compile parameters ?(Advanced)

- SHOW PARAMETERS PLSQL

- ALL_PLSQL_OBJECT_SETTINGS

What is MERGE ?( Basic)

- Combination of INSERT and UPDATE

Tell some new features in PL/SQL in 10g (Intermediate to Advanced)

- Regular expression functions REGEXP_LIKE , REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR

- Compile time warnings

- Conditional compilation

- Improvement to native compilation

- BINARY_INTEGER made similar to PLS_INTEGER

- INDICES OF , VALUES OF in FORALL lets you work on non-consecutive indices

- Quoting mechanism . Instead of quoting single quotes twice every time, give your own delimiter to go on using single quotes.

Ex: q'!I'm a string, you're a string.!'

- Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN

- Implicit conversion between CLOB and NCLOB

- Improved Overloading

- New datatypes BINARY_FLOAT, BINARY_DOUBLE

- Global optimization enabled

- PLS_INTEGER range increased to 32bit

- DYNAMIC WRAP using DBMS_DDL

What is a sequence ? (Basic)

- A database object that offers high-speed access to an integer value

- Guaranteed to be unique (within that sequence).

-Used commonly to generate Primary key values

Folks, check out http://hubpages.com/hub/oracle_sql_plsql for more questions.

COMMENTS AND SUGGESTIONS ARE WELCOME !!!!

Thanks all for the positive feedback!

    0 of 8192 characters used
    Post Comment

    • profile image

      Adelina Tuzzio 

      5 years ago

      [quote]8192 characters left.[/quote]

      Excellent content, Thanks.. You expressed that effectively!

    • profile image

      5 years ago

      Nice collection and informative as well. Thanks for posting Mr.

    • profile image

      bala 

      5 years ago

      really good,,,simple explantion

    • profile image

      Futamarka 

      5 years ago

      Для парикмахеров характерно поведение, больше связанное с комфортным одиночеством, внутренними размышлениями и переживаниями, творчеством или наблюдением за процессом. Стилист может быть превосходным ученым, исследователем, наблюдателем, писателем или индивидуальным предпринимателем. Если визажисту для комфорта необходимо присутствие других людей, то парикмахеру комфортно трудиться и в одиночестве.

    • profile image

      Rohit Gupta 

      5 years ago

      Nice set of questions and answers.Thanks...

    • profile image

      NK 

      5 years ago

      Nice........Questions... Thanks

    • profile image

      Rakesh 

      5 years ago

      PL/SQL is gone very advanced buddy, so better would be if you please update your page.

    • profile image

      Premsagar 

      5 years ago

      Good work!! Keep on posting

    • profile image

      Narendra 

      5 years ago

      Thanks such a nice questions

    • profile image

      phani 

      6 years ago

      nice tips

    • profile image

      uma 

      6 years ago

      good

    • profile image

      Tapas 

      6 years ago

      great..

    • profile image

      Neha 

      6 years ago

      Excellent !!

    • profile image

      srinivas 

      6 years ago

      good.. useful data for us....

    • 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

      Gopal

      gopalseri@gmail.com

    • profile image

      sandeep 

      6 years ago

      fine..

    • profile image

      Rajendra 

      6 years ago

      thanks a lot

    • profile image

      Abdul Gafoor 

      6 years ago

      Superb...

    • profile image

      Drashti 

      6 years ago

      nice thanks for sharing.please add some other important question and answers for interview.

    • profile image

      Rakesh (Oracle OCP) 

      6 years ago

      Awesome information!!! Thanks!

    • profile image

      Muhd.Aarif 

      6 years ago

      Really awesome......impressing,keep it up

    • profile image

      Merin Philip 

      6 years ago

      NICE YA

    • profile image

      Arrry 

      6 years ago

      Thanks...

    • profile image

      pbd 

      6 years ago

      thanks.. Pls post especially Objects & advanced plsql related questions..

    • profile image

      sandhya 

      6 years ago

      very good question gallery...

    • profile image

      rincy 

      6 years ago

      Thanks.........

    • profile image

      Avinash 

      6 years ago

      Its good

    • profile image

      Sharad Raj Kushwaha 

      6 years ago

      thnx to guide

    • profile image

      Benjamine 

      6 years ago

      Really very good questions and answers.

    • profile image

      Vijay Battula 

      6 years ago

      Excellent

    • profile image

      Sam 

      6 years ago

      Thanks a lot.Questions are really useful

    • profile image

      kksai 

      6 years ago

      found the questions n answers very useful.

      Thankyou for posting them.

    • profile image

      Farooque 

      6 years ago

      its very usefull

    • profile image

      Satish T 

      6 years ago

      Good work man..Thanks for sharing

    • profile image

      mahesh 

      6 years ago

      it's good for improving knowledge.

      Thanks

    • profile image

      siddique rahman 

      6 years ago

      good answers..we have very useful for ur answers..thank u

    • profile image

      Nagender 

      7 years ago

      add few more basic questions..

      good answers & thanks for your work

    • profile image

      abc 

      7 years ago

      nice job

    • profile image

      Usha 

      7 years ago

      Its really good

    • profile image

      Yogesh N. Patil 

      7 years ago

      Good work to explore intermediate Oracle Professionals... Thanks !!

    • profile image

      karthik 

      7 years ago

      Its nice.Already I finished ocp exam its used for recalling everyone totally nice!!!!!!1

    • profile image

      dipti_s 

      7 years ago

      The questions are helpful to me..thanks a lot!!!

    • profile image

      M.A.Anwar 

      7 years ago

      Interesting and useful technical information . Thanks.

    • profile image

      Vijay 

      7 years ago

      Gr8...

    • profile image

      ovais 

      7 years ago

      Very uselfull for basic review

    • profile image

      thulasi 

      8 years ago

      it is very helpful for the guys who has basic knowledge but not in depth.thanks a lot.keep doing the same .thanq

    • profile image

      Nandini 

      8 years ago

      nice

    • profile image

      raj kumar 

      8 years ago

      Very goood info.

    • profile image

      vinni 

      8 years ago

      VERY USEFUL.. THANX A LOT!!! POST MORE NUMBER OF QUESTIONS AND ANS....

    • profile image

      Satish Chauhan 

      8 years ago

      Many Many Thanks !!

    • profile image

      Baijnath Verma 

      8 years ago

      Thanks for Everyone

    • profile image

      Suresh 

      8 years ago

      This is Really to start of my refresh.

    • profile image

      Santha 

      8 years ago

      Excellent information... thank you.

    • profile image

      mmm 

      8 years ago

      very good

    • profile image

      lakshmi 

      8 years ago

      its very use full...thanks

    • profile image

      Phani 

      8 years ago

      Very useful.. Thanks!!

    • profile image

      pinky 

      8 years ago

      Post more number of questions....

      Anyhow this is very useful.......

    • profile image

      Manoj 

      8 years ago

      Great work man..keep it up.

    • profile image

      Raj Yadav 

      8 years ago

      Excellent dude!!

    • profile image

      P. K Roy 

      8 years ago

      It's very very essential for us.

    • profile image

      Praveena 

      8 years ago

      Good and Great Efforts.

    • profile image

      nandu 

      8 years ago

      hey u have done a good job.happy to c people like you

    • profile image

      Mira 

      8 years ago

      This is really good question answer its gonna help me Thank you

    • profile image

      sreekanth 

      8 years ago

      this is good...i am learning new things

    • profile image

      Sam 

      9 years ago

      Thanks a lot Rakesh !!

    • profile image

      Raj 

      9 years ago

      Very good questions with precise and tougtful answers! Nice work.

    • profile image

      Keshava 

      9 years ago

      I am Keshava. Found Q&A were very useful. Good one. Keep it up.

    • profile image

      Nagaraj 

      9 years ago

      Brilliant, Keep in up with some more Advanced Q and Pl/SQL enhancements in 11g.

    • profile image

      rpatel 

      9 years ago

      Gr8 Interview Q&As. Very helpful..

    • profile image

      suman 

      9 years ago

      Nice but this are very basic q&A i want some new Q regarding oracle11i

      .

    • profile image

      Asha 

      9 years ago

      Good one..

    • profile image

      vinay 

      10 years ago

      thanks .keep on doing

    • profile image

      deepak sony 

      10 years ago

      its really very knowledgeable site. thanks for everyone to share the knowledge.

      regards,

      Deepak Sony

    • profile image

      interview questions 

      10 years ago

      “Brilliant,” it is really nice to read a post from someone that knows a subject very well and is able to get their point across. You really summed a well.

      All the best!

    • profile image

      santhosh tadavai 

      10 years ago

      really helpfullll...n also referred to my fnds....appreciate the gud work...thnx again...

    • profile image

      githa 

      10 years ago

      try to present more example programs in which we have to find errors

    • profile image

      Ramya Selvi 

      10 years ago

      It was very useful to me.. Great Thanks... Keep it Up !!!!!!

    • clickbankformula profile image

      clickbankformula 

      10 years ago from Thailand

      thanks for the great tips

    • profile image

      Suhaib 

      10 years ago

      It is really good.

    • profile image

      rayanthomos 

      10 years ago from India

      Great!!!

    • profile image

      Nagesh 

      10 years ago

      Pls do the same. Its very good one

    • profile image

      rvavuleri 

      10 years ago

      keep it up. Good work

    • profile image

      Manas 

      10 years ago

      good

    • profile image

      prem 

      10 years ago

      good work dude. keep it up.

    • profile image

      Manjula 

      10 years ago

      Good work..

    • profile image

      pipa 

      10 years ago

      Thanks for this url.

    • profile image

      Bala 

      10 years ago

      Really a good and excellent tips for the Oracle Workers...

    • profile image

      Rashmi 

      10 years ago

      Its a nice site significant to enhance knowledge and inteligence .

    • profile image

      shiva 

      10 years ago

      its exellent

    • rakeshfan profile imageAUTHOR

      rakeshfan 

      10 years ago

      Thanks everyone for your positive feedback !

    • profile image

      vgmanjare 

      10 years ago

      it gives iot of imfarmetion

    • profile image

      subash 

      10 years ago

      excellent. Keep posting this kind of questions. Thanks heaps

    • profile image

      srisan 

      11 years ago

      Thanks for the questions set!!It helped a lot..

    • profile image

      polesh 

      11 years ago

      gr8..

    • profile image

      Siva 

      11 years ago

      Really done an excellent work..keep posting this sort of mails...Thanks

    • profile image

      ss 

      11 years ago

      Great url referred.I think many users will be benefitted by this type of mails.Keep it Up.

    • profile image

      m3powers 

      11 years ago

      Thanks for work!

    • profile image

      Sridhar 

      11 years ago

      Excellent !!

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com 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: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    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 googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    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)
    Marketing
    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.
    Statistics
    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)