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

      Sridhar 10 years ago

      Excellent !!

    • profile image

      m3powers 10 years ago

      Thanks for work!

    • profile image

      ss 9 years ago

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

    • profile image

      Siva 9 years ago

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

    • profile image

      polesh 9 years ago

      gr8..

    • profile image

      srisan 9 years ago

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

    • profile image

      subash 9 years ago

      excellent. Keep posting this kind of questions. Thanks heaps

    • profile image

      vgmanjare 9 years ago

      it gives iot of imfarmetion

    • rakeshfan profile image
      Author

      rakeshfan 9 years ago

      Thanks everyone for your positive feedback !

    • profile image

      shiva 9 years ago

      its exellent

    • profile image

      Rashmi 9 years ago

      Its a nice site significant to enhance knowledge and inteligence .

    • profile image

      Bala 9 years ago

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

    • profile image

      pipa 9 years ago

      Thanks for this url.

    • profile image

      Manjula 9 years ago

      Good work..

    • profile image

      prem 9 years ago

      good work dude. keep it up.

    • profile image

      Manas 9 years ago

      good

    • profile image

      rvavuleri 9 years ago

      keep it up. Good work

    • profile image

      Nagesh 9 years ago

      Pls do the same. Its very good one

    • profile image

      rayanthomos 9 years ago from India

      Great!!!

    • profile image

      Suhaib 9 years ago

      It is really good.

    • clickbankformula profile image

      clickbankformula 9 years ago from Thailand

      thanks for the great tips

    • profile image

      Ramya Selvi 9 years ago

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

    • profile image

      githa 8 years ago

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

    • profile image

      santhosh tadavai 8 years ago

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

    • profile image

      interview questions 8 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

      deepak sony 8 years ago

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

      regards,

      Deepak Sony

    • profile image

      vinay 8 years ago

      thanks .keep on doing

    • profile image

      Asha 8 years ago

      Good one..

    • profile image

      suman 8 years ago

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

      .

    • profile image

      rpatel 8 years ago

      Gr8 Interview Q&As. Very helpful..

    • profile image

      Nagaraj 8 years ago

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

    • profile image

      Keshava 8 years ago

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

    • profile image

      Raj 8 years ago

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

    • profile image

      Sam 7 years ago

      Thanks a lot Rakesh !!

    • profile image

      sreekanth 7 years ago

      this is good...i am learning new things

    • profile image

      Mira 7 years ago

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

    • profile image

      nandu 7 years ago

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

    • profile image

      Praveena 7 years ago

      Good and Great Efforts.

    • profile image

      P. K Roy 7 years ago

      It's very very essential for us.

    • profile image

      Raj Yadav 7 years ago

      Excellent dude!!

    • profile image

      Manoj 7 years ago

      Great work man..keep it up.

    • profile image

      pinky 7 years ago

      Post more number of questions....

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

    • profile image

      Phani 7 years ago

      Very useful.. Thanks!!

    • profile image

      lakshmi 7 years ago

      its very use full...thanks

    • profile image

      mmm 7 years ago

      very good

    • profile image

      Santha 6 years ago

      Excellent information... thank you.

    • profile image

      Suresh 6 years ago

      This is Really to start of my refresh.

    • profile image

      Baijnath Verma 6 years ago

      Thanks for Everyone

    • profile image

      Satish Chauhan 6 years ago

      Many Many Thanks !!

    • profile image

      vinni 6 years ago

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

    • profile image

      raj kumar 6 years ago

      Very goood info.

    • profile image

      Nandini 6 years ago

      nice

    • profile image

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

      ovais 6 years ago

      Very uselfull for basic review

    • profile image

      Vijay 6 years ago

      Gr8...

    • profile image

      M.A.Anwar 6 years ago

      Interesting and useful technical information . Thanks.

    • profile image

      dipti_s 6 years ago

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

    • profile image

      karthik 6 years ago

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

    • profile image

      Yogesh N. Patil 6 years ago

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

    • profile image

      Usha 6 years ago

      Its really good

    • profile image

      abc 6 years ago

      nice job

    • profile image

      Nagender 6 years ago

      add few more basic questions..

      good answers & thanks for your work

    • profile image

      siddique rahman 5 years ago

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

    • profile image

      mahesh 5 years ago

      it's good for improving knowledge.

      Thanks

    • profile image

      Satish T 5 years ago

      Good work man..Thanks for sharing

    • profile image

      Farooque 5 years ago

      its very usefull

    • profile image

      kksai 5 years ago

      found the questions n answers very useful.

      Thankyou for posting them.

    • profile image

      Sam 5 years ago

      Thanks a lot.Questions are really useful

    • profile image

      Vijay Battula 5 years ago

      Excellent

    • profile image

      Benjamine 5 years ago

      Really very good questions and answers.

    • profile image

      Sharad Raj Kushwaha 5 years ago

      thnx to guide

    • profile image

      Avinash 5 years ago

      Its good

    • profile image

      rincy 5 years ago

      Thanks.........

    • profile image

      sandhya 5 years ago

      very good question gallery...

    • profile image

      pbd 5 years ago

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

    • profile image

      Arrry 5 years ago

      Thanks...

    • profile image

      Merin Philip 5 years ago

      NICE YA

    • profile image

      Muhd.Aarif 5 years ago

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

    • profile image

      Rakesh (Oracle OCP) 5 years ago

      Awesome information!!! Thanks!

    • profile image

      Drashti 5 years ago

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

    • profile image

      Abdul Gafoor 5 years ago

      Superb...

    • profile image

      Rajendra 5 years ago

      thanks a lot

    • profile image

      sandeep 5 years ago

      fine..

    • 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

      srinivas 5 years ago

      good.. useful data for us....

    • profile image

      Neha 5 years ago

      Excellent !!

    • profile image

      Tapas 5 years ago

      great..

    • profile image

      uma 5 years ago

      good

    • profile image

      phani 5 years ago

      nice tips

    • profile image

      Narendra 4 years ago

      Thanks such a nice questions

    • profile image

      Premsagar 4 years ago

      Good work!! Keep on posting

    • profile image

      Rakesh 4 years ago

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

    • profile image

      NK 4 years ago

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

    • profile image

      Rohit Gupta 4 years ago

      Nice set of questions and answers.Thanks...

    • profile image

      Futamarka 4 years ago

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

    • profile image

      bala 4 years ago

      really good,,,simple explantion

    • profile image

      4 years ago

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

    • profile image

      Adelina Tuzzio 3 years ago

      [quote]8192 characters left.[/quote]

      Excellent content, Thanks.. You expressed that effectively!

    Click to Rate This Article