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

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

More by this Author


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

gr8..


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

Good work..


prem 8 years ago

good work dude. keep it up.


Manas 8 years ago

good


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

Great!!!


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.

regards,

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

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


raj kumar 6 years ago

Very goood info.


Nandini 6 years ago

nice


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

Gr8...


M.A.Anwar 6 years ago

Interesting and useful technical information . Thanks.


dipti_s 5 years ago

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


karthik 5 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.

Thanks


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

Excellent


Benjamine 5 years ago

Really very good questions and answers.


Sharad Raj Kushwaha 4 years ago

thnx to guide


Avinash 4 years ago

Its good


rincy 4 years ago

Thanks.........


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

Thanks...


Merin Philip 4 years ago

NICE YA


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

Superb...


Rajendra 4 years ago

thanks a lot


sandeep 4 years ago

fine..


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

Gopal

gopalseri@gmail.com


srinivas 4 years ago

good.. useful data for us....


Neha 4 years ago

Excellent !!


Tapas 4 years ago

great..


uma 4 years ago

good


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

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


NK 3 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
    working