ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Business and Employment»
  • Employment & Jobs

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




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



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



What is MERGE ?( Basic)

- Combination of INSERT and UPDATE

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


- Compile time warnings

- Conditional compilation

- Improvement to native compilation


- 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


- Global optimization enabled

- PLS_INTEGER range increased to 32bit


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 for more questions.


Thanks all for the positive feedback!

    0 of 8192 characters used
    Post Comment

    • profile image

      Adelina Tuzzio 4 years ago

      [quote]8192 characters left.[/quote]

      Excellent content, Thanks.. You expressed that effectively!

    • profile image

      4 years ago

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

    • profile image

      bala 4 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 5 years ago

      nice tips

    • profile image

      uma 5 years ago


    • profile image

      Tapas 5 years ago


    • profile image

      Neha 5 years ago

      Excellent !!

    • profile image

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


    • profile image

      sandeep 6 years ago


    • profile image

      Rajendra 6 years ago

      thanks a lot

    • profile image

      Abdul Gafoor 6 years ago


    • 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


    • 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


    • 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


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


    • profile image

      siddique rahman 6 years ago

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

    • profile image

      Nagender 6 years ago

      add few more basic questions..

      good answers & thanks for your work

    • profile image

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


    • profile image

      ovais 7 years ago

      Very uselfull for basic review

    • profile image

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


    • profile image

      raj kumar 7 years ago

      Very goood info.

    • profile image

      vinni 7 years ago


    • profile image

      Satish Chauhan 7 years ago

      Many Many Thanks !!

    • profile image

      Baijnath Verma 7 years ago

      Thanks for Everyone

    • profile image

      Suresh 7 years ago

      This is Really to start of my refresh.

    • profile image

      Santha 7 years ago

      Excellent information... thank you.

    • profile image

      mmm 7 years ago

      very good

    • profile image

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

      Thanks a lot Rakesh !!

    • profile image

      Raj 8 years ago

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

    • profile image

      Keshava 8 years ago

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

    • profile image

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

      thanks .keep on doing

    • profile image

      deepak sony 9 years ago

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


      Deepak Sony

    • profile image

      interview questions 9 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 9 years ago

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

    • profile image

      githa 9 years ago

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

    • profile image

      Ramya Selvi 9 years ago

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

    • clickbankformula profile image

      clickbankformula 9 years ago from Thailand

      thanks for the great tips

    • profile image

      Suhaib 9 years ago

      It is really good.

    • profile image

      rayanthomos 9 years ago from India


    • 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


    • 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 image

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

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

    • profile image

      polesh 10 years ago


    • profile image

      Siva 10 years ago

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

    • profile image

      ss 10 years ago

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

    • profile image

      m3powers 10 years ago

      Thanks for work!

    • profile image

      Sridhar 10 years ago

      Excellent !!