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

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! 98 comments

Sridhar 9 years ago

Excellent !!

m3powers 9 years ago

Thanks for work!

ss 9 years ago

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

Siva 9 years ago

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

polesh 9 years ago


srisan 9 years ago

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

subash 9 years ago

excellent. Keep posting this kind of questions. Thanks heaps

vgmanjare 9 years ago

it gives iot of imfarmetion

rakeshfan profile image

rakeshfan 9 years ago Author

Thanks everyone for your positive feedback !

shiva 9 years ago

its exellent

Rashmi 9 years ago

Its a nice site significant to enhance knowledge and inteligence .

Bala 9 years ago

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

pipa 9 years ago

Thanks for this url.

Manjula 9 years ago

Good work..

prem 9 years ago

good work dude. keep it up.

Manas 8 years ago


rvavuleri 8 years ago

keep it up. Good work

Nagesh 8 years ago

Pls do the same. Its very good one

rayanthomos 8 years ago from India


Suhaib 8 years ago

It is really good.

clickbankformula profile image

clickbankformula 8 years ago from Thailand

thanks for the great tips

Ramya Selvi 8 years ago

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

githa 8 years ago

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

santhosh tadavai 8 years ago

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

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!

deepak sony 8 years ago

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


Deepak Sony

vinay 8 years ago

thanks .keep on doing

Asha 8 years ago

Good one..

suman 8 years ago

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


rpatel 7 years ago

Gr8 Interview Q&As. Very helpful..

Nagaraj 7 years ago

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

Keshava 7 years ago

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

Raj 7 years ago

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

Sam 7 years ago

Thanks a lot Rakesh !!

sreekanth 7 years ago

this is good...i am learning new things

Mira 7 years ago

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

nandu 7 years ago

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

Praveena 7 years ago

Good and Great Efforts.

P. K Roy 7 years ago

It's very very essential for us.

Raj Yadav 7 years ago

Excellent dude!!

Manoj 7 years ago

Great work man..keep it up.

pinky 6 years ago

Post more number of questions....

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

Phani 6 years ago

Very useful.. Thanks!!

lakshmi 6 years ago

its very use full...thanks

mmm 6 years ago

very good

Santha 6 years ago

Excellent information... thank you.

Suresh 6 years ago

This is Really to start of my refresh.

Baijnath Verma 6 years ago

Thanks for Everyone

Satish Chauhan 6 years ago

Many Many Thanks !!

vinni 6 years ago


raj kumar 6 years ago

Very goood info.

Nandini 6 years ago


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

ovais 6 years ago

Very uselfull for basic review

Vijay 6 years ago


M.A.Anwar 6 years ago

Interesting and useful technical information . Thanks.

dipti_s 6 years ago

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

karthik 6 years ago

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

Yogesh N. Patil 5 years ago

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

Usha 5 years ago

Its really good

abc 5 years ago

nice job

Nagender 5 years ago

add few more basic questions..

good answers & thanks for your work

siddique rahman 5 years ago

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

mahesh 5 years ago

it's good for improving knowledge.


Satish T 5 years ago

Good work man..Thanks for sharing

Farooque 5 years ago

its very usefull

kksai 5 years ago

found the questions n answers very useful.

Thankyou for posting them.

Sam 5 years ago

Thanks a lot.Questions are really useful

Vijay Battula 5 years ago


Benjamine 5 years ago

Really very good questions and answers.

Sharad Raj Kushwaha 5 years ago

thnx to guide

Avinash 5 years ago

Its good

rincy 5 years ago


sandhya 4 years ago

very good question gallery...

pbd 4 years ago

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

Arrry 4 years ago


Merin Philip 4 years ago


Muhd.Aarif 4 years ago

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

Rakesh (Oracle OCP) 4 years ago

Awesome information!!! Thanks!

Drashti 4 years ago

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

Abdul Gafoor 4 years ago


Rajendra 4 years ago

thanks a lot

sandeep 4 years ago


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


srinivas 4 years ago

good.. useful data for us....

Neha 4 years ago

Excellent !!

Tapas 4 years ago


uma 4 years ago


phani 4 years ago

nice tips

Narendra 4 years ago

Thanks such a nice questions

Premsagar 4 years ago

Good work!! Keep on posting

Rakesh 4 years ago

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

NK 4 years ago

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

Rohit Gupta 3 years ago

Nice set of questions and answers.Thanks...

Futamarka 3 years ago

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

bala 3 years ago

really good,,,simple explantion

3 years ago

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

Adelina Tuzzio 3 years ago

[quote]8192 characters left.[/quote]

Excellent content, Thanks.. You expressed that effectively!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.

    Click to Rate This Article